VBA - FileName

[dipsy]

I want to put the file name and the file path in a
variable that I can use throughout the code by just using
the variable.

This is the code I wrote:

Dim fileName As String
fileName = "C:\Documents and Settings
\My Documents\2004\Data\HistoricalData.xls"

ActiveChart.SetSourceData Source:=Workbooks
(fileName).Sheets("By Year").Range("A1:E13"),
PlotBy:=xlColumns

Gives me - Run Time Error 9 Subscript out of range.

TIA

 

[Bob Phillips]

Have you opened the file?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[dipsy]

Yes the file was open - I tried the code after closing the
file - the code still gives the same error.

Thanks.

 

[Dave Peterson]

Don't use the whole path in the workbooks() portion.

dim fileName as string
dim fullFileName as string

filename = "HistoricalData.xls"
fullFileName = "C:\Documents and Settings\My Documents\2004\Data\"
& filename

Then workbooks(filename).... should work.