simple export a selection as an image?

[RealJetJackson]

All,

following the 'success' last week of my posts to get my charts exported as an image, I have moved on a bit and now want to export a selection from one of my sheets as an image too.

I have worked out that I can select part of a sheet to export like this :

Worksheets("Sheet1").Range("C6:D9").CopyPicture _
Appearance:=xlScreen, Format:=xlPicture

but how do I then 'write' it to a file, say a gif for instance?

something like blah.Export "C:\temp\test.gif", "GIF"?

 

[thorpyuk]

Hi, try:

Dim vFile As Variant, sFilter As String, lPicType As Long, oPic As IPictureDisp

sFilter = "Windows Bitmap (*.bmp),*.bmp"

vFile = Application.GetSaveAsFilename(InitialFileName:="T:\Common\DIRMKT\Planning Team\Selections Monitor\Images", filefilter:=sFilter)

If vFile <> False Then
    lPicType = xlBitmap
    Set oPic = Worksheets("Sheet1").Range("C6:D9").CopyPicture _
Appearance:=xlScreen, Format:=xlPicture    SavePicture oPic, vFile
ActiveCell = vFile
End If

 

[RealJetJackson]

ok, I have copy/pasted as you have written, but the Set oPic line turns read and throws and 'expected end of statement' error?

 

[RoryA]

CopyPicture does not return an object so that code won't work. Have a look at the code here for one method.

 

[RealJetJackson]

yes, I've found that code already. It works.

I've been trying to wade through it, to extract just the bits I need and put them in a button rather than a separate macro, but I'm quite new to VB and I'm a bit lost!

 

[RoryA]

You need all of the code. Just call the GIF_Snapshot routine from the button and you should be fine.

 

[RealJetJackson]

Sorry to be dense, how do I call the Macro from within a button?

 

[RoryA]

If it's a button from the Forms toolbar, just assign the GIF_Snapshot macro to it. If it's a button from the Control Toolbox, then you call it from the Click event:

Private Sub CommandButton1_Click()
   GIF_Snapshot
End Sub

 

[RealJetJackson]

ok, I have used the XL2GIF code and hacked it around a bit to fix the selected range and to fix the name the file is exported as (the sheet name). Not pretty, but I understand most of it I think!

Cheers Guys.