File Open problem in Excel 2000

  • Thread starter Thread starter Gef
  • Start date Start date
G

Gef

I want a macro to open a spreadsheet. I have seen Tom
Ogilvys postng on this and created a macro as follows;

Sub GefTest()

sname = Dir("D:\Documents and Settings\Me\My
Documents\Spreadsheet Examples\Action Buttons.xls")
If sname <> "" Then
Workbooks.Open "D:\Documents and Settings\Me\My
Documents\Spreadsheet Examples\" & sname

End Sub

But.. this gives me an error that says:

Compile Error: Method or Data member not found

It does not seem to like the .Open bit.

Please could someone explain where I have gone wrong.
 
Hi
the following should do
Sub GefTest()
Dim sname
sname = Dir("D:\Documents and Settings\Me\MyDocuments\Spreadsheet
Examples\Action Buttons.xls")
If sname <> "" Then
Workbooks.Open "D:\Documents and Settings\Me\MyDocuments\Spreadsheet
Examples\" & sname
End If
End Sub

Check for linebreaks:
- sname = DIR(...) is ONE line
- also there is only one line betwenn If and End If (the latter one was
missing in your code)
 
Hi Frank,

Thanks for the reply,

I copied your code (copy and paste) but still get the same
message - it still does not seem to like Workbooks.Open

I have made sure the code was on single rows, and that the
path is correct and no blank rows.

Soory to be a pain...any other ideas?
 
Hi
try
Sub GefTest()
Dim sname
sname = Dir("D:\Documents and Settings\Me\MyDocuments\Spreadsheet
Examples\Action Buttons.xls")
If sname <> "" Then
Workbooks.Open "D:\Documents and Settings\Me\MyDocuments\Spreadsheet
Examples\Action Buttons.xls"
End If
End Sub
 
Sub GefTest()
Dim sname as String
Dim sPath as String
Dim sFname as String
sPath = "("D:\Documents and Settings\" & _
"Me\MyDocuments" & _
"\SpreadSheet Examples\"
sFname = "Action Button.xls"
sname = Dir(sPath & sFname)
If sname <> "" Then
Workbooks.Open sPath & sFname
else
msgbox sPath & sFname & " not opened"
End If
End Sub
 
Thanks Tom and Frank

I have tested both samples and they both work when I put
them in modules in new spreadsheets.

They don't work from my Personal.xls - which suggests
there is something wrong with it (there area lot of
modules in there).

Gef
 
I have worked out why this does not work in my
Personal.xls spreadsheet.

I named a module Workbooks - which "tatered" the code-
Sorry to have meesed you about.

Gef
 
Back
Top