Workbooks.Open Method in VBA

Question

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.

Answer 1

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).