Increase resolution of a Chart exported as picture using VBA

[bofonomo]

Hello Excellers,

I have a spreadsheet where I want to export the charts using VBA, which I've been able to do using this:

Dim oCht As Chart
ActiveSheet.ChartObjects("Chart 3").Select
Set oCht = ActiveChart
On Error GoTo Err_Chart
oCht.Export Filename:="C:\Documents and Settings\All Users\Desktop\PopularICON.png", Filtername:="png"

Err_Chart:
If Err <> 0 Then
Debug.Print Err.Description
Err.Clear
End If

However, due to the size of the chart, the export picture size is quite small and when enlarged to use in a document becomes blurry. I know that a way round it is to manually make the chart bigger but then I have to change all the font sizes and sizes of the points etc, which I don't want to do because there are quite a few charts.

Is there something I can add to the macro to export a chart as a larger picture?

Possiblely stop screen updating and then resize the pictures, export, the resize them back to the original size, and enable screen updating?

 

[Gerald Higgins]

Do you have to export it as *.png ?

What about *.pdf, which is a vector graphics format ?
Or something similar ?

 

[bofonomo]

Pdf would be fine, I didn't realise you could export charts as pdfs.

I am currently having to screen capture and crop each graph when putting into word, which is a chore.

I just want the graphs to be exported automatically as a file that can be easily imported into word (or any thing else), so pdf would be fine.

 

[Gerald Higgins]

OK well if you do a search on this board, you should find various ways of creating pdfs from Excel.

 

[bofonomo]

I would prefer to save it as a jpg, or png, but higher resolution, is this not possible?

I've tried the pdf approach, however the file size of pdf'ed charts are considerably larger and would make a document a little too large.

 

[VoG]

This may be of interest http://peltiertech.com/WordPress/image-file-type-comparison-of-exported-charts/

 

[bofonomo]

It still doesn't mention anything about the actual picture size?

If you export a graph of of size 11 by 17 cm from excel the image will be that size. Is there not a way of exporting it larger with out the proportion of the fonts etc changing?

Regards

 

[bofonomo]

From the link above:

"PNG/GIF files from 2007 are larger in resolutions and the htm codes generated by Excel resize them to fit into the original dimensions as seen in Excel.
This is when the blurriness happens."

That's a bummer. Perhapse would be easier/better to make a macro to export them directly as objects to Word?
​

 

[Gerald Higgins]

I haven't done this with Word, but I've set up something that exports Excel charts to Powerpoint, one at a time, and I would guess it can be made to work for Word.

I developed it based on some code I found somewhere on this board - can't remember exactly where - thanks to whoever it was that developed it.

This works for charts created on their own Exel sheet, rather than charts embedded in a standard worksheet.

Sub Export_Charts_to_Powerpoint()
Dim YesNo As String
YesNo = MsgBox("            For this to work, you MUST have the Powerpoint Exec Summary file OPEN, and NO OTHER Powerpoint file open.                                                                                             Do you want to proceed ?", vbYesNo, "CAUTION")
If YesNo = vbYes Then
'===== Uses Early Binding to the PowerPoint Object Model
'===== Set a VBE reference to Microsoft PowerPoint Object Library
Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim SlideCount As Long
'===== Make sure a chart is selected
Sheets("Monthly Trends").Select
'== If ActiveChart Is Nothing Then
'== MsgBox "Please select a chart and try again.", vbExclamation, "No Chart Selected"
'==Else
'===== Reference existing instance of PowerPoint
Set PPApp = GetObject(, "Powerpoint.Application")
'===== Reference active presentation
Set PPPres = PPApp.ActivePresentation
PPApp.ActiveWindow.ViewType = ppViewSlide
'===== Reference active slide
Set PPSlide = PPPres.Slides(PPApp.ActiveWindow.Selection.SlideRange.SlideIndex)
'===== Add another slide
SlideCount = PPPres.Slides.Count
Set PPSlide = PPPres.Slides.Add(SlideCount + 1, ppLayoutBlank)
PPApp.ActiveWindow.View.GotoSlide PPSlide.SlideIndex
'===== Copy chart as a picture
ActiveChart.CopyPicture Appearance:=2, Size:=xlScreen, Format:=xlPicture
'===== Paste chart
PPSlide.Shapes.Paste.Select
'===== Align pasted chart
PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
'PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
PPApp.ActiveWindow.Selection.ShapeRange.Top = 65