Enhanced Export Chart Procedure

In Export Chart as Image File I described the VBA command to export a chart as an image file, and I presented a simple routine that exported the active chart.

I said that one day I would enhance the procedure to use GetSaveAsFileName, which allows the user to browse to any folder to save the image file, rather than blindly dumping it into the directory containing the active workbook. I also thought it would be nice not only to detect a duplicate file name, but also to allow the user to overwrite the duplicate with a new version of the same file. The older procedure did not check for invalid characters, while the new procedure relies on the GetSaveAsFileName dialog to accomplish this.

Without further ado, here is the enhanced procedure. After using it for fifteen minutes, the abilities to browse to another folder, to see the files in the target directory, and to overwrite an existing file had already paid for the effort to rewrite the code.

The ExportChart module and the menu modifying code below should go into a regular module, probably in an add-in or other workbook you will use to contain the code.

Sub ExportChart()
  Dim sChartName As String
  Dim sFileName As String
  Dim sPathName As String
  Dim sPrompt As String
  Dim sCurDir As String
  Dim iOverwrite As Long

  If ActiveSheet Is Nothing Then GoTo ExitSub
  If ActiveChart Is Nothing Then GoTo ExitSub

  sCurDir = CurDir
  sPathName = ActiveWorkbook.Path
  If Len(sPathName) > 0 Then
    ChDrive sPathName
    ChDir sPathName
  End If

  sFileName = "MyChart.png"

  Do
    sChartName = Application.GetSaveAsFilename(sFileName, "All Files (*.*),*.*", , _
        "Browse to a folder and enter a file name")

    If Len(sChartName) = 0 Then GoTo ExitSub
    If sChartName = "False" Then GoTo ExitSub

    Select Case True
      Case UCase$(Right(sChartName, 4)) = ".PNG"
      Case UCase$(Right(sChartName, 4)) = ".GIF"
        'Case UCase$(Right(sChartName, 4)) = ".BMP"
        'Case UCase$(Right(sChartName, 4)) = ".TIF"
        'Case UCase$(Right(sChartName, 5)) = ".TIFF"
      Case UCase$(Right(sChartName, 4)) = ".JPG"
      Case UCase$(Right(sChartName, 4)) = ".JPE"
      Case UCase$(Right(sChartName, 5)) = ".JPEG"
      Case Else
        If Right$(sChartName, 1) <> "." Then sChartName = sChartName & "."
        sChartName = sChartName & "png"
    End Select

    If Not FileExists(sChartName) Then Exit Do

    sFileName = FullNameToFileName(sChartName)
    sPathName = FullNameToPath(sChartName)

    sPrompt = "A file named '" & sFileName & "' already exists in '" & sPathName & "'"
    sPrompt = sPrompt & vbNewLine & vbNewLine & "Do you want to overwrite the existing file?"

    iOverwrite = MsgBox(sPrompt, vbYesNoCancel + vbQuestion, "Image File Exists")

    Select Case iOverwrite
      Case vbYes
        Exit Do
      Case vbNo
        ' do nothing, loop again
      Case vbCancel
        GoTo ExitSub
    End Select
  Loop

  ActiveChart.Export sChartName

ExitSub:

  ChDrive sCurDir
  ChDir sCurDir
End Sub
 

The code to add a button to the bottom of the Chart menu, and to remove the button, is pretty simple:

Sub AddExportChartMenuItem(Optional bDummy As Boolean = True)
  Dim MyBar As CommandBar
  Dim MyPopup As CommandBarPopup
  Dim MyButton As CommandBarButton
  Dim iMenu As Long
  Dim vMenu As Variant

  RemoveExportChartMenuItem

  vMenu = "Chart Menu Bar"

  Set MyBar = CommandBars(vMenu)
  Set MyPopup = MyBar.FindControl(ID:=30022)  ' Data menu

  With MyPopup

    Set MyButton = .Controls.Add(Type:=msoControlButton)
    With MyButton
      .Caption = myChartExportMenu
      .Style = msoButtonIconAndCaption
      .FaceId = 435  ' 422
      .BeginGroup = True
      .OnAction = "'" & ThisWorkbook.Name & "'!ExportChart"
      .Visible = True
    End With

  End With

