locked
Chart.export creates corrupt files randomly RRS feed

  • Question

  • Hi All,

    I am developing a program using userforms to display various charts. I use the chart.export property to save and load chart pictures on userform images.
    The program was developed under Windows XP using Excel 2007. I had absolutely no issue under this environment.

    I bought a new computer lately running on Windows 7 with Excel 2010. Under this new environment the chart.export property fails randomly creating files with correct extensions but with no data (0 kb). Sometimes the export works properly and images are displayed appropriately, sometimes the export fails and I get the Run Time Error 481 - Invalid Picture.

    I have 6 userfoms each displaying 2 to 3 charts. Only 2 of these 6 userforms are affected by the export failure. All others work appropriately.

    Here is the code I use to export charts as pictures. This is exactly the same kind of code used for all userforms:

    Code:
    Set CurrentChart = Sheets("ANA_TECHNIQUE").ChartObjects("Chart 1").Chart
    CurrentChart.ShowWindow = True
    ANATECHCHART1 = ThisWorkbook.Path & "\TEMP\ANATECHCHART1.gif"
    CurrentChart.Export Filename:=ANATECHCHART1, FilterName:="GIF"
    AnaTechnique.Image1.Picture = LoadPicture(ANATECHCHART1)
    I spent hours trying to understand this unstable behavior (registry, chart reference, etc) but got no luck.

    Any help would be greatly appreciated. Thanks in advance!!
    • Moved by Martin_Xie Tuesday, August 31, 2010 2:18 AM Move it to VBA forum for better support. (From:Visual Basic General)
    Friday, August 27, 2010 9:02 AM

Answers

  • i had the same issue and within the loop of creating the charts i added  a line to activate the chart object which solved the issue.

     

    For Each ws In ActiveWorkbook.Worksheets
        i = 0
        For Each oCht In ws.ChartObjects
            ' added next line to activate -  fix export issue in excel 2010
            oCht.Activate
            i = i + 1
            fName = ws.Name & i & ".gif"
            oCht.Chart.Export Filename:=fName, FilterName:="GIF"
        Next
    Next

    • Proposed as answer by Ross Marsden Wednesday, November 16, 2011 1:31 AM
    • Unproposed as answer by Ross Marsden Wednesday, November 16, 2011 1:32 AM
    • Proposed as answer by Bobby Moss Wednesday, November 16, 2011 4:52 PM
    • Marked as answer by danishani Friday, March 2, 2012 10:21 PM
    Tuesday, September 13, 2011 12:17 AM
  • I would agree that's an Excel 2010 bug. I had the same issue, so I created a workaround for my specific case here: http://www.vbaexpress.com/forum/showthread.php?t=38738
    Felipe Costa Gualberto - http://www.ambienteoffice.com.br
    Tuesday, August 23, 2011 2:28 PM

