VBA can't find file as it moves from folder to folder

G

Guest

OK -- I need professional help.

I have two files used by several people in the company. One file ('user')
captures information for each person's business unit, and compares it to
industry standards for that type of service. It pulls the 'comparative'
information from the second file. Since the formulas are complex, I've
protected everything so that the users can't break it. I want the
comparative info in a second file so that, as it gets updated from year to
year, the users can just swap the new file in for the old one.

I've written the VBA language below with the idea that, wherever the 'user'
file is, it would look in the same folder for the 'comparative' file.
However, that's not happening. What happens instead is that, regardless
where the user puts the two files, the 'user' file looks back to the original
folder on my computer. Needless to say, it ain't found.

Here's what I wrote, and I'm hoping this is where the problem lies.


Private Sub Workbook_Open()
Dim File_Path As String
Dim File_Name As String

File_Path = ThisWorkbook.Path
File_Name = File_Path & "\" & "Instrument Panel Reference Data.xls"
Workbooks.Open Filename:=File_Name, UpdateLinks:=3

Thanks in advance for any help you can offer.

Windows(ThisWorkbook.Name).Activate
 
A

Alan

Since this is in the ThisWorkbook module you can simple do it all with:

Workbooks.Open (ThisWorkbook.Path & "\Instrument Panel Reference Data.xls"),
UpdateLinks:=3


Regards,

Alan
 

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