Run VBA on any file open

[Rob Oldfield]

Hi,

I have a situation where my company is shortly going to be moving our main
data file share from one server to another. One of the main problems with
this is going to be in coping where users have included included links into
their spreadsheets. It will be OK if they have done that in such a way that
the link ends up as s:\.... \whatever.xls as when we change the drive
mappings that will be catered for. But they will go clunk if the links are
in the form \\currentserver\share\....\whatever.xls

I've looked into ways of scanning the share for any files that contain links
in that form, but there are just too many problems with that (largely down
to the amount of files that we have).

My current thought is to tweak everyone's copy of Excel so that it runs a
macro whenever _any_ file is opened, do a find and replace to search for
\\currentserver\share and replace with the drive letter, but I can't figure
out how to do it. The VBA to do the find/replace is no problem... the bit
that I'm struggling with is what to do with that code. Drop it into an xlt
file that lives in each users alternate startup file location? Or an xla
that is automatically assigned? However I've tried, I just can't get the
idea to work.

Any ideas? (This is Excel 2000)

Thanks in advance.

 

[Guest]

Look at this webpage on personal.xls

http://office.microsoft.com/en-us/excel/HA010872961033.aspx

 

[Rob Oldfield]

Thanks for that Joel. That lets me set things up so that my macros will be
available for anyone to run, but ideally I'd like a specific chunk of code
to run whenever a user opens up any file at all. I've tried adding code to
the Workbook_Open event in the personal.xls, but that doesn't seem to
automatically run. Any ideas how I persuade that to happen?