Peltier Tech Blog

Excel Chart Add-Ins | Training | Custom Solutions | Charts and Tutorials | PTS Blog

 

Main menu:

 
Peltier Tech
Chart Add-Ins

Peltier Tech Waterfall Chart Utility
Peltier Tech Cluster-Stack Chart Utility
Peltier Tech Box and Whisker Chart Utility
Peltier Tech Marimekko Chart Utility
Peltier Tech Dot Plot Utility
Peltier Tech Cascade Chart Utility

 
Excel Dashboards

Subscribe

Site search

Subscribe

Site search


Recent Posts

Recently Commented

Popular Posts

Archive


 

Categories


 

Privacy Policy

Creative Commons License
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.

Enhanced Export Chart Procedure

by Jon Peltier
Monday, June 9th, 2008
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.

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.

Related Posts:

Bookmark and share this entry:
  • Twitter
  • Digg
  • Facebook
  • LinkedIn
  • del.icio.us
  • Technorati
  • StumbleUpon
  • Google Bookmarks
  • Reddit
  • MySpace
  • Slashdot
  • Yahoo! Buzz
  • Tumblr

Learn how to create Excel dashboards.

Comments


Comment from Consultant Ninja
Time: Monday, June 9, 2008, 9:04 am

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


Comment from Jon Peltier
Time: Monday, June 9, 2008, 9:34 am

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


Comment from Serhat
Time: Tuesday, June 10, 2008, 10:55 am

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?


Comment from Jon Peltier
Time: Tuesday, June 10, 2008, 11:27 am

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


Comment from Serhat
Time: Tuesday, June 10, 2008, 1:03 pm

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?


Comment from Tony Rose
Time: Tuesday, June 10, 2008, 2:29 pm

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


Comment from Jon Peltier
Time: Tuesday, June 10, 2008, 2:39 pm

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


Comment from Tony Rose
Time: Tuesday, June 10, 2008, 2:48 pm

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


Comment from Eric Chan
Time: Wednesday, June 11, 2008, 12:33 pm

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


Comment from Jon Peltier
Time: Wednesday, June 11, 2008, 12:37 pm

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
 


Comment from Eric Chan
Time: Wednesday, June 11, 2008, 12:45 pm

Can you please post the function for FullNameToPath() as well?
Thank you!


Comment from Jon Peltier
Time: Wednesday, June 11, 2008, 1:24 pm

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

File Name Functions


Comment from Eric Chan
Time: Wednesday, June 11, 2008, 3:34 pm

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


Comment from Jon Peltier
Time: Wednesday, June 11, 2008, 3:52 pm

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


Comment from Sue
Time: Friday, July 11, 2008, 3:09 pm

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.


Comment from Ben Armine
Time: Monday, August 11, 2008, 9:01 am

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?


Comment from Jon Peltier
Time: Tuesday, August 12, 2008, 8:54 am

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”


Comment from Jon Peltier
Time: Tuesday, August 12, 2008, 8:55 am

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]


Comment from Charlie
Time: Tuesday, September 9, 2008, 12:05 am

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.


Comment from Jon Peltier
Time: Tuesday, September 9, 2008, 7:42 am

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.


Comment from stan
Time: Tuesday, September 9, 2008, 6:39 pm

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?


Comment from Jon Peltier
Time: Tuesday, September 9, 2008, 8:08 pm

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


Comment from Charlie
Time: Wednesday, September 10, 2008, 3:56 am

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


Comment from Kenneth Farrell
Time: Saturday, January 10, 2009, 7:46 pm

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


Comment from Jon Peltier
Time: Saturday, January 10, 2009, 11:13 pm

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.


Pingback from Export Excel Chart as .PNG « Hacks Ahoy
Time: Monday, January 26, 2009, 6:52 pm

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


Comment from Andrew Smyth
Time: Wednesday, March 25, 2009, 10:53 am

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


Comment from Jon Peltier
Time: Wednesday, March 25, 2009, 11:19 am

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


