Export Range to JPG (Excel 2016)

[makiwara]

Hi!

I have read a lot about this topic, but unfortunately there are no codes which work with excel 2016 too.

I need a macro, which exports for example the range of A1-E10 to the desktop as a jpg file.

Do you know a solution maybe?

Thank you for your help, it means a lot me!

Have a very nice day

 

[rpaulson]

here is the code I use in Excel 2013, I think it should work with 2016 Enjoy,

Sub create_jpg()

Dim data As Range
Set data = Range("A1:E10")

 DoEvents
   data.CopyPicture xlScreen, xlPicture
    DoEvents

    Application.DisplayAlerts = False
    Set picHolder = ActiveSheet.ChartObjects.Add(1, 1, data.Width, data.Height).Chart
    With picHolder
        
        DoEvents
        DoEvents
        
        Do While .SeriesCollection.Count > 0
            .SeriesCollection(1).Delete
        Loop
        
        .Paste
        DoEvents
        DoEvents
       
        myPath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\"
        F_Name = "test"
       .Export Filename:=myPath & F_Name & ".jpg", Filtername:="JPG"
    End With

    picHolder.Parent.Delete
    Application.DisplayAlerts = True
     
End Sub

hth,

Ross

 

[makiwara]

Thank you for your quick reply!

It works fine if I set the range from A1 to A5 and saves the proper part of the image to my dekstop.

However, if I set the range to A1:A6 or higher (A7, A8, or change column: B5, D7 or E10) it generates just a blank rectangle onto my desktop.

Do you have an idea, how could I solve it? Have a very nice day!

https://imgur.com/a/L96LOFa

here is the code I use in Excel 2013, I think it should work with 2016 Enjoy,

Sub create_jpg()

Dim data As Range
Set data = Range("A1:E10")

 DoEvents
   data.CopyPicture xlScreen, xlPicture
    DoEvents

    Application.DisplayAlerts = False
    Set picHolder = ActiveSheet.ChartObjects.Add(1, 1, data.Width, data.Height).Chart
    With picHolder
        
        DoEvents
        DoEvents
        
        Do While .SeriesCollection.Count > 0
            .SeriesCollection(1).Delete
        Loop
        
        .Paste
        DoEvents
        DoEvents
       
        myPath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\"
        F_Name = "test"
       .Export Filename:=myPath & F_Name & ".jpg", Filtername:="JPG"
    End With

    picHolder.Parent.Delete
    Application.DisplayAlerts = True
     
End Sub

hth,

Ross

 

[makiwara]

B1:b5 works too, B1:b6 not, and a1:B5 neither.

 

[makiwara]

I found a very good solution, source: https://stackoverflow.com/questions/42091390/vba-range-to-jpg-picture/42092375#42092375

[COLOR=#101094][FONT=inherit]Sub[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Export[/FONT][/COLOR][COLOR=#303336][FONT=inherit]()[/FONT][/COLOR]<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">[COLOR=#303336][FONT=inherit]
 [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] oWs [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Worksheet
 [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] oRng [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Range
 [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] oChrtO [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] ChartObject
 [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] lWidth [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Long[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] lHeight [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Long[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

 [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Set[/FONT][/COLOR][COLOR=#303336][FONT=inherit] oWs [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] ActiveSheet
 [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Set[/FONT][/COLOR][COLOR=#303336][FONT=inherit] oRng [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] oWs[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"B2:H11"[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

 oRng[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]CopyPicture xlScreen[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] xlPicture
 lWidth [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] oRng[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Width
 lHeight [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] oRng[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Height

 [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Set[/FONT][/COLOR][COLOR=#303336][FONT=inherit] oChrtO [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] oWs[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]ChartObjects[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Add[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]Left[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]0[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Top[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]0[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Width[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:=[/FONT][/COLOR][COLOR=#303336][FONT=inherit]lWidth[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Height[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:=[/FONT][/COLOR][COLOR=#303336][FONT=inherit]lHeight[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

 oChrtO[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Activate
 [/FONT][/COLOR][COLOR=#101094][FONT=inherit]With[/FONT][/COLOR][COLOR=#303336][FONT=inherit] oChrtO[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Chart
  [/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Paste
  [/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Export Filename[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Case.jpg"[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Filtername[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"JPG"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
 [/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]With[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

 oChrtO[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Delete
 [/FONT][/COLOR]</code>[COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Sub[/FONT][/COLOR]