Error 1004 when I open a file

[Deer Hunter]

Hi!

When I want to open a .xls file with VBA command:
Set bk2 = Workbooks.Open(name)

where bk2 is defined as: Dim bk2 As Workbook

I get:
'run time error '1004':
Method 'Range' of object '_Worksheet' failed

and when I look in Excel, the file is opened!

Please anybody who can help me on the right track? What is wrong?

Regards,
Mike

 

[Tom Ogilvy]

Sub Tester2()
Dim wkbk As Workbook
Dim name as String
name = "C:\data\aa_userform.xls"
Set wkbk = Workbooks.Open(name)
Debug.Print wkbk.Name
End Sub

worked fine for me.

Make sure the workbook is not already open when you run the code. Probably
better not to use "name" as a variable name.

 

[Deer Hunter]

Hi Tom,

If I replace my code with your code, still getting the same errormessage..
Could it be something with the sheet I want to open? Protection, marco's or
something else?

Mike

 

[Tom Ogilvy]

Well, nothing in the code you show has anything to do with a range. If that
is all the code you have, then perhaps the workbook has code it is running
which fails. If that appears to be the case, try this:

Sub Tester2()
Dim wkbk As Workbook
Dim name as String
name = "C:\data\aa_userform.xls"
Application.EnableEvents = False
Set wkbk = Workbooks.Open(name)
Application.EnableEvents = True
Debug.Print wkbk.Name
End Sub