VBA - Export Range to Chart object / save image (higher resolution?)

[GBagley]

I'm using the code below to export and save a range (yes, it's a large range!) to an image file. Is there a way to get a higher resolution image using this method?Or is it a limitation of how CopyPicture works with a chart? Or an issue with the size of my range? Gif was no better, and trying a bitmap ended up with a ~200mb image file! The JPG images are only about 40k each. I also tried changing the size of the chart object, but that seems to just stretch the image.

Also, while we're here, I occasionally get an error raised on the line that I highlighted below, which is why I have the error handler looping back to try it again. It will run fine after a couple repeated passes. Any ideas on how to fix that? I tried adding a wait time of up to 5 seconds just before that line, but that didn't seem to help

Sub Export_Range_Image()

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

Dim oRange As Range
Dim oCht As Chart
Dim oImg As Picture
Dim nowTime As String
Dim tryagainloop As Integer
Dim ShTemp As Worksheet

Application.ScreenUpdating = False

Set oRange = ActiveSheet.Range("A1:TD256")

Set ShTemp = Worksheets.Add
Charts.Add
ActiveChart.location Where:=xlLocationAsObject, Name:=ShTemp.Name
Set oCht = ActiveChart

On Error GoTo TryAgain

TryAgain:
Err.Clear

[COLOR="Yellow"]oRange.CopyPicture xlScreen, xlPicture[/COLOR]

oCht.Paste

On Error Resume Next
MkDir ThisWorkbook.Path & "\Saved_Images"

oCht.Export Filename:=ThisWorkbook.Path & "\Saved_Images\" & nowDate & " - " & VisTitle & ".jpg", Filtername:="JPG"

Application.DisplayAlerts = False
ShTemp.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

 

[VoG]

This may help you http://peltiertech.com/WordPress/export-chart-as-image-file/

 

[GBagley]

Good read, thanks! I got the resolution figured out (turns out resizing the chart does increase the size/resolution of the image that is exported, I had just been looking at it in a smaller userform image box, so I couldn't see the difference).

Now I'm having a couple other issues. I want to export the chart as a .PNG file, but I get an error saying it's not a valid image type.

oCht.Export Filename:=ThisWorkbook.Path & "\Saved_Images\" & nowDate & " - " & VisTitle & ".png", Filtername:="PNG"

Either that, or it will export fine but I'll get an 'invalid image type' error when I try to load it into the image control in my userform. Seems to change depending on what computer I'm using...


Also occasionally getting an error on:

ActiveChart.CopyPicture xlScreen, xlPicture

But if I step through it a couple times, it will run fine.

I'll post more specific code/error messages when I get another change to run the program.

 

[Jon Peltier]

This thread's been idle for a long time, but there were a few unanswered points.

1. I don't know why it's telling you sometimes that .PNG is an invalid image type.

2. You can only load BMP, JPG, and GIF images into a UserForm control. PNG is too modern for VBA, if you can believe that.

3. Try to insert a DoEvents call before the offending line:

DoEvents
ActiveChart.CopyPicture xlScreen, xlPicture