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:
- Export Chart as Image File
- Robust VBA Save-As Technique
- VBA to Split Data Range into Multiple Chart Series
- File Name Functions
- List VBA Procedures by VBA Module and VB Procedure
- Build an Excel Add-In 6 – Interface for 2003
- Installing an Excel Add-In
- Image File Type Comparison of Exported Charts
- Get Open or Save-As Filename
- Time Trials of Approaches to Measure Minimum and Maximum Chart Values
Posted: Monday, June 9th, 2008 under VBA.
Comments: 63
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:
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:
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 LongThen 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.
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.