Increase resolution of a Chart exported as picture using VBA

B

bofonomo

Board Regular
Joined
Feb 4, 2010
Messages
114
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?
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
Do you have to export it as *.png ?

What about *.pdf, which is a vector graphics format ?
Or something similar ?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
​
 
Upvote 0
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.
Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

AOB
  • Solved
Error Handler invoked but no error number, description or source?
Replies
2
Views
417
AOB
AOB
WildBurrow
  • Solved
Export/Save selected charts(pictures) as image, if file exists add unique number
Replies
1
Views
2K
WildBurrow
WildBurrow
E
  • Question
Can anyone help me get my VBA code working? I have spent tons of time but can't quite fix it.
Replies
3
Views
2K
profray
P
Martin_H
  • Question
Export charts from active sheet [VBA]
Replies
1
Views
963
smozgur
smozgur
V
  • Question
Updating Chart Source using Word VBA
Replies
0
Views
2K
vante
V

Forum statistics

Threads
1,214,643
Messages
6,120,707
Members
448,981
Latest member
recon11bucks

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top