Issue Working w/ Relative Path in Excel 2007 VBA

Question

I am working with Excel 2007 and the following suggestion from this site has not worked:

Workbooks.Open Filename:=ThisWorkbook.Path & "\Chapter 7 - 10 MECHANICAL.xls"

I have also tried the following code w/ no luck as well:

Workbooks.Open Filename:=app.Path & "Chapter 7 - 90 ECS 1 LLC.xls"

The files are in the same path as the workbook with the macro, so I am at a loss for what I am doing wrong.

I am running Microsoft Vista.

Thanks in advance.

Answer 1

For ThisWorkbook.Path to work, an existing excel file should be open.
I mean - for a new workbook (which is not saved), the Path will be blank.

EDIT: Is the macro enabled? Does the code you provided run?
I tried with 2 files in same dir & the code with ThisWorkBook.Path & "\myOtherFile.xls" works.

Answer 2

Try this:

Workbooks.Open Filename:=ThisWorkbook.Path & application.pathseparator & "Chapter 7 - 10 MECHANICAL.xls"

This will make sure the proper slash is used.

Bill

Answer 3

app.Path will return the path to Excel itself (in Program Files), which is not what you want.

You probably want to call the CurDir function, which will return the current directory.

---

EDIT: On second thought, you might not want the CurDir function, becausethe current directory doesn't change when you open a file. If you're trying to open a file in the same folder as the current workbook, your code should work.

Make sure that ThisWorkbook is the workbook that you think it is. Also, what error are you getting?

Answer 4

Your first line of code is correct.

Workbooks.Open Filename:=ThisWorkbook.Path & "\Chapter 7 - 10 MECHANICAL.xls"

ThisWorkbook.path will return the path to the workbook where the code is being run from.

If the workbook running the code has not yet been saved, it will return an empty string.

Try adding this code to see what is happening:

Debug.Print ThisWorkbook.Path & "\Chapter 7 - 10 MECHANICAL.xls"
Debug.Print ThisWorkbook.Saved

The output can be viewed via 'View - Immediate Window'