Exporting Chart to GIF File

[julia.stone]

I am new to developing in VBA for Excel. I'd appreciate any tips on
how to fix this bug, as I believe I am on the right track but something
I can't quite see is incorrect. Thanks in advance.

I have a workbook with 7 sheets. Let's say on worksheet 5 there are 5
charts. On Worksheet 6 I have 5 option buttons, and for each button
that I select I want to display a particular chart. I have been able
to successfully loop through my chart objects and verify that I have 5,
yet when I try to export each of them I get the following error:

Run-time error '1004'
Application-defined or object-defined error

Below is my code sample of just trying to export one chart. The last
line of the code is where I get an error. Any help will be greatly
appreciated.

Private Sub OptionButton1_Click()
Dim strGifFileName As String
Dim CurrentChart As Chart
Dim index As Integer
index = 1
Set CurrentChart = Worksheets(6).ChartObjects(index).Chart
strGifFileName = "C:\My Documents\" & "MyChart" & index & ".gif"
CurrentChart.Export fileName:=strGifFileName, FilterName:="GIF"
End Sub

 

[NickHK]

Julia,
What if you do not use "index" as the name of your variable ?

NickHK

 

[julia.stone]

The index was just for testing purposes - I want to just save Chart 1
to a gif file for now. I plan to loop through my chart objects and
according to index save them to the appropriate file names. Can you
clarify why you mention not using the index variable?

My error is on the line:

CurrentChart.Export fileName:=strGifFileName, FilterName:="GIF"

 

[OJ]

HI,
The reason Nick says this is because he thinks Index is a reserved word
in VB....I dunno whether it is or not but If I change the name of the
variable it works for me....having said that it works with index
too...iwould steer clear of words like that for variables tho'.....the
only other thing I can think of is the directory doesn't exist..

OJ

 

[julia.stone]

Thanks to you both. I changed the variable name to chart_index. And
that did not help, but apparently there is an issue with the way I have
defined the location to save the file:

strGifFileName = "C:\My Documents\" & "MyChart" & chart_index & ".gif"

When I changed it to just C:\ it worked. I would prefer My Documents.

 

[NickHK]

Julia,
Depending on your set up, I bet the path you mean is more like:

C:\Documents and Settings\<User Name>\My Documents\MyChart" & index & ".gif"

Yes, as OJ says, IMHO it is better to avoid such variable names that are
used by VB, even if it is not reserved.

NickHK

 

[Tushar Mehta]

If you leave out the reverse slash between the drive and the 'my
documents' XL will correctly expand the name. So, use:
strGifFileName = "c:My Documents" _
& Application.PathSeparator & "MyChart" & Idx & ".gif"

[Note, I changed Index to Idx; like the others I am averse to a
variable name that might be some kind of keyword.]

Alternatively, use the Environ function to construct the path to My
Documents. Something like:
strGifFileName = Environ("USERPROFILE") _
& Application.PathSeparator & "My Documents" _
& Application.PathSeparator & "MyChart" & Idx & ".gif"

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Harald Staff]

There's usually no
C:\My Documents
folder, unless you spesifically created one yourself. My Documents is a
friendly name for somewhere else, real path depending on who's using the
computer at the moment.

HTH. Best wishes Harald

 

[Tushar Mehta]

Hi Harald,

XL (or is it Win XP?) correctly expands c:My Documents (when used with
no intervening reverse-slash).

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Harald Staff]

Hi Tushar

Not here they do, norwegian WinXP, various Excel versions. So at best it's
an unstable solution for use in controlled environments. Environ works well
(but not in the 9x windows family).

Best wishes Harald

 

[Peter T]

Another thought - if My Documents is your Default File Location (Tools >
Options > General)

Sub test()
Dim strGifFileName As String
Dim sDefPath As String
Dim sPathSep As String
Dim chart_index As Long
sPathSep = Application.PathSeparator
sDefPath = Application.DefaultFilePath
If Right$(sDefPath, 1) <> sPathSep Then
sDefPath = sDefPath & sPathSep
End If
chart_index = 3
strGifFileName = sDefPath & "MyChart" & chart_index & ".gif"
Debug.Print strGifFileName
End Sub

Regards,
Peter T

 

[Tushar Mehta]

Hi Harald,

Yeah, I too was surprised when I read that code somewhere. Was about to
post a "That's not going to work when I tested it. To my suprise, it
worked just fine."

I guess outside of the two suggestions I provided, the only other choice
for the OP would be to use the appropriate Windows API to get the user
name and go from there.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005