Comment from Andrew Smyth
Time: Wednesday, March 25, 2009, 1:44 pm

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.


Comment from Jon Peltier
Time: Wednesday, March 25, 2009, 6:14 pm

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.


Comment from Bob
Time: Sunday, March 29, 2009, 10:09 pm

Could it be SP3?

Cheers,

Bob


Comment from Jon Peltier
Time: Sunday, March 29, 2009, 10:19 pm

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.


Comment from julian
Time: Monday, June 1, 2009, 4:39 am

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


Comment from Matthew McGlone
Time: Tuesday, August 25, 2009, 5:30 pm

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.


Comment from Jon Peltier
Time: Thursday, August 27, 2009, 1:26 pm

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


Comment from S.
Time: Thursday, November 19, 2009, 11:25 am

Thank you a lot! It’s great!


Comment from Ian
Time: Wednesday, February 10, 2010, 6:57 am

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.


Comment from Ed
Time: Wednesday, February 24, 2010, 5:34 pm

Fantastic Add-In!


Comment from Pradheep
Time: Thursday, March 11, 2010, 5:03 pm

Very useful & thoughful add-in. Thank you!


Comment from Pete
Time: Thursday, April 22, 2010, 3:52 pm

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?


Comment from Jon Peltier
Time: Thursday, April 22, 2010, 8:12 pm

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


Comment from David
Time: Sunday, April 25, 2010, 4:55 pm

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.


Comment from Jon Peltier
Time: Sunday, April 25, 2010, 6:46 pm

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.


Comment from David
Time: Monday, April 26, 2010, 3:09 am

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


Comment from David
Time: Monday, May 10, 2010, 7:21 pm

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.


Comment from Jon Peltier
Time: Monday, May 10, 2010, 10:43 pm

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.


Comment from David
Time: Wednesday, May 26, 2010, 11:10 pm

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?


Comment from Jon Peltier
Time: Thursday, May 27, 2010, 6:02 am

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.


Comment from David
Time: Monday, May 31, 2010, 6:44 pm

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.


Comment from Jon Peltier
Time: Monday, May 31, 2010, 9:01 pm

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


Comment from E
Time: Wednesday, July 14, 2010, 6:50 pm

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.


Comment from Jon Peltier
Time: Wednesday, July 14, 2010, 9:04 pm

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.


Comment from E
Time: Thursday, July 15, 2010, 7:40 am

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


Comment from Jon Peltier
Time: Thursday, July 15, 2010, 10:01 am

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.


Comment from E
Time: Thursday, July 15, 2010, 1:42 pm

Above my pay grade :)


Pingback from چگونه یک نمودار در Excel را به عکس تبدیل کنیم؟ | ساري گلين
Time: Monday, August 16, 2010, 2:16 pm

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


Comment from Barb Reinhardt
Time: Wednesday, August 18, 2010, 1:43 pm

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


Comment from Barb Reinhardt
Time: Wednesday, August 18, 2010, 1:48 pm

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


Comment from Barb Reinhardt
Time: Thursday, August 19, 2010, 6:55 am

Jon, I just found this on your site

http://peltiertech.com/Excel/Charts/FixFonts.html

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


Comment from Jon Peltier
Time: Thursday, August 19, 2010, 9:40 am

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.


Comment from Barb Reinhardt
Time: Thursday, August 19, 2010, 9:57 am

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


Comment from Anonymous
Time: Thursday, August 26, 2010, 2:19 am

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


Comment from Jon Peltier
Time: Thursday, August 26, 2010, 12:27 pm

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?

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.

Read the PTS Blog Comment Policy.





Subscribe without commenting

Peltier Tech Waterfall Chart Utility Peltier Tech Cluster-Stack Chart Utility Peltier Tech Box and Whisker Chart Utility Peltier Tech Marimekko Chart Utility Peltier Tech Dot Plot Utility Peltier Tech Cascade Chart Utility

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