Workbooks.Open Method in VBA

Question

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

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

But when I try to change the Filename value to a new path, as shown below, but all my actions did not work. Show Runtime 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 

In fact, myMacro.xls and myTest.xls were placed in the same folder. Therefore, I want to go to the directory with flexible folders.

How can I fix this problem? Thank you for your reading and reply.

Answer 1

You can try using ThisWorkbook.Path to create an absolute path. It returns the path to the macro workbook folder (excluding the file name). Something like this should work:

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

Be sure to include the backslash, since the workbook path does not end with one.

Answer 2

Filename refers to the current Excel directory (which is different from the directory in which the open document is open).

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

But you really want to do this:

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

where EnsureSlash is your custom function that adds a backslash ( \ ) to the end of the line if it does not already exist (since ThisWorkbook.Path ends with a slash when the path is the root directory, and otherwise).