troubleshooting Question

Chart Export to gif not working 100%

Avatar of dimmergeek
dimmergeekFlag for United States of America asked on
Microsoft ExcelMicrosoft DevelopmentVB Script
6 Comments1 Solution838 ViewsLast Modified:
I have an Excel document with five worksheets.  Four of the worksheets have nothing but data.
The last worksheet, currently has 44 charts displaying data from the other four pages.

I have been asked to export these charts on a daily basis to a network drive so people can see the charts on a web site.

I wrote some VBA code to perform this task, but it is behaving oddly.  Here is the code:

//-----------------------------------------------------------------------------------------------------------------------
Sub Export()

    Dim objCht As ChartObject
    Dim strPath As String
    Dim chartCount As Integer
   
    strPath = "G:\depart\CMS\lean\mwMetrics\Images\"
    chartCount = 0
     
    For Each objCht In Sheets("Charts").ChartObjects
        'MsgBox objCht.Name & " has been exported.", vbInformation, "Export Status"
        objCht.Chart.Export strPath & objCht.Name & ".gif", filtername:="gif"
        chartCount = chartCount + 1
    Next
   
    MsgBox chartCount & " charts exported.", vbInformation, "Chart Count"

End Sub
//-----------------------------------------------------------------------------------------------------------------------

As mentioned earlier, there are 44 charts.  The message box at the end tells me 44 charts are exported.  When I go to the directory on the netwrok, i see 44 charts, they are all named properly and I can open them all.

The problem is that (randomly) between 1 and 10 charts will invariably be 'blank' meaning that the file size is 0kb, and when I open it, I just get a little red 'X' logo in paint, or Internet Explorer on the web page.

It's not always the same charts that fail in this manner, and it's not always the same number of charts.

Any ideas on how to fix?
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros