Peltier Tech

Peltier Technical Services - Excel Charts and Programming

Enhanced Export Chart Procedure

by 99 Comments

Monday, June 9, 2008 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.
 

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.

Export Chart Procedure

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 sCurDir As String
  sCurDir = CurDir

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

  Dim sPathName As String
  sPathName = ActiveWorkbook.Path
  If Len(sPathName) > 0 Then
    ChDrive sPathName
    ChDir sPathName
  End If

  Dim sFileName As String
  sFileName = "MyChart.png"

  Do
    Dim sChartName As String
    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)

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

    Dim iOverwrite As Long
    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

Add and Remove CommandBar Button

The code to add a button to the bottom of the Chart menu, and to remove the button, is pretty simple. Note that this uses the old CommandBar user interface of Excel 2003 and earlier. In Excel 2007 and later on Windows, a new ribbon tab called ‘Add-ins’ is added with an ‘Export Chart’ button. On a Mac, this will not add a button anywhere. You could also manually customize the ribbon and add the ExportChart routine to a button.

Sub AddExportChartMenuItem(Optional bDummy As Boolean = True)

  RemoveExportChartMenuItem

  Dim vMenu As Variant
  vMenu = "Chart Menu Bar"

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

  With MyPopup
    Dim MyButton As CommandBarButton
    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.

Export Chart Add-In

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.

Posted: Monday, June 9th, 2008 under VBA.
Tags: , , , .
Comments: 99

Peltier Tech Charts for Excel

Microsoft MVP Logo

Reader Interactions

Comments

  2. Jon Peltier says

    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. Serhat says

    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?

  5. Serhat says

    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. Jon Peltier says

    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. Tony Rose says

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

  10. Jon Peltier says

    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
 

  14. Jon Peltier says

    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. Sue says

    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. Ben Armine says

    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. Jon Peltier says

    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. Jon Peltier says

    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]

  20. Jon Peltier says

    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. stan says

    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?

  23. Charlie says

    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. Kenneth Farrell says

    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. Jon Peltier says

    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.

  28. Andrew Smyth says

    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. Jon Peltier says

    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.

  31. Jon Peltier says

    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.

  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. Jon Peltier says

    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

  37. Ian says

    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.

  40. Pete says

    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. Jon Peltier says

    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. David says

    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. Jon Peltier says

    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.

  45. David says

    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. Jon Peltier says

    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. David says

    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. Jon Peltier says

    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. David says

    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. Jon Peltier says

    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. E says

    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. Jon Peltier says

    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.

  54. Jon Peltier says

    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.

  56. Barb Reinhardt says

    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

  58. Barb Reinhardt says

    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. Jon Peltier says

    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. Barb Reinhardt says

    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. Anonymous says

    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. Jon Peltier says

    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. Raj says

    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. Jon Peltier says

    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. Diane says

    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. Chris Z. says

    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. Sven says

    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.

  70. Sven says

    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.

  73. Manuel says

    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.

  75. Win Smith says

    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

  78. Limits says

    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!

  80. adam says

    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

  82. Daniel says

    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. Jon Peltier says

    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 says

    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. Jon Peltier says

    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. Sean says

    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?

  88. Franz Klein says

    Dear Jon,
    back in 2010 Raj wrote:
    …. 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?

    You answered: … 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.

    Raj didn’t follow up on that, but I unfortunately have to confirm the nasty observation. This happens with the simple version of the code for export.
    …
    myChart.Export filename:=ThisWorkbook.Path & “\” & myFileName, Filtername:=”PNG”
    …
    Using Excel from Office 2011 for Mac I am consistently getting .png images with truncated charts (on every Mac I tried). The problem is related to chart size vs screen size. If the chart is bigger than the screen the export function leads to truncated the chart (e.g. on an iMac 2012, 27inch screen). Interestingly, when you manually “Save as Picture…”. the .png can get much larger than screen size. If the chart is viewed with lower magnification, so that it fits the screen, export does not truncate it, but the resolution is reduced correspondingly. There should be a way to code it, that produces the same result a the manual command – no? (p.s. also the manual command has its limits, somewhere before the chart becomes 10x the screen width Excel freezes, although file size would hardly exceed 10Mb)

    Any insights very much appreciated,
    Franz

  89. Peter Wilde says

    The add-in fails in Excel 365 (aka 2016) with the following error:
    Run-time error ‘5’: Invalid procedure call or argument.

    The debugger indicates the error occurs in Nodule1 in Sub ExportChart() at the line: ChDrive sPathName

    It has been three years since anyone posted here.

  91. Peter Wilde says

    Hi,
    It is a statement in the beginning of the VBA code for your ExportChart.xla file:

    ‘=========================================================================
    ‘ Peltier Technical Services, Inc., Copyright © 2008. All rights reserved.
    ‘=========================================================================
    Option Explicit
    ‘=========================================================================
    Public Const myChartExportMenu As String = “E&xport Chart”
    ‘=========================================================================
    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

    When the error occurs, a small window comes up which has the Debug button. Clicking that takes you to the VBA for Applications windows, and the line is yellow highlighted. When I installed the add-in, I just followed the procedure of adding it through the Manage Add-ins tool available through Excel settings.

  94. Jon Peltier says

    Hmmm, is that a SharePoint address?

    Try this. Select a chart and run this procedure:

    Sub TestExport()
    ActiveChart.Export “\\ADHServer\Projects\030 – Contra Costa Clean Water Program\” & _
    “SSID_Studies\Marsh_Creek_Fish_Kill_Study\LoggerProgram\MBW_StageChange_Analysis.png”
    End Sub

    If you don’t get a chart, it might be a problem with permissions. Using a folder in “My Documents” on your local PC may work.

  95. PETER WILDE says

    It works perfectly from My Documents. The problem may have something to do with path length as the directory on the server in question is wide open with no security enabled.

    Thanks for your responses to this. It is a great add-in.

  96. Jon Peltier says

    I doubt it’s path length; the full path and file I had you try was 143 characters.

    It might be an issue with the network address (starting with two back slashes), though I don’t really know why.

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. […]

  3. […] Enhanced Export Chart Procedure […]

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Peltier Tech Charts for Excel