End Sub

Sub RemoveExportChartMenuItem(Optional bDummy As Boolean = True)
  Dim vMenu As Variant

  vMenu = "Chart Menu Bar"

  On Error Resume Next
  CommandBars(vMenu).FindControl(ID:=30022).Controls(myChartExportMenu).Delete
  On Error GoTo 0

End Sub
 

These procedures use a constant that contains the label text for the button, so in the declarations section of the module you should insert this line:

Public Const myChartExportMenu As String = "E&xport Chart"
 

Call these menu modifying procedures from the ThisWorkbook module of the workbook or add-in containing the chart export and menu modifying code, using the following event procedures.

Private Sub Workbook_AddinInstall()
   AddExportChartMenuItem
End Sub

Private Sub Workbook_Open()
  AddExportChartMenuItem
End Sub

Private Sub Workbook_AddinUninstall()
  RemoveExportChartMenuItem
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  RemoveExportChartMenuItem
End Sub
 

The procedures above call a few helper functions, which are posted in File Name Functions.

I’ve wrapped this procedure into a small self-contained add-in, which you can download from here:
ExportChart.zip. Unzip the file and install the add-in, as described in Installing an Excel Add-In. The add-in adds a button, Export Chart, to the bottom of the Chart menu, which will export the active chart.

Peltier Tech Chart Utility

