Automatically update user ID path in a macro?

[Chelsea7]

I have a quick question. I'm using a macro to import an excel file into Access. Everything works fine. However, I plan on placing this database on a shared drive so that other users will have access to it. My current path for the file name is;

\\CORPORATE\REDIR\C422\My Documents\lfa220p2.xls

Where C422 is my user id. If I put this on the shared drive and another user uses it, they will get an error in the path because they have a different user ID. The user ID is the same s the Windows log on ID.I remember some like 'USER' in access. How can I set it up where the path will recongize the user and direct the file to their 'My Documents' folder. I was trying to do this

\\CORPORATE\REDIR\& USER &\My Documents\lfa220p2.xls

Can it be done using this method or do I have to user VBA?

I rather not install it on every local machine because the DB will have updates and I don't have admin rights to run a script to download to each workstation. I would have only one user at one time access the DB and others will be locked out.

Thanks
Chelsea

 

[PHV]

In VBA:
strPath = "\\CORPORATE\REDIR\" & Environ("USER") & "\My Documents\lfa220p2.xls"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886