Open same file from different computers

[Amots]

Dear experts

I am new to VBA so please bear with me.
I have an excel workbook with buttons to open other workbooks. Right now this is hard coded so every time I put it on a new computer I need to reconfigure the path. Also, if a user moves the other workbooks to another directory I will have to reconfigure the path agin.
Is there a code in VBA which can search for a certain file name and open it?
I have tried to look this up in different places but could not find anything which can help me. If this issue has been covered in the past please excuse me.
Any help will be appreciated...

 

[Eric W]

Welcome to the forum.

When you put the workbook on a new computer, do you put the other workbooks on that computer at the same time, or are they on a network drive? If they are on a network drive, that can get tricky really quickly, since that might involve mapping drive letters, which could be different for different users.

If all the workbooks are on the same computer, I'd suggest putting them all in the same folder. Then when you try to open the other workbooks, you can get the current path by using ActiveWorkbook.Path, and use that to open the other workbooks. So you don't need to hard code the path.

If the user moves the workbooks, so that you can't find them with ActiveWorkbook.Path, I'd suggest using a FileOpen Dialog Box, and making the user navigate to where the workbook is. If you wanted, you could then save that path somewhere in the workbook so that the user doesn't need to navigate every time.

 

[Yongle]

You could look at this from the other direction...
1. Use a master workbook to maintain a register of other key workbook names and locations
2. Get the subsisiary files to write location details to that file whenever they are opened

Corporate Discipline
1. Is it appropriate to prevent users moving these specific files around willy nilly?
2. Use VBA to prevent a workbook being usable if it moves