Macro drive problem

[Guest]

I have written a macro that opens a number of workbooks from a network drive
using Excel. I would like this macro to be used by a number of users. However
users have the drive name on different letters eg on my computer A:\network
drive\file.xls but on colleagues it is B:\network drive\file.xls Can I get
the macro to be dynamic and point to the drive name rather than use the
letter as it returns an error unless the drive is on the same letter name as
I have written in the macro

Regards

 

[Guest]

If I were you I should st up a parameter sheet containing user names and
drive letters:

A B
Name Letter
user_1 X
user_2 Y
....
user_n Z

The macro should be informed of the actual user name (e.g. via an input box
in Workbook_open event procedure), then the filename to be opened can be
composed

filetoopen=VLOOKUP("user_n",A1:B5,2,FALSE)&":\"&network_map_name&"\"&"file.xls"

Column B could contain Letter+network_map together if it is more suitable
for the case:

user_1 X:\network_map\


In this case composing the filename is simpler:

filetoopen=VLOOKUP("user_n",A1:B5,2,FALSE)&"file.xls"

Regards,
Stefi

„Bobak†ezt írta:

 

[Dave Peterson]

You can use the UNC Path in your open statement:

dim wkbk as workbook
set wkbk = workbooks.open(filename:="\\server\folder\folder\filename.xls")

....