CODEWITHSUNDEEP
excelvbacharts
JMichael
JMichael

Reputation: 177

Copy-Paste image to chart improperly pastes a blank image

I have a tangential question to this one: Using VBA Code how to export excel worksheets as image in Excel 2003?

Specifically, when the macro pastes the copy of the range to the chart, the image is blank, even though the copied range contains 5 charts and some formatted cells. When I perform the exact same steps manually it all works as expected.

Even weirder, I've recorded the whole process except the export step. When I run the recorded macro, it works. But when I copy the code from the recorded macro inside the For Each loop below, and tweak it to point to the sheet being worked by the macro (i.e. replacing "ActiveSheet" with "t") the macro doesn't work again.

I even went so far as to just invoke the recorded macro after using the For Each to move to each sheet, still getting a blank image pasted.

I'd appreciate any help on this.

My code:

Sub ExportCharts()
Dim Rng As Range
Dim S As Worksheet
Dim wb As Workbook
Set wb = ThisWorkbook
Dim EName As String
Dim CO As ChartObject
Dim C As Chart
Dim temp As String

Application.ScreenUpdating = False

'Iterate through the sheets in the workbook
For Each t In wb.Worksheets
    'Capture the sheet
    Set S = t

    S.Activate
    'Set the range to be exported
    Set Rng = S.Range("A1:Z60")

    'Copy range as picture onto Clipboard
    Rng.Select
    Rng.CopyPicture Appearance:=xlScreen, Format:=xlBitmap

    'Build the chart/file name
    EName = S.Name & " Quality Charts"

    'Create an empty chart with exact size of range to be copied
    S.Range("$AA$1:$AC$2").Select
    ActiveSheet.Shapes.AddChart.Select
    Set C = ActiveChart
    temp = Right(C.Name, Len(C.Name) - 1 - Len(S.Name))

    S.Shapes(temp).Height = Rng.Height
    S.Shapes(temp).Width = Rng.Width

    'Paste into chart area, export to file, delete chart
    'C.Activate
    With C
        .Paste
        .Export "\\COMPUTERNAME\Users\USERNAME\Desktop\My Documents\" & EName & ".jpg"
        'Note the above is an actual hard coded path in my code (yes I want it hard coded)
    End With
    C.Delete
Next

Application.ScreenUpdating = True

End Sub

Upvotes: 1

Views: 4171

Answers (1)

JMichael
JMichael

Reputation: 177

So I finally figured out the issue. The line turning off screen updating is the issue.

One would assume that's because I say copy as it appears on the screen (why MS didn't just allow for that command to use the display as it appeared at the last screen update is beyond me, but it's not exactly like Excel is bug free).

In any event, commenting out that line results in a good paste.

As a note for those who have more going on in there macros and want/need screen updates off in order to get a reasonable run speed, after I figured out the problem I tried reactivating screen updates before the copy as picture, then turning it off again immediately afterwards, and that worked.

Upvotes: 0

Related Questions