All replies

  • Welcome to MSDN forums!

    It's likely to get better support at VBA Forum: http://social.msdn.microsoft.com/forums/en-US/isvvba/threads/


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

    Monday, August 30, 2010 11:01 AM
  • Oh I'm glad you raised this question as I have exactly the same problem i.e. 16 charts generated in a Chart sheet but required to be displayed in a userform, so I have to export them 1st :(. Been looking for an answer but no joy so far. If you find an answer let me know please.

    Basically my code is similar I try deleting the file before generating it and had to add some code to pick the right order to display as the order you make the charts in makes you stick to that order unftil you set the chart name (which can be changed in Layout) to the chart object.

    Private Sub UpdateChart()
        Dim i As Integer
        Set CurrentChart = Nothing

    'This next bit assigns the chart in the order you named them in not the order in ChartObject speak  

    For i = 1 To ChartNumMax
            If CInt(Right(Sheets("Charts").ChartObjects(i).Name, 2)) = ChartNum Then
                Exit For
            End If
        Next

        Set CurrentChart = Sheets("Charts").ChartObjects(i).Chart
       
        CurrentChart.Parent.Width = 450
        CurrentChart.Parent.Height = 300

        fname = ThisWorkbook.Path & Application.PathSeparator & "Chart_" & ChartNum & ".jpg"

    ' Deletes file before export makes no difference to problem

        If Dir(fname) <> "" Then Kill fname
        CurrentChart.Export Filename:=fname, FilterName:="jpg"
        On Error Resume Next
    ' little loop in case refresh rate was causing problem - made no difference to problem

       Dim fstart As Single
        fstart = Timer
        Do
            DoEvents
        Loop Until Timer > (fstart + 1)
    '   Show the chart
        Image1.Picture = LoadPicture(fname)
    End Sub

     

    So tried a few things but no joy so far - I'll let you know if i strike gold!!!

    Friday, November 12, 2010 8:13 PM
  • Oh forgot to say I'm using Excel 2010 and still the same problem
    Friday, November 12, 2010 8:18 PM
  • Ok last addendum I hope might be useful for person searching to know that this the error I get with all file types .gif. .jpg .bmp etc

    Microsoft Visual Basic

    Run-time error '481'

    Invalid Picture

     

    Cheers

    Friday, November 12, 2010 8:24 PM
  • Ok I noticed Run time error 481 was in the question my bad !

    But also the only net comments I can find are re: registry problems or maybe corrupt disk probs.

    So got me the latest "CleanMyPC - Registry Cleaner" and ran that - Brillant made the PC faster but the problem remains

    Plus used "Advanced System Care software" and guess what the PC is as clean as a whistle no probs at all.

    Therefore I think it's a Microsoft probs an I'll love to know the fix. I'll be out there search and trying every tweak I can think of !!!

    Friday, November 12, 2010 8:44 PM
  • Yes, I am experiencing the same issue.

    I have 18 charts in one Sheet and I exported them to show on a Form, like Ace19852, it works great on Office 2003 and 2007, but on 2010 the files are empty! They are exported but empty, but Excel 2010 detects them as graphs. 

    Some troubleshooting details:

    1. From the 18 graphs just one is generated.

    2. Build a sub to try to export all graphs without any form interference:

    Sub GraphList()
      On Error Resume Next
      Dim plan As Worksheet
      Dim i As Integer
      Dim Gr As Chart
      Dim caminho, _
        arquivo, _
        Fname As String
      
      
      Set plan = Sheets("GRAFICOS")
      
      i = 0
      MsgBox plan.ChartObjects.Count
      
      For i = 0 To plan.ChartObjects.Count
        MsgBox i
        Set Gr = plan.ChartObjects(i).Chart
        Gr.Parent.Width = 580
        Gr.Parent.Height = 378
    
      ' HOMEPATH Drive
      drive = Environ("HOMEDRIVE")
      ' File Name
      caminho = Environ("HOMEPATH")
      arquivo = drive & caminho & "\temp " & i & ".gif"
      Fname = arquivo
     Gr.Export Filename:=Fname, FilterName:="GIF"
    Next i
      ' Show the chart
      ' Image1.Picture = LoadPicture(Fname)
    End Sub
    

    All but one graph were generated with 0 bytes. The successfully exported graph was random, depend on start from the Worksheet the graph exported changed.

    3. I tought it was a problem with graphs generated on Excel 2003 and rebuild ALL graphs on 2010. Without sucess.

     

    I wonder if nobody else experienced this problem or I am doing it in the dumb way? Please give me some light on this topic, I am stuck on it for 2 weeks!

    Thx

    F. Schubert

     

    Thursday, June 9, 2011 10:25 PM
  • Did you (Or anyone on this list) found a solution to this problem? I have a very similar problem with Excel 2010 that did not surface at all with previous Excel versions. Any help would be very useful!

    Thanks

    Thursday, June 9, 2011 11:17 PM
  • So far no real advance in this case. Any Microsoft Support operator to give us a light? I think this is a bug from Excel Object Model.

    Well, please give a look on this thread, I will update with my latest findings.

    -> Current tesat: converted old XLS to XLSM and my charts disappeared, yes, the spreadsheet is empty...

    Thx

     

    F. Schubert

    Friday, June 10, 2011 12:28 AM
  • First Sucessful results:

     

    1. Converted Worksheet to 2010 XLSM
    2. DELETED ALL GRAPHS from Sheets
    3. Recreate the graphs in Excel 2010
    4. Run the Test export routine from my previous post.
    5. ALL graphs successfully exported.

    This is a VERY work expensive solution, but so far is the only one I've found. It seems that Excel 2010 changed the graph engine (???) and older versions (2003) did not work.

    Open points:

    1. If I make the same procedure on a XLS file it will work?
    2. If open point 1 works can I create graphs on 2010 and open in 2003? (probably not!)...

    I will finish my tests and inform you. Please Vote as Helpful if you find my posts useful.

     

    Thx

     

    F. Schubert

    Friday, June 10, 2011 12:50 AM
  • False positives, no results.

    More troubleshooting:

    -> All graphs made on Excel 2010 - try to export 16 charts: succeed with 3, failed with all others.

    -> Tested in XLS and XLSM, both with same behavior.

    -> Tried other formats: PNG, BMP and GIF, all with same errors, images are generated with 0 Bytes, empty.

    So far NO SOLUTION FOUND, EVEN A WORKAROUND.

    Please MS VBA guys help us!

     

    Thx

     

    F. Schubert

    Friday, June 10, 2011 1:29 AM
  • Hello guys,

    so far NO solution. I ran OCCI (http://technet.microsoft.com/en-us/library/ee833946.aspx#Office2010ResourceKit_CodeCompatibilityInspector_InspectingVBACode) to see if mine Chart Export functions were deprecated or changed but no error found on the Export routines.

    Now I am waiting Microsoft Office Connect bugs system to allow me to post this bug. I do not know how to consider this issue. For me it is a bug and have to be fixed.

     

    Thx

     

    F. Schubert

    Friday, June 10, 2011 2:04 AM
  • Hello,

    i don`t give up easily but this issue took too much time from me. I made further tests all without success.

    1. Moved the graph from Object to entire sheet. The option Export is missing, I can just ExportAsFixedFormat what is not my need.

    2. Created a new Workbook with one worksheet with some graphs and just one macro to export them, all in 2010 Object Model. Same error.

    My conclusion is: this is a bug in Excel 2010.

    Friday, June 10, 2011 12:57 PM
  • Thanks for keeping looking at this issue and reporting back. I also kept on looking at potential workarounds but no luck, it definitely looks like a Excel 2010 bug. I hope some Microsoft people get on this, it is very unproductive and time wasting to loose programming capabilities and render unusable VBA routines we've been using due to newer but problematic versions of Excel. Good luck and please report back if you learn something else. I'll do the same.
    Friday, June 10, 2011 7:30 PM
  • I'm not sure I can shed any light on the random part of the problem but here are some thoughts.

    I ran into similar issues while working on TM Chart Utilities's 'Export Chart' capability.

    1) Use a system generated temporary file name. This circumvented file privilege issues and also messing with existing files.  Don't know if it will help in the context of this discussion but it may be worth testing.

    2) In Win XP and Office 2003 there were no problems on my side with the export feature.  A few of the people who used the add-in reported problems but I could not trace them to any cause.

    Then, in Win Vista and Office 2007, some image types caused problems.  Some amount of experimenting led to the conclusion that the necessary export filters were missing.  So, I modifed the code to offer only those image types that would work on a given machine.  Since then I haven't had any complaints.

    Just before this post, on Win 7 Office 2010 on my machine I tested the add-in's export feature.  It worked with BMP, JPG, GIF, and PNG (TIF was the exception).

    Anyone interested can try out the TM Chart Utilities (http://www.tushar-mehta.com/excel/software/chart_utilities/index.html) and see what image types the Export Chart feature offers.  It might yield a clue as to something being missing in the Office installation.

    Then, if there are filters missing consider a reinstall of Office with all modules and options selected.

    For those who don't want to download the add-in, the relevant code segments are below.  Maybe, they can adapt the code to their own tests.

    ImgType is a combobox in the userform.

        Sub testAndAddType(aChart As Chart, ByVal aType As String)
            Dim FSO As Object, TempName As String
            Set FSO = CreateObject("scripting.filesystemobject")
            TempName = FSO.gettempname
            TempName = Left(TempName, Len(TempName) - 3) & aType
            On Error Resume Next
            aChart.Export TempName, aType
            If Err.Number = 0 Then Me.ImgType.AddItem aType
            Kill TempName
            End Sub 'testAndAddType
    Private Sub UserForm_Activate()

    'some code; aChart is a variable of type Chart

        testAndAddType aChart, "BMP"
            testAndAddType aChart, "GIF"
            testAndAddType aChart, "JPG"
            testAndAddType aChart, "PNG"
            testAndAddType aChart, "TIF"

    'some other code

        End Sub 'UserForm_Activate

     

     

     


    Tushar Mehta (Technology and Operations Consulting)
    www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)
    Microsoft MVP Excel 2000-Present
    Tuesday, June 14, 2011 3:51 PM
  • I don't have XL2010 so I can't try this.  Save the XL file as HTML.  It should create a directory of gif images for all charts.
    Tuesday, June 14, 2011 7:28 PM
  • Another idea is copy each one to the clipboard, paste them in powerpoint and export from powerpoint (if that is possible).
    Tuesday, June 14, 2011 7:33 PM
  • I don't have XL2010 so I can't try this.  Save the XL file as HTML.  It should create a directory of gif images for all charts.


    Huh?

    I shared the approach because it worked across multiple versions of OSs and Office.


    Tushar Mehta (Technology and Operations Consulting)
    www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)
    Microsoft MVP Excel 2000-Present
    Friday, June 17, 2011 2:59 PM
  • I am having the same problem. Out of 8 charts I'm trying to save as GIF sometimes some will have 0 bytes, and other times everything will work fine. I have tried creating a system temporary file and exporting to that file, but the problem still remains.

    Since it works sometimes and not others, do you really think it is because of "missing export filters"?

    Has anyone reported this to Microsoft as a bug?

    Geoff

    Friday, August 19, 2011 10:14 PM
  • I am at my wits end. I have tried various things to get this to work including saving locally first, save as a temp file, changing the export format, looping thousands of times trying to save over and over, editing the chart before save, creating a new file (copy and paste charts from other), and finally creating a brand new file with NO elements of the file that was having trouble.

    My new file is STILL experiencing the error, very regularly.

    I have called Microsoft and setup a case with support. Case number 111081981691786.  They are supposed to call me back on Monday. Hopefully they will acknowledge this as a bug and can replicate the problem on their end.

    Friday, August 19, 2011 10:56 PM
  • After calling support and demonstrating the problem they were able to duplicate my results. After playing with the file for a few days the Microsoft support technician was able to affect change!  He noticed that if you scroll to the chart so that each chart was visible on the screen before exporting the file size for each exported chart would not be 0 bytes. I can open my Excel file, scoll to see all the charts, run my export code and files will be valid (sometimes).

    I won't propose this an an answer because I don't believe this behavior should be required. He is going to look into the matter further and I'll post my results.

    Geoff

    Monday, August 22, 2011 4:22 PM
  • I would agree that's an Excel 2010 bug. I had the same issue, so I created a workaround for my specific case here: http://www.vbaexpress.com/forum/showthread.php?t=38738
    Felipe Costa Gualberto - http://www.ambienteoffice.com.br
    Tuesday, August 23, 2011 2:28 PM
  • Looks like this is going to be submitted to the developers to review the potential as a bug. We have a workaround--display the chart on the screen before export.

    I'll be watching for the patch, but the problem may not be fixed in this version of the product (or at all).

    Good luck everyone!

    Geoff


    • Proposed as answer by Tim9855 Tuesday, September 13, 2011 12:10 AM
    • Unproposed as answer by Tim9855 Tuesday, September 13, 2011 12:10 AM
    • Proposed as answer by Tim9855 Tuesday, September 13, 2011 12:11 AM
    Thursday, August 25, 2011 8:21 PM
  • i had the same issue and within the loop of creating the charts i added  a line to activate the chart object which solved the issue.

     

    For Each ws In ActiveWorkbook.Worksheets
        i = 0
        For Each oCht In ws.ChartObjects
            ' added next line to activate -  fix export issue in excel 2010
            oCht.Activate
            i = i + 1
            fName = ws.Name & i & ".gif"
            oCht.Chart.Export Filename:=fName, FilterName:="GIF"
        Next
    Next

    • Proposed as answer by Ross Marsden Wednesday, November 16, 2011 1:31 AM
    • Unproposed as answer by Ross Marsden Wednesday, November 16, 2011 1:32 AM
    • Proposed as answer by Bobby Moss Wednesday, November 16, 2011 4:52 PM
    • Marked as answer by danishani Friday, March 2, 2012 10:21 PM
    Tuesday, September 13, 2011 12:17 AM
  • Same problem. Worked on excel 2003 but not 2010. So after much trouble, I moved the embedded chart to a chart sheet. Now it seems to work consistently.
    Friday, November 4, 2011 6:14 PM
  • Activating the chart resolved the issue for me in Excel 2010. Had a similar routine that exported charts from all worksheets that began producing empty PNG files after upgrading to 2010. After applying this suggestion, my charts are now exporting as expected.

    Thanks Tim9855!


    • Edited by Bobby Moss Wednesday, November 16, 2011 4:55 PM
    Wednesday, November 16, 2011 4:54 PM
  • I'd also been trying to get this working since converting an existing 2003 spreadsheet over to 2010. The spreadsheet has a user form with a drop down from which the user selected the graph to export, and it was randomly working on some graphs then creating 0 byte gif file on others.

    Activating the chart first solves the issue for me. Thanks!

    Tuesday, November 22, 2011 2:37 PM
  • Yup. Activating the charts worked for me too.
    Friday, March 2, 2012 9:39 PM
  • This code was working in Excel 2007 on XP Professional: 

    targetChart.Chart.Export Filename:="C:\Chart.gif", Filtername:="GIF"

    but caused a "Permission Denied" error on Excel 2010/Windows 7 after a recent upgrade.

    Based on a tip on Dick Kusleika's Daily Dose of Excel I changed the code to export to my Desktop like this using the Environ function to return my username so it would be generic for another user:

    targetChart.Chart.Export Filename:="C:\Documents and Settings\" & Environ("username") & "\Desktop\Chart.gif", Filtername:="GIF"

    Apparently on my system, writing to the "C" drive programmatically requires higher permissions, but exporting to my desktop is OK.

    

    

    Tuesday, July 17, 2012 8:06 PM
  • OK, so after presenting a beautiful userform which loads 3 charts as saved GIF's to upper management, the problem described in the thread above started appearing. The macro would randomly export the charts as 0 KB files which would then error out as "Invalid Picture" when reloading the pictures into the userform.

    I am using Excel 2010.

    I discovered this thread and WOW, activating each chart removed the problem!

    However, now the code takes a full 6-8 seconds to run due to the extra time of activating each chart before exporting and loading into the userform. This is almost useless. No one wants to wait for such a lag time to load a chart.

    Does anyone have any suggestions on how I can possibly speed up my VBA below?

    Thanks!

    Dim Fname As String
    Dim i As Integer
    
    Application.ScreenUpdating = False
    
    For i = 1 To 3
        Sheets("Sheet1").ChartObjects(i).Activate
        Fname = ThisWorkbook.Path & "\temp" & i & ".gif"
        ActiveChart.Export Filename:=Fname, FilterName:="GIF"
    Next i
    
    With frmResourceCharts
        .Image1.Picture = LoadPicture(ThisWorkbook.Path & "\temp1.gif")
        .Image2.Picture = LoadPicture(ThisWorkbook.Path & "\temp2.gif")
        .Image3.Picture = LoadPicture(ThisWorkbook.Path & "\temp3.gif")
    End With
    
    Application.ScreenUpdating = True
    
    frmResourceCharts.Show

    Thursday, October 11, 2012 3:23 PM
  • @craig_priestley
    You don't need to activate each chartobject everytime you open the userform. You need to do it only once. I usually do it when I open the workbook.

    So, put the following code in your Workbook_Open event and remove it from the code snippet you just wrote:

    For i = 1 To 3
        Sheets("Sheet1").ChartObjects(i).Activate
        Fname = ThisWorkbook.Path & "\temp" & i & ".gif"
        ActiveChart.Export Filename:=Fname, FilterName:="GIF"
    Next i


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    Wednesday, October 17, 2012 12:22 AM
  • This still does not work for me.

    I had limited success by scrolling the excel sheet to put the chart in view before doing the export but I still get some charts that are exported as 0byte files. It is always the same charts that cause the problem with a particular layout. However if I move the charts around the worksheet, then other random charts create 0byte files until I again move the charts around the worksheet.

    To be honest I cant believe that this problem has been known about for so long and Microsoft have done nothing to fix it, other than to suggest that we should first scroll the image into view. I could understand this if it was the only way to export charts full stop, but because this is a random problem there is obviously a problem here that Microsoft needs to Address.

    Please Microsoft Pull your finger and sort this out, you have known about this for 2 years!!!!

    • Proposed as answer by EdaKucera Monday, February 25, 2013 4:58 PM
    • Unproposed as answer by EdaKucera Monday, February 25, 2013 4:59 PM
    Saturday, November 24, 2012 3:00 PM
  • Only charts visible on the sheet get exported (i have put all the graphs over each other and it works). So either do the same or make your script work the way it scrolls to each of the charts or selects them.
    • Proposed as answer by Alex Johnstom Wednesday, November 13, 2013 2:28 PM
    Monday, February 25, 2013 5:00 PM
  • I know this topic is closed, but it seems you don't run into problems until you're exporting larger images in sequence. I added this line of code after each export and it completely resolved the problem. Just putting this out there for others who find this thread with the same problem I had.

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


    Thanks!

    Thursday, September 5, 2013 3:55 PM
  • The charts not being visible on the Excel sheet does seem to be causing the Export problem. However, a workaround that I came up with was to create the charts in the visible portion of the screen, export them, and then move them to the desired location.


    Wednesday, November 13, 2013 2:29 PM
  • Hi All,

    Same here.. Activation helped to lower the number of reported errors.. and waiting a second before activation after activation/before saving and after saving ... seems to downgrade the number of cases that it happens to zero .. BUT! this is not normal development..

    Please MS.. try to fix it...

    Best regards. (Stil a MS fan)

    Marcel

    Tuesday, February 25, 2014 1:51 PM
  • Just to add some more data on this.

    As at July 2014 the problem still occurs. It happens on both Excel 2010 and on the Mac version Excel 2011 (version 14.4.2) July 2014.

    Another clue is that if the image is wider than the screen pixels you will get an image that is correct up to that pixel and is transparent beyond (a png file in this case). On my laptop (1440 pixel wide screen) I only get 1440 pixels of a 1600 pixel wide image.

    So it is almost certainly related to graph position on screen, and occurs on both platforms. 

    Please fix this Dr Microsoft.

    BobJordanB

    Thursday, July 3, 2014 12:01 AM
  • I found a tip online and it worked well:  call the Chart.Activate function before the Chart.Export function.  Basically this sets the chart as the "selected" object in the sheet, and after doing this it seems to export fine every time for me. (Excel 2013, 64-bit edition)

    Source: http://www.mrexcel.com/forum/excel-questions/808744-exporting-chart-via-visual-basic-applications-sometimes-gives-empty-file.html


    Shawn Keene


    Friday, February 12, 2016 8:21 PM
  • The solution to this problem appears to be to set the charts to nothing after export.

    Each time you export a chart, clear the object.

         oCht.Chart.Export Filename:=fName, FilterName:="GIF"

         Set oCht=nothing


    • Edited by msegy9 Wednesday, March 14, 2018 9:48 PM
    Wednesday, March 14, 2018 9:47 PM
  • This seems to do the tick, simply activating the chart before the export. Thanks.
    This problem was so random it would work for a while then export corrupt charts.

    Hopefully .Activate fixes it for ALL of the time

    Friday, October 9, 2020 2:24 PM
  • i have tried all above suggestions, but it doesn't work (0kb files were created) and so lucky when i removed filtername=".gif" property, it's ok now. Hope this help. (Office 2016, win10 pro)

    Thursday, October 22, 2020 10:32 AM