Sunday, June 13, 2010

How to Save Excel Range as Image using VBA

How to copy Excel Range as Image using VBA / How to export Excel Range as Image

The following code saves the Excel Range (A1:B2) as an image.

It uses the Export function of the Chart object (Refer :How to Save a Chart as Image using Excel VBA)
to save as Image

Sub Export_Range_Images()

' =========================================
' Code to save selected Excel Range as Image
' =========================================

Dim oRange As Range
Dim oCht As Chart
Dim oImg As Picture



Set oRange = Range("A1:B2")
Set oCht = Charts.Add


oRange.CopyPicture xlScreen, xlPicture


oCht.Paste

oCht.Export FileName:="C:\temp\SavedRange.jpg", Filtername:="JPG"

End Sub

16 comments:

  1. Cool! But how do I set page size and multiple pages from the same spreadsheet and without creating a new spreadsheet with the image?

    How can I cope a cell value as the file save name?

    ljs2905@gmail.com

    ReplyDelete
  2. Thanks, it helped a lot!

    ReplyDelete
  3. Anonymous11:48 AM

    How do you apply this to a button?

    ReplyDelete
    Replies
    1. Anonymous12:29 PM

      I'm by no means an expert with VBA, but I would assume that you simply assign it as a macro, like you usually would. ('Sub' at the beginning determines the name).

      Delete
  4. Anonymous9:45 PM

    Doesn't work for me.

    Windows XP Excel 2003

    Err 1004, Method 'Export' of Object '_chart' failed.

    wordy1 AT bigpond.com

    ReplyDelete
  5. Anonymous11:54 PM

    Call the code from button's click event. It should work

    ReplyDelete
  6. Anonymous1:14 PM

    IS there a way to edit this to output PDF files?

    ReplyDelete
    Replies
    1. Anonymous8:25 AM

      Public Sub EXPORTER(ByRef fPath As String)
      ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fPath, Quality:=xlQualityMedium, IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False
      End Sub

      Delete
  7. when i paste my data onto the chart it make a bar graph with the image of table superimposed on top. I just want the image of table. Any help?

    ReplyDelete
    Replies
    1. Anonymous6:25 AM

      I help me with this code before pasting picture

      With oCht
      On Error Resume Next
      .ChartArea.Delete
      .Legend.Delete
      On Error GoTo 0
      End With


      Delete
  8. when i paste my range to the chart it creates bar graph with my table superimposed ontop. i want it to be just the image of the table.

    ReplyDelete
  9. How do I specify which worksheet to be exported in your code??

    ReplyDelete
    Replies
    1. Anonymous2:18 AM

      Replace

      Set oRange = Range("A1:B2")

      with

      Set oRange = Worksheets("Sheet1").Range("A1:B2")

      Delete
  10. Is there any way to save images which names are in excel sheet to a folder accordingly.

    ReplyDelete
    Replies
    1. Anonymous2:16 AM

      @Chandan Kumar where are these Images available (as part of Excel or in a separate directory).

      Delete
  11. Anonymous2:15 AM

    @Chandan Kumar where are these Images available (as part of Excel or in a separate directory).

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.