Default File Path in Workbooks.Open(FileName:="Filename")

D

Dennis

Using XL 2003 & 97

If I use:

Workbooks.Open(FileName:="myFile.xls")

The file is not found

But if I use:

Workbooks.Open(FileName:="C:\Excel Files\myFile.xls")
The world is beautiful.

Even though this makes sense, I would like to have VBA code that assumes the
path to the file:
1) that I wish to open next is,
2) in the same path as my active file.

(I know that I could hard code it but I would like smart VBA code which
looks for the second file in the same directory as the file that I have
active - no matter what the directory path)

What is the best way to write that VBA code?

TIA Dennis
 
J

Jim Rech

I don't know how you determine the "second file in the same directory" but
once you do you can get its path with:

WBPath = Workbooks("SecondWB.xls").Path

You could use that and the ChDir and ChDrive statements to made that folder
active. Or you could append the result to your open string:

Workbooks.Open(WBPath & "\myFile.xls")

--
Jim Rech
Excel MVP
| Using XL 2003 & 97
|
| If I use:
|
| Workbooks.Open(FileName:="myFile.xls")
|
| The file is not found
|
| But if I use:
|
| Workbooks.Open(FileName:="C:\Excel Files\myFile.xls")
| The world is beautiful.
|
| Even though this makes sense, I would like to have VBA code that assumes
the
| path to the file:
| 1) that I wish to open next is,
| 2) in the same path as my active file.
|
| (I know that I could hard code it but I would like smart VBA code which
| looks for the second file in the same directory as the file that I have
| active - no matter what the directory path)
|
| What is the best way to write that VBA code?
|
| TIA Dennis
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top