simple export a selection as an image?

RealJetJackson

New Member
Joined
Aug 11, 2008
Messages
22
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

Well-known Member
Joined
Mar 14, 2006
Messages
1,453
Hi, try:

Code:
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

New Member
Joined
Aug 11, 2008
Messages
22
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,320
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
CopyPicture does not return an object so that code won't work. Have a look at the code here for one method.
 

RealJetJackson

New Member
Joined
Aug 11, 2008
Messages
22
:) 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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,320
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
You need all of the code. Just call the GIF_Snapshot routine from the button and you should be fine.
 

RealJetJackson

New Member
Joined
Aug 11, 2008
Messages
22
Sorry to be dense, how do I call the Macro from within a button?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,320
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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:
Code:
Private Sub CommandButton1_Click()
   GIF_Snapshot
End Sub
 

RealJetJackson

New Member
Joined
Aug 11, 2008
Messages
22
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.
 

Forum statistics

Threads
1,081,659
Messages
5,360,313
Members
400,580
Latest member
Drexl88

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top