Macro drive problem

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

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:

> 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

You can use the UNC Path in your open statement:

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

....



Bobak wrote:
>
> 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

--

Dave Peterson