Chart Size

[Eric Van Buren]

I'm using this routine to export charts as a gif file but I would like
to make all of the charts the same size. Width 369px and Height 249px.
Could someone show me how to add this? Thanks.

Sub ExportAllCharts()

Dim iCompanyCount As Integer
Dim iCompany As Integer

Application.Calculation = xlCalculationAutomatic

iCompanyCount = Sheet1.Range("A1").CurrentRegion.Rows.Count - 1

For iCompany = 1 To iCompanyCount

Application.StatusBar = "Exporting Chart " & iCompany & " of "
& iCompanyCount

Sheet3.Range("A2").Value = iCompany
Chart2.Export "c:\mydir\" & Sheet3.Range("D2").Value & ".gif",
"GIF"
Next

Application.StatusBar = False

End Sub

 

[Thomas Wieser]

Hi Eric,

I'm using this routine to export charts as a gif file but I would like
to make all of the charts the same size. Width 369px and Height 249px.
Could someone show me how to add this? Thanks.

I have the same problem, I have just started a new thread below.

The problem is, that the Export method has no other available arguments
to set the size. So I tried a workaround: I set the with and height of
the chart object, and then I do the export:

Worksheets(1).ChartObjects(1).Width = 3600
Worksheets(1).ChartObjects(1).Chart.Export FileName:="C:\test.png", _
FilterName:="PNG", _
Interactive:=True


The problem now is, that the width of the new PNG file is 4800 pixels
instead of 3600 pixels, and I don't know why... do you have larger
experience with this Export method?


Regards, Thomas

 

[Eric Van Buren]

Thomas - Sorry, I don't have any experience with doing with the Export
method. Although your workaround is interesting to me. Could you
please post the complete code that you used for project to export the
gifs? Thanks.

 

[Jon Peltier]

John Walkenbach has a utility on his web site that will export charts
for you (http://j-walk.com). If you are hungry for the code:

Sub SizeAndExport()
Dim High As Single
Dim Wide As Single
Dim Name As String
If ActiveChart Is Nothing Then
MsgBox "Select a chart and try again", vbExclamation, _
"No Chart Selected"
Else
High = Application.InputBox(prompt:= _
"How high will your exported chart be (pixels)?", _
Title:="Chart Height", Type:=1)
Wide = Application.InputBox(prompt:= _
"How wide will your exported chart be (pixels)?", _
Title:="Chart Width", Type:=1)
Name = Application.GetSaveAsFilename("Chart.GIF", _
"GIF Files (*.GIF),*.GIF", , _
"Enter Chart Name")
With ActiveChart.Parent
.Height = High * 3 / 4
.Width = Wide * 3 / 4
End With
ActiveChart.Export Name, "GIF"
End If
End Sub

You will probably want the chart close to the intended size, to be sure
the fonts and scales all look okay.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/Excel/Charts/
_______

 

[Thomas Wieser]

Hi Eric,

It is not possible to set any other attributes for the Export method, so
I use this workaround. Important: The width and height values are in
"Points", not in pixels - 1 point = 4/3 pixels! That's why I had a width
of 4800 pixels instead of 3600.


So: I did it in the same way as Jon posted below. I resize the chart in
that way that the relation between width and height is the same as
before. Important: The AutoScaleFont attribute of the chart must be set
to True.

Dim akt_x As Integer
Dim akt_y As Integer
Dim factor As Integer

akt_x = Worksheets(1).ChartObjects(1).Width
akt_y = Worksheets(1).ChartObjects(1).Height

factor = 2700 / akt_x
Worksheets(1).ChartObjects(1).Width = 2700
Worksheets(1).ChartObjects(1).Height = akt_y * factor



Then, I set the Window Zoom to 25% to enable the user to see everything.
Therefore, I have to unfocus the chart and focus a cell element, don't
know why. For me, it is important that the user can see what Excel is
doing, and if there is any display error, the user can change the chart
manually.

Worksheets(1).Range("A1").Select
ActiveWindow.Zoom = 25


Finally, I do the export to a PNG file, should also work with GIF files:

Worksheets(1).ChartObjects(1).Chart.Export _
FileName:="C:\myfile.png", _
FilterName:="PNG", _
Interactive:=True




Regards, Thomas