How to deal with Excel VBA scripts that contain staff file ID’s in the file-path

Learn how to implement a single VBA function to resolve that issue of changing all your VBA scripts when handing over work to a colleague.

 

 

The chances are that if you are working for an organisation with a vast IT Network you may experience times where VBA scripts or Excel macros run slowly across the network and perform much better on your hard-drive.

In these cases you are probably saving your files and reports into an area on your hard-drive that is linked to your login staff ID, so a common file path for a staff member with the file ID 123456 may look like:

C:\Documents and Settings\123456\My Documents\Monthly Reports

And your Excel VBA script for saving the file would look something like:

Excel VBA Runtime Error FilePath _1

 

But what happens when you get that promotion and pass on your old reports to a new colleague and their file ID is 987654? Yes that’s right they’ll run your fancy VBA scripted reports and find they see a dreaded error message:

Excel VBA Runtime Error FilePath

One way around this is to go through all your VBA script and change to the new staff members file ID, this is very time-consuming and depending on how much script you have this could easily produce more errors. Then what happens when the new staff member moves on?  Yes it all has to be done again…..but not if you use the Environ function in VBA.

Simply change the part of the script where you usually have a file ID to Environ(“Username”) and you will no longer have to worry about changing VBA script when handing over, your new VBA script should look something like:

Excel VBA Runtime Error FilePath _2

In case the image isn’t that clear the VBA script is:

ActiveWorkbook.SaveAs _
(“C:\Documents and Settings\” & Environ(“Username”) & “\My Documents\Monthly Reports\Sales.xlsm”)

No longer do you have to worry about spending hours or days changing all those Excel VBA scripts when it comes to hand-over time!  I’ll be sharing more tips like this going forward as there are plenty of other ways you can make hand-overs of Excel files with VBA script nice and easy on yourself.

Keep Excelling,

Leave a Comment