Exporting chart via VBA sometimes gives empty file

[borntorun75]

Hi there,

I've done a search of the forum and the closest I could come to this problem elsewhere is this unanswered thread. http://www.mrexcel.com/forum/excel-questions/724646-chart-exporting-error.html

I have some VBA which is looping through all charts in the workbook and exporting them as .png files. This VBA can often work correctly, and exports everything as anticipated.

However, just as regularly it will only export a few of the graphs correctly, while the others get saved as empty .png files.

Prior to saving the files, I am checking to see that the chart actually has some data in it by checking .SeriesCollection.Count. In the cases where the exporting is failing, they do actually have data in them.

So, I'm not sure why the export is putting out empty files under certain (unknown) conditions. Is there some kind of timing issue, or some event I need to force to ensure that the graph is drawn and formatted in full before I export it ?

Prior to this code, I have some more VBA that forces formatting on the sheets. It's a known issue that PivotCharts can lose their formatting when the pivot is refreshed, so I've created some VBA to re-format the charts.

Thanks - I'm not sure where to look for the solution to this as sometimes it works fine, while others it only exports half the graphs correctly.

As you'll see, the file name(s) are made up of some slicer values, which look Ok and populated at the time of export. The files are always saved under the expected name - it's just sometimes they are empty.

Thanks, Mike.

                    For Each Sh In ActiveWorkbook.Sheets
                        If Sh.Name = "Manager summary charts" Then
                            For Each objChart In Sh.ChartObjects

'
'   Only save if there are data series in the chart
'
                                If objChart.Chart.SeriesCollection.Count > 0 Then
                                    CreateFolder ("c:\test\" & _
                                    sC_Area.SlicerItems(sI_Current_Area).Name & "\" & _
                                    sC_Manager.SlicerItems(sI_Current_Manager).Name)

                                    objChart.Chart.Export "c:\test\" & _
                                    sC_Area.SlicerItems(sI_Current_Area).Name & "\" & _
                                    sC_Manager.SlicerItems(sI_Current_Manager).Name & "\" & _
                                    objChart.Chart.Parent.Name & _
                                                ".png", "PNG"
                                End If
                            Next objChart
                        End If
                    Next Sh

 

[rayban2310]

Thank you!! Took me weeks experimenting with error handling before finding this. Still up to date. Also it has to be the ChartObject (ChartObjects("example").Activate), with the chart itself (ChartObjects("example).chart.Activate) it wont work.