Comments

  1. I wrote a similar function here, but included the ability to export shapes or ranges of cells.

    http://www.consultantninja.com/2007/11/exporting-excel-charts-shapes-ranges.html

  2. Ninja – Nice one. I’d missed it earlier. It’s based on a RangeToGif procedure developed by Harald Staff, and posted here:

    http://www.mvps.org/dmcritchie/excel/xl2gif.htm

    I made a couple of comments on your post, regarding the treatment of charts in your routine.

    For ranges, I’ve cobbled together a little routine that converts the selected range in Excel into HTML, with options to copy various cell and font formatting, and to show or not show the row and column headers. It’s pretty nice, but it doesn’t render as nicely in FireFox as in IE (duh, since I borrowed from MS Excel export-to-html formatting). I haven’t posted this export routine, but maybe if I’m hungry for a blog topic some day, or if someone reads this comment and asks nicely….

  3. Hi Jon,

    I’ve followed the steps and added the add-in and copied the macro code. But when I try to run the macro I get the “Compile Error, Sub of Function Not Defined” message.

    Would you please help?

  4. The add-in is self-contained. If you install the add-in, you don’t need to copy or paste any code.

  5. Thanks, now it works. But I had a problem: The add-in didn’t work when I grouped some drawings with the chart. It seems that when you group a chart with something, Excel don’t count it as a chart. Any idea?

  6. Serhat – The routine works on the active chart. Once you’ve grouped the chart with other objects, it cannot be the active chart. Are the shapes something that could be pasted into the chart itself? Ungroup the chart from the objects, copy the objects, select the chart, then paste. The shapes are now integral with the chart. Unfortunately, shapes in a chart are not always faithfully rendered when exporting.

    Tony – This routine makes it easy. Run it once, and name the chart Chart.jpg, then run it again using Chart.png. (In fact, if you leave off the extension, or use one that Excel doesn’t recognize, it will default to png.)

  7. Umm, I love this add-in for Excel. It’s very easy to install and a breeze to use! Thanks Jon! Now I need to see how much better my graphs would be as a png. vs. jpg..

  8. I’m going to run my chart with both extensions and post a new blog entry comparing the two.

  9. Can you please post the function for FullNameToFileName()?
    It says that the function is not defined in your code.

  10. Eric –

    Another helper function I use a lot and forgot to post.

    Function FullNameToFileName(sFullName As String) As String
      Dim k As Integer
      Dim sTest As String
      If InStr(1, sFullName, "[") > 0 Then
        k = InStr(1, sFullName, "[")
        sTest = Mid(sFullName, k + 1, InStr(1, sFullName, "]") - k - 1)
      Else
        For k = Len(sFullName) To 1 Step -1
          If Mid(sFullName, k, 1) = "\" Then Exit For
        Next k
        sTest = Mid(sFullName, k + 1, Len(sFullName) - k)
      End If
      FullNameToFileName = sTest
    End Function
     
  11. Can you please post the function for FullNameToPath() as well?
    Thank you!

  12. Eric – I’ve posted a set of helper functions here:

    File Name Functions

  13. Is it possible to export the charts onto a network server instead of the local computer?

  14. Eric –

    If you can browse to the directory in the Save As dialog, and if you have the right permissions, you can save the file to any share. I just saved an image on my office network to

    \\pts1\SharedDocs\Web\MyChart.png

  15. Thanks a lot. My boss was just asking if I knew of a way to save charts as images so she can import them into Movie Maker. This works great. I dabble in VBA but couldn’t have come up with this. Now I can study it and learn something.

  16. I have installed the add-in on Excel 2000 (past its sell by date!). The Chart menu shows the Export button. Activating the button brings up a dialogue box with File Name (“MyChart.png”) and Save as Type (All Files (*.*)).

    Using the default settings (or any other) gives a run-time error 1004 with the message Method ‘Export’ of object ‘_Chart’ failed. Opening the debugger showed the error at

    ActiveChart.Export sChartName

    [edited for length]

    The VB editor indicates the routine has acquired the correct file location.

    Ben
    Not being a code cruncher can anyone help?

  17. Ben –

    What is the save as file name (sChartName)? To make this easier, insert this line in front of “ActiveChart.Export sChartName”, and rerun the utility:

    Debug.Print sChartName

    Is the save as directory one which you have sufficient write permissions (i.e., are you permitted to create a file in that directory)?

    Can you select a chart, open the Immediate Window (Ctrl+G) in the VB Editor, and execute this command:

    activechart.export “C:\mychart.png”

    How about this command:

    activechart.export “C:\mychart.gif”

  18. Further investigation showed the PNG Filter had not been loaded when Office 2000 was installed. The Add-in is now working beautifully.

    Experience also shows the difference between using png / gif file format compared to jpg.

    Many thanks for all the work you have done to help others extract the best from Excel.

    Ben [JP]

  19. I have Office 2007, and I’m not seeing a button anywhere to export the chart…
    I like the idea though, it looks like it works.

  20. Charlie –

    There should be a button on the Add-Ins tab for “Export Chart”.

    Incidentally, I tried to add the button to the right click menu, which would work in old and new versions of Excel. But in their rush to release 2007, Microsoft forgot to make the chart context (right click) menus accessible to changes through VBA.

  21. Jon:

    I have a 4 by 4 array of charts ( all same size) and want to export it to a single image file (.gif).

    How would you do it in a VB script?

  22. Stan –

    Essentially you would copy the charts as pictures, paste them into a larger chart, and export the larger chart.

    Harald Staff wrote a procedure to copy a range of cells into a chart for export. Perhaps you could adapt it. David McRitchie hosts Harald’s procedure here:

    XL2GIF routine and manipulations

  23. Jon,

    I forgot to activate the Add-in! It works awesome, great job. I can’t believe something like this isn’t standard. Thanks a bunch.

    Charlie

  24. Jon, presently I scan my scientific-type Excel plots into a graphics program to tidy them up before submitting them for publication. I use a clone tool to erase unwanted tick marks and scale labels from secondary axes where I am using only as little as one third of the full axis. And I crop out excess chart area around the plot area. I also add axis break marks, and I relabel logarithmic axes to an exponential format. I now use your Exponential Notation route for the latter. l love it, thanks. I plan to use your Enhanced Export Chart Procedure to export my untidy plots from Excel directly to the graphics program. Does this path retain the Excel construction features in the plots? If so, is there a way to import the tidied up plot back into Excel where I can make the modified plots into templates and/or do further editing?
    Sincerely, Ken Farrell

  25. Ken –

    The exported charts are in a raster format (i.e., pixel by pixel), so cannot serve as templates, and may not be useful as raw material for a graphics program. PowerPoint allows exporting of objects in vector formats (consisting of drawing objects), and I have considered going this route with Excel charts, but have never been sufficiently motivated.

    However, you can make a lot of the changes you describe in your Excel charts, to minimize the manual work you need to do in third party packages. The excess white space can be shrunk by expanding the plot area size. The unwanted axis tick labels can be hidden through use of custom number formats. Unwanted tick marks can be avoided by not using built in tick marks, but instead positioning a hidden XY series along the axis, one point everywhere you want a tick mark, and add custom error bars as custom tick marks.

  26. The macro is not working properly as I am getting a runtime error 5. Any ideas as to why? Thanks.

  27. Did you get any indication of which command failed? Did you see whether the code in this article works?

  28. The debugger highlights this line: “ChDrive sPathName”.
    Could this be a problem with the 64 bit XP that I am running? I have had problems previously with other programs. Since I am in a hurry I am just manually using the “ActiveChart.Export” command, but I would really like your macro for future use. Also, on a side note, does this command have any option to control the resolution? I would prefer some higher resolution TIFF files.

  29. I’m not sure why that line would fail. I have a trap that bypasses the line if sPathName is “”, or rather, if the length of the string in sPathName is zero. Is the workbook stored on a network share, perhaps, or opened from email or from a zip file?

    You could change the section of code to

    On Error Resume Next
        ChDrive sPathName
        ChDir sPathName
    On Error Goto 0

     
    The charts exported as image files have no greater resolution than the screen.

  30. Could it be SP3?

    Cheers,

    Bob

  31. SP3 of ?? I don’t see how that would be a problem. I did just notice the problem occurred on 64-bit XP, but to have this fail on such a core function boggles the mind. Although Microsoft has boggled many minds with various upgrades and updates.

  32. Kenneth Farrell says:

    New problem:
    I am attempting to revisit some old data files of stress-strain curves obtained in a tensile machine controlled by a Visual FoxPro program based on MS DOS Windows 3.1. The data were collected by Visual FoxPro in six columns with headings. Each tensile test contained between 1000 and 5000 rows. The collective outputs were saved in two large folders, one formatted in .DBF and the other in .CDX, both under the same test specimen identity numbers. I have not been able to retrieve sensible data from the CDX files. The DBF folder, which contains data for 171 tensile tests, has a size of 18,294KB. When viewed in ASCII format in MS Word it is in continuous text style and comprises 3956 pages with 56 lines per page. In the past, I was able to access individual tensile test files in the folders by opening them into a copy of the FoxPro program on my personal computer. The program was initiated via a c:\> prompt at which I typed tensile. On my present computer I can not get a c:\> prompt. The only DOS prompt available seems to be c:\WINDOWS, which does not respond to the tensile command nor to any other opener word I have tried.
    When this large DBF file is opened directly into my Excel2003, only the first part of the file, just sufficient to fill all 65,500 rows in a single Excel worksheet, is converted. At least, that is all I can recover. If the remainder of the folder was converted I can not find it. It does not scroll over into successive sheets in the Excel workbook. By counting the contents of the single successful sheet, I estimate that seven Excel worksheets will be needed to hold (in properly-converted form) the entire contents of the DBF folder. I have tried to break down the folder contents into smaller blocks each sized to fit on a single xls worksheet. When opened in Excel, the results are gibberish that neither I nor the Text Import Wizard can make sense of.
    Is there a simple solution to this problem? Preferably in Excel2003. I have never written a Macro but I would be willing to try to write one directing Excel to display all the results of the file conversion by scrolling them onto successive worksheets, if such a route might work. Better still, could a Macro direct the data to be tiled on a single worksheet? Presently, the single sheet conversion is a continuous list of 28 tests 65,500 rows deep but only 6 columns wide. The remaining 250 columns are unoccupied. All of the 171 tests in the DBF folder would fit comfortably if tiled on a single worksheet.
    I have read several weighty tomes on Excel2003 but they offered no help with my problem.
    KenF.

  33. Excellent! This works great, and I could never have done it myself. Thanks :)

  34. Matthew McGlone says:

    Thanks for the great tool. Is there a way to specify the desired output resolution? I need a specific size (x,y) and find that resizing in Paint or other graphic editors to distort the chart image.

  35. Matthew –

    You need to make the chart the proper size to begin with. You should do int manually, and then make sure the chart looks okay.

    To do it programmatically, before exporting multiply the size in pixels by 0.75 to specify the size in points (for a typical monitor setting). For example, to export the chart as a 400×600 pixel image:

    ActiveChart.Parent.Height = 400 * 0.75
    ActiveChart.Parent.Width = 600 * 0.75

  36. Thank you a lot! It’s great!

  37. Yea, works great on 2007 – copied into library folder – then clicked on the “start” button (top left) > Excel Options > Addins. Might need to restart excel to get the Add Ins tab showing.

  38. Fantastic Add-In!

  39. Very useful & thoughful add-in. Thank you!

  40. The add-in works great. I’d love to be able to use it in converting Excel data into graphs and then to images which are finally captured in a video format. Excel can do the first part, the add-in the second part and Movie Maker the final part. The challenge is to automate it so that the thousands of Excel records can be converted to images each with it’s own file name (say an incremented number). It could be a great way to review a profile that varies over time. Any help for a non-programmer?

  41. Hi Pete –

    I once built a project that did this for a client, and it worked much as you envision.

    The code to export a series of charts would go something like this:

    Sub ExportSeriesOfCharts()
      Dim sFileName As String
      Dim sFileExt As String
      Dim sPathName As String
      Dim sPrompt As String
      Dim iChart As Long
    
      If ActiveSheet Is Nothing Then GoTo ExitSub
      If ActiveChart Is Nothing Then GoTo ExitSub
    
      sPathName = "C:\MyPath\"
      sFileName = "MyChart"
      sFileExt = ".png"
    
      For iChart = 1 To 999
        ''''''''''''''''''''''''''''''''''''''
        ' code to update the chart goes here '
        ''''''''''''''''''''''''''''''''''''''
    	
        sChartName = sPathname & sFileName & Format(iChart,"000") & sFileExt
        ActiveChart.Export sChartName
      Next
    
    ExitSub:
    
    End Sub
  42. Works great but I see no visual difference at all between .gif, .png, .jpg of the same graph. However the file sizes are all different. Any comments?

    I need to be sure that a gif is really a gif and a png is a png etc.

  43. Get IrfanView, a free image editing software package. It will tell you what format is used for a given image. I’ve never known an exported chart to use a different format than I expected.

    I find in 2007, it’s hard to tell the types apart, they all are pretty unattractive. In 2003, PNGs and GIFs look much the same, just like on the monitor, while the JPGs look a bit muddy around the text. It depends on the antialiasing and ClearText, which is more pronounced in 2007 and in Vista/Windows 7.

  44. Thanks. IrfanView confirms they are all that they claim to be. Many thanks for a great add-in.

  45. Hi Jon

    I am introducing this at work but I am getting an error, but only on the first save after opening Excel (2007):
    Run time error ‘5’ Invalid procedure call or argument.

    The program runs and image file is created ok but the debug stops at the end
    ExitSub:
    *ChDrive sCurDir

    Also after opening a different Excel file sometimes I first get:
    Run time error ’76’ Path not found.

    This time the image file is NOT created and the debug stops at
    ExitSub:
    ChDrive sCurDir
    *ChDir sCurDir

    Then I try again and get the other error (‘5′) as above, and the image IS created.

    On all subsequent image saves there is no problem eg if I am saving other images in the same workbook there are no more errors and it works fine.

    You had a similar comment back in March 2009 with a code suggestion.

  46. David –

    You could simply comment out those two lines. Their purpose is to restore the original active path (the default in the Save As or Open dialog) after the user navigated somewhere else to export the file. Changing this back is no big deal, since it doesn’t change back if the user browses around in the Save As or open dialog.

  47. Hi Jon,

    I commented the two lines out. Problem fixed, until today when the debug stopped at an early point … ChDrive sPathName.

    So I now have intermittent problems at two points, both regarding the ChDir/ChDrive. This is on a network, so that could be the problem, however I am using mapped drive letters.

    Anyway, if I just want to bypass the sections only when there is an error with
    On Error Resume Next and On Error Goto 0, where exactly should I put these ?

    These are the two code sections:

    If Len(sPathName) > 0 Then
    ChDrive sPathName
    ChDir sPathName
    End If

    >>>>>>

    ExitSub:
    ChDrive sCurDir
    ChDir sCurDir
    End Sub

    Or do you think I should comment out these secions completely?

  48. David –

    You would put “On Error Resume Next” before ChDrive and “On Error Goto 0″ after ChDir.

    This behavior is troubling. Insert “Debug.Print sPathName” before the first ChDrive and “Debug.Print sCurDir” before the second. This will put sPathName and sCurDir into the Immediate window, so you can check that these are valid paths.

  49. Jon

    I have replicated the second instance (the SCurDir problem).

    The default file location in my Excel save options is set to the full network pathname \\….. And it causes an error – but only on the first time in any new file opened. It then picks up the current working directory as the new ‘default’ and all is fine on subsequent runs from the same file.

    I have changed my Excel save default location to the mapped letter and it causes no problem.

    Maybe the \\…. location is also causing the sPathName problem also – I don’t know, I can’t replicate the problem.

    Anyway, I’ll wrap the On Error … around them both as I don’t think it will be a big issue if these get skipped when other users happen to use network paths.

    But the info may be useful to you.

  50. David –

    This might work better for a network location (and it works fine locally). It uses a single call instead of two (ChDrive and ChDir).

    In the declaration section of the code module, insert this:

    Private Declare Function SetCurrentDirectoryA Lib _
        "kernel32" (ByVal lpPathName As String) As Long

    Then place this function in the module:

    ' use instead of ChDrive and ChDir together when it's a network share
    ' also works with local file system, so it's a complete replacement
    Sub ChDirNet(szPath As String)
        Dim lReturn As Long
        lReturn = SetCurrentDirectoryA(szPath)
        If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path."
    End Sub
  51. Hi Jon,

    Is there a way to put a chart on the desktop that will be updated when the chart is updated in excel? The add-in works great but doesn’t update when the chart is updated.

  52. The exported chart is a static image. By itself, it can’t update.

    However, you could modify the code to run whenever the chart’s data changed, so a fresh image of the chart is exported.

  53. Jon, I’m really new to this. Would you point me in the right direction.

  54. Look up the Worksheet_Change event procedure. This fires when contents of cells in a worksheet are changed (but not when formula values change). Make it ignore changes unless they occur within the chart’s source data. When the source data changes, run the export code.

  55. Above my pay grade :)

  56. Jon,

    I have a chart that I want to export using the code you’ve provided here. I find that I want to resize the chart, but when I do, the titles, axis titles, etc. look really strange and the chart is not presentable. What I’ve done is something like this

    Dim aCht as Excel.Chart

    Set aCht = ActiveChart
    aCht.Height = something
    aCht.Width = something else

    Is there some switch I need to change so that they resize well?

    Thanks,
    Barb

  57. Correction, I need to use aCht.ChartArea.Height and .width, but I still have the title issue.

  58. Jon, I just found this on your site

    However in my version of Excel 2007, none of the format font screens include an option for autoscaling. I’m sure I’ve seen it before, so I wonder what and when it changed.

    My screen now includes
    Small Caps
    Large Caps
    Equalize character height.

    An image of my font screen is shown at the link.

    http://yfrog.com/n6formatfontp

  59. Barb –

    That’s funny, I thought it was there. Of course, when Microsoft decided they didn’t need to keep a font tab on the Format dialog for each chart element, they went to a less specialized font screen, and font autoscaling makes no sense in the context of a worksheet.

    The AutoScaleFont property still exists for a chart’s font elements, but it’s grayed out, meaning it won’t do anything (I tried), but it won’t show an error.

    You can still do something like this:

    ChartElement.Font.Size = ChartElement.Font.Size * Chart.NewHeight / Chart.OldHeight

    In general, the best thing to do is make your chart the right size for its intended use. If you need one configuration for on screen, and another for exporting, make two charts, one formatted for its role.

  60. Thanks for your help Jon. I hadn’t thought about resizing that way. Still a pain though. I think I’ll have to size them as I want to show them.

    Thanks again,
    Barb

  61. Hi Jon, We are finding that the output (we use .gif) has a border around the graph when we create a pdf (Adobe). There is no visible border in the windows viewer or internet explorer and the web chart always looks fine without borders – which is what we want.

    We can also see the border in MSPaint.

    Can the border be switched of (assuming there is an invisible border). Someone in our office suggested we look at the chart properties? Maybe there’s some code you can add or suggest?

    regards, David

  62. David –

    If there is no visible border, then I imagine there’s an issue behind the scenes with the export protocol. Do you get the same border if you export using PNG or BMP? What if you maually copy and paste the chart?

  63. Hello Jon

    This is a very usefull tool.

    In my case, i am trying to save a huge chart. Your tool saves it but in a truncated way. Is there any way how i can overcome this?
    Thanks

  64. Hi Raj –

    What version of Excel are you using? How large (rows and columns of the underlying worksheet) is the chart?

    As I recall, this is a limitation not of memory but of some arbitrary pixel size, in recent but not the latest Excel versions.

    I just tested this with a chart that covered A1:CV200 (200 rows x 100 columns, 3400 x 6400 pixels). Exported as a PNG, the entire chart was exported with no truncation in Excel 2003 SP3.

  65. Jon,
    Great tool, thanks for providing it – works like a charm.

    Question – I have a need to update a new graph for each of about 1500 records, export the individual graphs using your add-in, and saving them with unique identifiers so I can put that file name reference as a field in each record – ultimately this is for a merge into publisher. Any ideas how to do this in an automated way?

    Thanks again for the add-in!
    Diane

  66. This code is helpful for saving bitmaps directly from Excel, but I’d like to achieve higher resolution files.

    The method below achieves the best results, so far, but requires many manual steps. Do you have any ideas for automating it with code? Ideally, the macro would save all charts (including groups) from a workbook as EMFs in the same folder where the file is located. (Saving the EMFs as PNGs would be great, too, but probably requires code outside Excel.)

    This is using Excel 2007.

    * Select chart (or group), “Copy as picture” -> “As shown on screen” / “Picture”
    * Paste that image into PowerPoint
    * Right click the image and select “Save as Picture…”, save as EMF format
    * Open the EMF and save as PNG or TIFF, depending on needs (I did this with the default Windows Picture and Fax Viewer, because it’s what I have installed)

    I tried the code from here (http://www.xcelfiles.com/EMF_Save.html), which will save the selected object as EMF, but confusingly, the resultant EMF file is of lower resolution than that which is obtained by pasting into PowerPoint as a middle-man. I don’t understand this.

    Any ideas are much appreciated!

  67. Thanks for the post, Jon. I’ve had some success building macros around the basic Chart.Export command which saves a selected range to PNG.

    What I’ve noticed, however, is that the quality of the PNG image is significantly worse with this method than if I just copy and paste the selection to Paint and save as PNG.

    Which is a pity, as I’d love to automate the export process.

  68. Hi Sven –

    I have not noticed much different. What version of Excel are you using?

  69. Hi Jon,

    Excel 2007.

  70. I should add that what I’m doing is pasting a Charley Kyd-style dashboard, which has a bunch of camera objects and small charts, into a new empty chart and exporting that to PNG using Chart.Export. So maybe it’s the charts-within-charts that’s not working well when using this method, compared to the manual cut-n-paste to Paint.

  71. Sven –

    The camera tool in 2007 is atrocious. The pictures of charts look like a child drew them with crayons.

  72. Lol, ok then. I’ll see if I can get upgraded to Office 2010.

  73. Hi Jon,

    Thanks so much for your nice web, I’m learning a lot.

    I have Office 2010. I have installed this Add-in (I see it in the Add-in list on Excel), but I cannot find the export button anyway…. It seems that this Office 2010 has a special ability to hide everything.

    Could you help me with this?

    Thank you.

  74. Manuel –

    Excel 2007 and 2010 add a tab named Add-Ins to the ribbon, and put the Export Chart button on that tab.

  75. Jon,

    I’ve been using the add-in for a while but I could never get the PNG images to look right. There was always a lot of black where I didn’t expect it. The other day it occurred to me to check the “fill” options for the chart area. I had “No Fill.” When I changed it to white everything is fine.

    Thanks for the useful add-in!

    Best,

    Win

  76. Excellent !

    This Add-In is perfect for exporting excel charts.
    Especially handy for creating images for publications.

    Thanks
    FM

  77. Thanks very much, this is brilliant and what I have been looking for all over!!

  78. I have been looking for something like this. Is there a way to have a chart with datasources automatically refresh and then output the file? That would be slick!

  79. Incorporate this exporting code into your procedure that updates the chart’s data source.

  80. Jon You are a legend! I’ve been trying to put charts in my blog for ages and in the end I was printing them, scanning them and then inserting them as jpegs. This add in is awesome, thanks alot for making it freely available. I’m now going to uninstall Googledrive which didn’t help at all (contrary to advice on the web). I can’t believe Microsoft doesn’t have this as a standard feature, but I guess that’s because there’s no $ in it… Cheers, Adam

  81. Thanks a lot! Brilliant, saves me a lot of mouse clicks!

  82. Hi Jon
    Awesome add-in, thanks. A quick tip for those having resolution issues. Zoom in to make the chart look huge on screen, then the output resolution is much better. If the chart looks small on screen, the resolution will be much worse.

  83. Daniel –
    I have thought of trying this, but haven’t investigated. Do you change the zoom, or stretch the actual chart? And do you have to then shrink the chart in an image editor?

  84. Jon,

    First – great functionality. As someone mentioned before, ideal for publication figures.
    Second – do you know of any way to automate the outputting of the image file? I’ll be regularly updating the chart data source through SAS and am hoping there’s a way for the image file to update without me going into excel and manually exporting the chart each time.
    Jon

  85. How is the data being updated? If there is some event that you can watch for in VBA, then you can put the export code into an event procedure.
    For example, if updating the data forces a recalculation, you could put code into the worksheet code module that goes something like this:

    Private Sub Worksheet_Calculate()
        ' code to export the chart...
    End Sub
  86. I have used this plugin before successfully but seem to have an issue now.

    I am running Excel 2011 on a Mac.

    I get Run time error 68
    Device unavailable.

    The Debugger highlights ChDrive sPathName.

    I apply fix you mentioned Mar 25 2009.

    Then I get
    Run-time error 1004
    Method ‘GetAsFilename’ of object ‘_Application’ failed

    Next steps?

  87. Sean –

    I have not tried this on a Mac. My MacBook had a hard disk error, and I needed to reinstall everything. I’ve reinstalled OSX but not yet Mac Office.

Subscribe without commenting

Trackbacks

  1. […] January 26, 2009 I ran across a very useful little add-in for Excel today.  My hatred for Microsoft notwithstanding, I’m forced to use Office for much of what I do at my job.  Several times over the past year, I’ve found myself needing to include charts from Excel in presentations, documents, or even just through email.  Most people send the entire spreadsheet or just copy and paste it as a BMP.  I’ve found that a lot of the time, it’s nearly impossible to read or just a royal pain to deal with.  No longer a problem. […]

  2. […] ذخیره کنید. دریافت افزونه Export Chart منبع این افزونه این سایت […]

  3. How Can I Save A Graph To A PNG Or GIF File In Microsoft Excel? | Click & Find Answer ! says:

    […] Enhanced Export Chart Procedure […]

Write a Comment

I welcome comments from my readers. If you have an opinion on this post, if you have a question, or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion. If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.

Subscribe without commenting

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.