Replace file path in VBA with user name

Question

I have an Excel file with VBA written on it that draws information from a file on my computer. The Excel file is on a network folder and I would like for other users on the network to use it as well. However, I have hardcoded the file path on the VBA and, as such, whenever another user opens it, it looks for a file that is not available.

This is the path I would like to change:

C:\Users\User1\Documents\The Market in\DATA FOR REPORTS.xlsx

The only difference on the paths would be the user's name: User1, user2, etc.

How can I write the VBA code in order for it to replace the username in the file path with the Windows user name opening it?

I have tried to use wild card and also tried to use ENVIRON("username") but have not been successful.

The code I want to replace is what's below:

Private Sub Workbook_Open()

Application.Visible = False
WelcomeForm.Show
Workbooks.Open ("C:\Users\User1\Documents\The Market in\DATA FOR REPORTS.xlsx")

End Sub

This is what I did using ENVIRON:

Private Sub Workbook_Open()

Dim username As String

username = Environ("username")

Application.Visible = False
WelcomeForm.Show
Workbooks.Open ("C:\Users\&username&\Documents\The Market in\DATA FOR REPORTS.xlsx")

End Sub

Thank you very much

Environ("Username") should work. Can you show how did you use it and what was the problem/error? — Egan Wolf
– Egan Wolf, Commented Aug 25, 2017 at 13:11
This is not correct concatenation syntax - "C:\Users\&username&\Documents - look carefully at the answer below — braX
– braX, Commented Aug 25, 2017 at 13:43

Brian M Stafford · Accepted Answer · 2017-08-25 13:20:03Z

8

Try something like this:

Private Sub Workbook_Open()
   Application.Visible = False
   WelcomeForm.Show
   Workbooks.Open ("C:\Users\" & Environ("UserName") & "\Documents\The Market in\DATA FOR REPORTS.xlsx")
End Sub

answered Aug 25, 2017 at 13:20

Brian M Stafford

8,9432 gold badges19 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Darren Bartrup-Cook · Accepted Answer · 2017-08-25 13:45:48Z

2

Environ("userprofile") will return the path & username.
On my PC it returns C:\Users\darren.bartrup-cook

Another way is:
CreateObject("WScript.Shell").SpecialFolders("MyDocuments")
On my PC this returns C:\Users\darren.bartrup-cook\Documents

You could use it like this:

Private Sub Workbook_Open()

    Dim wrkBK As Workbook
    Dim DocFldr As String

    DocFldr = CreateObject("WScript.Shell").SpecialFolders("MyDocuments")

    Set wrkBK = Workbooks.Open(DocFldr & "\The Market in\DATA FOR REPORTS.xlsx")

    MsgBox wrkBK.Name & " is open", vbOKOnly + vbInformation

End Sub

answered Aug 25, 2017 at 13:45

Darren Bartrup-Cook

20.2k2 gold badges27 silver badges50 bronze badges

Collectives™ on Stack Overflow

Replace file path in VBA with user name

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related