Auto File Save

[data808]

So I have some VBA to save a file automatically when I click the button and here is the file path:

fname = "C:\Users\john.smith\Desktop\" & Range("G2") & "\ECIT_" _
& Format(DateValue(mnth & "-01"), "mm") & mnth & "_" & Range("G2") & ".xlsm"

The problem is that this one is specific to that one computer's desktop. How do I make it so that it doesn't matter who's desktop it is? I'm pretty sure there is a way but I can't remember. Is it something like %userprofile%?

Thanks.

 

[Micron]

Consider using Environ("UserName") instead of john.smith.
It may or may not work because the value it returns is based on the account of the logged on user.
Then there is Application.UserName, or research, such as

Excel VBA Get Username - in 6 Easy ways - using Macro

Excel VBA Get Username code for Windows current user. File author, MS Office UserId, Network User, Computer Name using Excel Macro.

officetricks.com

 

[data808]

Consider using Environ("UserName") instead of john.smith.
It may or may not work because the value it returns is based on the account of the logged on user.
Then there is Application.UserName, or research, such as

Excel VBA Get Username - in 6 Easy ways - using Macro

Excel VBA Get Username code for Windows current user. File author, MS Office UserId, Network User, Computer Name using Excel Macro.

officetricks.com

The Application.Username throws the profile name after the \Desktop\ which makes the path not exist. I think the Environ did something similar. Still messing around with it but no luck so far.

 

[data808]

Consider using Environ("UserName") instead of john.smith.
It may or may not work because the value it returns is based on the account of the logged on user.
Then there is Application.UserName, or research, such as

Excel VBA Get Username - in 6 Easy ways - using Macro

Excel VBA Get Username code for Windows current user. File author, MS Office UserId, Network User, Computer Name using Excel Macro.

officetricks.com

You have to write it like this:

fname = Environ("USERPROFILE") & Chr(92) & "Desktop\"

Then it works.

 

[Micron]

Or
fname = Environ("USERPROFILE") & "\Desktop\"