Export a specific excel range as a jpeg via vba

Question

i am totaly new here. I want to export a specific excel range as a jpeg and i use vba for that.

I produced also the vba code but i have a small problem: when i run the the code i export a jpeg file in my excel sheet but i want to export it to specific path at the explorer. Maybe you can help be :)

Option Explicit

Sub Range_To_Image()
  Dim objPict As Object, objChrt As Chart
  Dim rngImage As Range, strFile As String

  On Error GoTo ErrExit

  With Sheets("Tabelle1") 'Tabellenname - Anpassen!

    Set rngImage = .Range("A1:C20")

    rngImage.CopyPicture Appearance:=xlScreen, Format:=xlBitmap

    .PasteSpecial Format:="Bitmap", Link:=False, DisplayAsIcon:=False

    Set objPict = .Shapes(.Shapes.Count)

    strFile = "C:\Users\daniel\Desktop\Sales Report\haus.jpg" 'Pfad und Dateiname für das Bild

    objPict.Copy

    Set objChrt = .ChartObjects.Add(1, 1, objPict.Width + 8, objPict.Height + 8).Chart

    objChrt.Paste
    objChrt.Export strFile
    objChrt.Parent.Delete
    objPict.Delete

  End With

  ErrExit:
  Set objPict = Nothing
  Set objChrt = Nothing
  Set rngImage = Nothing
End Sub

Welcome to SO. Similar question have been asked before. Please have a look around. [Here](https://stackoverflow.com/questions/16143877/using-vba-code-how-to-export-excel-worksheets-as-image-in-excel-2003) for example. Good luck :) — JvdV, Aug 30 '18 at 07:42
Possible duplicate of [Using VBA Code how to export excel worksheets as image in Excel 2003?](https://stackoverflow.com/questions/16143877/using-vba-code-how-to-export-excel-worksheets-as-image-in-excel-2003) — franiis, Aug 30 '18 at 07:45

score 2 · Answer 1 · answered Aug 30 '18 at 09:31

Here's an amended version of your macro that simply copies/pastes the range directly into your temporary chart for export.

Sub Range_To_Image()
  Dim objChrt As Chart
  Dim rngImage As Range
  Dim strFile As String

  On Error GoTo ErrExit

  With Sheets("Tabelle1") 'Tabellenname - Anpassen!

    Set rngImage = .Range("A1:C20")

    rngImage.CopyPicture Appearance:=xlScreen, Format:=xlBitmap

    strFile = "C:\Users\daniel\Desktop\Sales Report\haus.jpg" 'Pfad und Dateiname für das Bild

    Set objChrt = .ChartObjects.Add(rngImage.Left, rngImage.Top, rngImage.Width, rngImage.Height).Chart

    With objChrt
        .Parent.Activate 'to avoid exporting an empty file
        .ChartArea.Format.Line.Visible = msoFalse 'remove border from chart
        .Paste
        .Export strFile
        .Parent.Delete
    End With

  End With

ErrExit:
  Set objChrt = Nothing
  Set rngImage = Nothing
End Sub

Export a specific excel range as a jpeg via vba

1 Answers1