CODEWITHSUNDEEP
vbaexcelexcel-2003
Nano HE
Nano HE

Reputation: 1971

Workbooks.Open Method in VBA

My vba script in myMacro.xls Workbooks.Open Method work well as below,

Workbooks.Open Filename:="D:\ExcelMacroProj\myTest.xls", ReadOnly:=True

But when I try to change the Filename value to a new path as below, but all my practices didn't work. Show Run time error 1004.

Workbooks.Open Filename:="myTest.xls", ReadOnly:=True
or
Workbooks.Open Filename:="./myTest.xls", ReadOnly:=True
or
Workbooks.Open Filename:=".\myTest.xls", ReadOnly:=True

Actually myMacro.xls and myTest.xls was placed in the same folder. That's why I want to change to a flexible folder directory.

how could I fix this issue? Appreciated for your read and reply.

Upvotes: 4

Views: 43618

Answers (2)

GSerg
GSerg

Reputation: 78175

Filename is relative to the current Excel directory (which is different from the directory in which an opened document is).

You change the current directory by using ChDir "x:\new\path".

But what you actually want to do is:

Workbooks.Open Filename:=EnsureSlash(ThisWorkbook.Path) & "myTest.xls", ReadOnly:=True

, where EnsureSlash is your custom function that appends a backslash (\) to the end of the string, if it's not already there (because ThisWorkbook.Path ends with a slash when the path is the root directory, and doesn't otherwise).

Upvotes: 8

Greg Haskins
Greg Haskins

Reputation: 6794

You might try using ThisWorkbook.Path to make an absolute path. It returns the folder path of the workbook running the macro (excluding the filename). Something like this should work:

Workbooks.Open Filename:=ThisWorkbook.Path & "\myTest.xls", ReadOnly:=True

Make sure to include a backslash, since the workbook path doesn't end with one.

Upvotes: 8

Related Questions