Exporting charts as Image sometimes generates empty files

Question

I'm doing a macro that exports all the charts in the sheet and then opens Outlook and attaches them. However, I've noticed, several times the charts do export but as 0KB (the file is created, but the image can't be seen)

But it doesn't happen to all the charts. Just most of them and sometimes, it generates them all without a problem. (When I execute the code step by step, all charts generate without a problem, also after executing the step by step, then I execute it normally and all charts generate, BUT if I close and reopen the workbook, it gives the same issue, generates only two and the rest are empty files)

Here's the code:

Dim sheetNumber, Size, i As Integer
    Dim chartNames(), FNames() As String
    Dim objChrt As ChartObject
    Dim myChart As Chart


    'Activate Charts Sheet
    Sheets("GRAFICAS").Activate
    'Calculate Number of Charts in Sheet
    Dim chartNumber
    chartNumber = ActiveSheet.ChartObjects.Count
    'Redimension Arrays to fit all Chart Export Names
    ReDim chartNames(chartNumber)
    ReDim FNames(chartNumber)
    'Loops through all the charts in the GRAFICAS sheet
    For i = 1 To chartNumber
        'Select chart with index i
        Set objChrt = ActiveSheet.ChartObjects(i)
        Set myChart = objChrt.Chart
        'Generate a name for the chart
        chartNames(i) = "myChart" & i & ".png"

        On Error Resume Next
        Kill ThisWorkbook.Path & "\" & chartNames(i)
        On Error GoTo 0
        'Export Chart
        myChart.Export FileName:=Environ$("TEMP") & "\" & chartNames(i), Filtername:="PNG"
        'Save path to exported chart
        FNames(i) = Environ$("TEMP") & "\" & chartNames(i)
    Next i

What am I missing?

Answer 1

I have the same issue, however, I do not experience any errors with 1) "misplacement" or 2) empty charts, or 3) solely based upon activation of chart prior to export. I checked these threads: Link1 and [Link2][3], and it seems that even with activation of the chart the bug still appears (a bit randonmly, but more frequent with larger files) (I know this as I also print pdfs of the chartgroup (which works flawlessly). The pfds are a bit different as they include a frame in addition (Cell A1:K50)).

I have tried some of the tips from the links:

1. Activate the chart before export

2. Remove "the filtername-part" from the export command:

temporaryChrtObj.Chart.Export Filename:=strFilePathName,'Filtername:="PNG" (dropped last part)

3. After export: deleting the object and then also remove its contents from "locals".

temporaryChrtObj.delete: set temporaryChrtObj=nothing

4. Let the program render at some locations in the code:

Application.Wait (Now + TimeValue("0:00:03"))

5. A tip (I haven't tried) in link2 is to change regional computer settings to american.

I have tried 1), 2), 3) and 4) and also something I suggested myself: 6) to zoom out a bit, to make the printfiles smaller (this will also make the prints coarser/more pixelated). Eg. use ActiveWindow.Zoom = 180 instead of ActiveWindow.Zoom = 250

Still, the errors occurs :(

I am getting a bit desperate here.

Ok, so as seen in my comment nr. 2, I made a "fix", the code is a bit large, especially with all these attempts at going around the problem:

Answer 2

After a bit of head scratching, it appears that a Chart to be exported needs to be within the visible area of the sheet on which it is embedded. The sheet does not need to be visible, but the chart needs to be within the area that would be visible if the sheet on which the chart is embedded were the ActiveSheet. Otherwise empty files are written.

Answer 3

With Office 2016 and Office 365 in C#, the function "Activate()" also solved the same problem:

Excel.ChartObjects xlCharts = Excel.ChartObjects)worksheet.ChartObjects(Missing.Value);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Item(1);

myChart.Activate();

Excel.Chart chart = myChart.Chart;
chart.Export(outputFile, "PNG", false);

Answer 4

I have been experiencing, as many other users, it seems, same issue. after many trials, some googleing, I found that EXCEL is sensitive to chart visibility, position, size. So I guess, from your example, that only Mychart6 and Mychart7 are visible on screen.

below i put the code that I am using today for a very similar task. Up to now is working with no issues.

how it works: loop within all charts in sheet33 move chart, with topleft corner overlapping to topleft corner of cell B2 (that is supposed to be visible) export chart move back to original position range B2:G13 has been set as range object in order to allow me, if needed, to manage charts of different dimension (setting .height and .width properties of chart aligned with same properties of range B2:G12)

Sub export_kpi()

    On Error Resume Next

    Dim pd As String
    Dim ob As ChartObject
    Dim intervallo_riferimento As Range
    Dim temp_top As Double
    Dim temp_left As Double

    Set intervallo_riferimento = Sheet33.Range("B2:G13")

    pd = "//best-collab.st.com/ws/PC_R2/images1/kp"

    For Each ob In Sheet33.ChartObjects

        temp_top = ob.Top
        temp_left = ob.Left

        ob.Top = intervallo_riferimento.Top
        ob.Left = intervallo_riferimento.Left

        ob.Chart.Export pd & ob.Index & ".jpg", "jpg"

        ob.Top = temp_top
        ob.Left = temp_left

    Next ob

    ExportImage Sheet33.Range("B27:G38"), pd & "0.jpg"

End Sub

Answer 5

It turns out, it's a random error for Excel 2010-2013 users. However, after some more googling. I encountered the answer here

You just need to add

objChrt.Activate

After selecting the chart. So In my case the final code looks like this:

 For i = 1 To chartNumber
        'Select chart with index i
        Set objChrt = ActiveSheet.ChartObjects(i)
        objChrt.Activate
        Set myChart = objChrt.Chart
        'Generate a name for the chart
        chartNames(i) = "myChart" & i & ".png"

        On Error Resume Next
        Kill ThisWorkbook.Path & "\" & chartNames(i)
        On Error GoTo 0
        'Export Chart
        myChart.Export FileName:=Environ$("TEMP") & "\" & chartNames(i), Filtername:="PNG"
        'Save path to exported chart
        'Application.Wait (Now + #12:00:01 AM#)
        FNames(i) = Environ$("TEMP") & "\" & chartNames(i)
    Next i