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

 

[borntorun75]

Hi,

Running the debugger, it may be something to do with the objchart.Chart.Name

Checking the contents of that in the immediate window, it is showing the following ..

Manager summary pivots Chart_Manager_Budget. The Manager summary pivots is the name of the sheet, while Chart_Manager_Budget is the name of the chart.

The chart is actually located in a sheet called Manager summary charts, not the pivot sheet. I wonder if that is anything to do with it ?

Maybe my referencing isn't working as it should be - not sure if it's explained how I sometimes get correct results, but sometimes not.

Mike.

 

[NdNoviceHlp]

Hi Mike. It seems like U are creating the folder more than once (ie. replacing it's contents). Maybe...

For Each Sh In ActiveWorkbook.Sheets
If Sh.Name = "Manager summary charts" Then
CreateFolder ("c:\test\" & _
sC_Area.SlicerItems(sI_Current_Area).Name & "\" & _
sC_Manager.SlicerItems(sI_Current_Manager).Name)
For Each objChart In Sh.ChartObjects
If .Chart.SeriesCollection.Count > 0 Then
.Chart.Export "c:\test\" & _
sC_Area.SlicerItems(sI_Current_Area).Name & "\" & _
sC_Manager.SlicerItems(sI_Current_Manager).Name & "\" & _
.Chart.Parent.Name & ".png", "PNG"
End If
Next objChart
End If
Next Sh

HTH. Dave

 

[borntorun75]

Hi Mike. It seems like U are creating the folder more than once (ie. replacing it's contents). Maybe...

For Each Sh In ActiveWorkbook.Sheets
If Sh.Name = "Manager summary charts" Then
CreateFolder ("c:\test\" & _
sC_Area.SlicerItems(sI_Current_Area).Name & "\" & _
sC_Manager.SlicerItems(sI_Current_Manager).Name)
For Each objChart In Sh.ChartObjects
If .Chart.SeriesCollection.Count > 0 Then
.Chart.Export "c:\test\" & _
sC_Area.SlicerItems(sI_Current_Area).Name & "\" & _
sC_Manager.SlicerItems(sI_Current_Manager).Name & "\" & _
.Chart.Parent.Name & ".png", "PNG"
End If
Next objChart
End If
Next Sh

HTH. Dave

Hi Dave (NdNoviceHlp),

I am certainly running that CreateFolder routine each time, you are correct. However, the subroutine checks whether the folder already exists. If it does, then it won't attempt to recreate it.

What I'm also seeing is that the first file saved in that folder often gets saved as an empty .png file. That's even when I'm stepping through the code, and I can see the graph populated perfectly in the spreadsheet at the very point the .export command is executed. When it does the .export, the file is empty.

Like most problems, it's usually something down to coding / logic that's not right rather than it actually being a bug. I'm just a bit stumped on this one.

Thanks for posting your thought though,
Mike.

 

[borntorun75]

I've identified that having any chart selected in one of the worksheets seems to upset the process / coding somehow.

If I remove focus off the chart, and put select just a normal cell (A1) for example, then running my code seems to run perfectly fine, and all the charts I access through the VBA are exported Ok - consistently.

Does this seem to make any kind of sense, maybe highlighting an issue in my coding where the focus isn't correct ?. Maybe naively, I just assumed that while running the VBA that interacts with charts, then considerations about which Excel objects have the focus when the VBA is run.

Thanks, Mike.

 

[borntorun75]

Bah, scratch previous reply (#5).

It's starting reoccurring again, even though I thought reply #5 was a work-around.

Any ideas ?

Mike.

 

[borntorun75]

SUCCESS!

Another Google has given me the answer on the MSDN forum. It would appear that the problem I'd been experiencing is 'random', or at least people have had the same behaviour happen without reason.

Looping through the chart objects on a sheet, exporting the graphs, sometimes gave an empty file. Correctly named, but 0 bytes.

MSDN : Chart.export creates corrupt files randomly

The first answer in that thread gave the fix. So, it appears that I owe thanks to Tim9855 on MSDN.

Here's my original coding, with the critical extra line shown in red. Activating the chart object has made all the difference.

According to other replies on the above thread, it's commented this was Excel 2010 specific, but Excel 2013 has made its appearance since then, so not sure if it's relevant to 2013.

                    For Each Sh In ActiveWorkbook.Sheets
                        If Sh.Name = "Manager summary charts" Then
                            For Each objChart In Sh.ChartObjects
                                [COLOR=#ff0000][B]objChart.Activate[/B][/COLOR]


'
'   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

I don't think threads are RESOLVED here at MrExcel because there's often more than one solution to a problem, but I can at least confirm the above works.

Thanks, Mike.

 

[monsefoster]

I don't know if it's allowed to respond old posts however I just wanted to say thank you because I've been looking for this answer for more than 3 days with no luck until now.

 

[Jon Peltier]

That's funny. I NEVER activate charts while exporting more than one, and I've never seen this issue.

Code as simple as this has never let me down, and I see no reason that creating a new folder should cause issues:

Sub ExportChartsOnActiveSheet()
  Dim chtob As ChartObject
  Dim sPath As String
  Dim sFilePath As String
  
  sPath = ActiveWorkbook.Path & Application.PathSeparator
  
  For Each chtob In ActiveSheet.ChartObjects
    sFilePath = sPath & chtob.Name & "_" & Format(Now, "yyyymmdd_hhmmss") & ".png"
    chtob.Chart.Export sFilePath
  Next
End Sub

I even built in a couple lines to create a new folder for the exported chart, with no apparent ill effects:

Sub ExportChartsOnActiveSheet()
  Dim chtob As ChartObject
  Dim sPath As String
  Dim sFilePath As String
  
  sPath = ActiveWorkbook.Path & Application.PathSeparator
  
  For Each chtob In ActiveSheet.ChartObjects
    sPath = ActiveWorkbook.Path & Application.PathSeparator & chtob.Name & Application.PathSeparator
    On Error Resume Next ' in case directory already exists
    MkDir sPath
    On Error GoTo 0
    sFilePath = sPath & chtob.Name & "_" & Format(Now, "yyyymmdd_hhmmss") & ".png"
    chtob.Chart.Export sFilePath
  Next
End Sub

I hope by saying that I haven't jinxed all my code!

 

[JG1212726]

Just wanted to add to this old thread because Google brought me here in 2023. This is still relevant in Office 365 for Windows. A German colleague of mine kept getting random 0 byte PNG files when running my VBA code. I wasn't having any issues in the US, so I tried changing my Windows 10 Regional settings to English (Germany) and SURE ENOUGH, random 0 byte PNG files. After a Google search brought me here I added the Chart Activate code before my export and it works in both regions. So I thought I would post this to help anyone in the future that finds this old thread. It appears be more of an issue in non-United States regions.