Range Exported to Image Comes Up Blank

Bdra

New Member
Joined
Jul 19, 2008
Messages
41
The code below (1) locates the Temp folder on the user's system, and (2) exports the print range of a worksheet to a JPG image, then saves the image to the Temp folder.

The code works exactly as intended on my own system. On the user's system a JPG image is generated and saved, but the image comes up blank. Somehow it fails to capture anything from the worksheet.

My system runs Excel 2010, the user runs Excel 2016, but I have no idea whether the version difference matters. Has anyone encountered this issue before?

Code:
Sub ExportImage()
   
    Dim FSO As Object, TmpFolder As Object
    Set FSO = CreateObject("scripting.filesystemobject")
    
    Set TmpFolder = FSO.GetSpecialFolder(2)
    SavePath = TmpFolder & "\Some Filename.jpg"
    
    'Export print area as correctly scaled jpg image
    With ThisWorkbook.Sheets("Some Worksheet")
        zoom_coef = 100 / .Parent.Windows(1).Zoom
        Set area = .Range(.PageSetup.PrintArea)
        area.CopyPicture xlPrinter
        Set chartobj = .ChartObjects.Add(0, 0, area.Width * zoom_coef, area.Height * zoom_coef)
        chartobj.Chart.Paste
        chartobj.Chart.Export SavePath, "jpg"
        chartobj.Delete
    End With

End Sub
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,619
I have seen this problem and the simple solution is to activate the chart object after the ChartObjects.Add line:
Code:
chartobj.Activate
 

Bdra

New Member
Joined
Jul 19, 2008
Messages
41
Thank you! That sounds like an excellent candidate for a solution. Since I don't see the issue on my own system I can't test your suggestion right away, but I will try it when the user is back at work on Monday and report back here.
 

Bdra

New Member
Joined
Jul 19, 2008
Messages
41
Following up on the exchange above, I implemented your suggestion, and it did indeed resolve the issue.

However, subsequently the user asked me to change the background color of the graphics that are being converted to a JPG image, and that simple change caused the application to revert back to generating blank images, even with the chartobj.Activate line included.

So some additional factor must be at work here. The graphic I am working with is a map of the U.S., composed of shape objects. The background is simply the worksheet itself. My best guess is that activating the worksheet range in order to colorize it, thus removing focus from the shapes in the foreground, is what made the difference. In that case the bottom line is that you need to carefully manage which elements have active focus in order for the VBA routine to work.

However, the user gave up on having the background color changed, so in the end there was no cause to keep troubleshooting the issue. As a practical matter your suggestion did resolve the issue and help me finalize the project, so once again -- Thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,734
Messages
5,597,807
Members
414,177
Latest member
RezaAkhund

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top