largeselection
Active Member
- Joined
- Aug 4, 2008
- Messages
- 358
Hi,
I have a macro that does a lookup from a file that is in a central network drive. There are multiple users who use the macro. I have shared the file so that everyone can be in it at once so the macro can do the lookups. The issue is that the users have the drive where the lookup file is as different drive letters.
So for example, I have the drive as O:/, but someone else might have it as T:/. So in the code, it opens up the file at the location O:/, but that drive is the wrong one for a different user.
Is there a way that I can reference the path based on the drive name (it is called CENTRAL DRIVE) so rather than O:/FOLDER, it could be CENTRAL DRIVE/FOLDER so that everyone could access the file regardless of his/her drive letter and wouldn't need to have the code modified for each user.
The only thoughts I had that I know how to put in would be to have a prompt pop up and ask for the drive letter and then have the user type in the drive letter and have the code pop the user response into the drive letter portion of the path.
Or similarly, have the drive letter reference a specific cell on a sheet and have the user fill out that cell with their drive letter.
I have a macro that does a lookup from a file that is in a central network drive. There are multiple users who use the macro. I have shared the file so that everyone can be in it at once so the macro can do the lookups. The issue is that the users have the drive where the lookup file is as different drive letters.
So for example, I have the drive as O:/, but someone else might have it as T:/. So in the code, it opens up the file at the location O:/, but that drive is the wrong one for a different user.
Is there a way that I can reference the path based on the drive name (it is called CENTRAL DRIVE) so rather than O:/FOLDER, it could be CENTRAL DRIVE/FOLDER so that everyone could access the file regardless of his/her drive letter and wouldn't need to have the code modified for each user.
The only thoughts I had that I know how to put in would be to have a prompt pop up and ask for the drive letter and then have the user type in the drive letter and have the code pop the user response into the drive letter portion of the path.
Or similarly, have the drive letter reference a specific cell on a sheet and have the user fill out that cell with their drive letter.