macro for different users... kind of

[jatman]

i am using the line below along with variations throughout in a macro:

ActiveWorkbook.SaveAs "C:\Users\jat\Desktop\Orders\" & fname & ".xlsx", _

the problem that i have is the macro will save on my desktop because it is
my profile's desktop. if i wanted to put the macro on another user's
profile, i do not want to go changing the macro everytime. can the line be
changed so that it goes to the current user's desktop?

i was thinking something like this:

Function ThisUser()
ThisUser = Environ("UserName")
End Function

and the line would be:
ActiveWorkbook.SaveAs "C:\Users\" & %thisuser% & "\Desktop\Orders\" & fname
& ".xlsx", _

this seems a bit too easy, so i think it's wrong... would it work?

jat

 

[Hong Quach]

Hi Jatman,

You can use Environ("USERPROFILE") to get the user home directory. So far I
only run this on WinXP, and WinXP return something like "C:\Documents and
Settings\jat" where "jat" is the current user login name.

You can use the AllEnviron() below to print out all the interesting values
that you might not know it was there before.

Public Sub AllEnviron()
Dim c As Integer
c = 1
While Environ(c) <> ""
Debug.Print Environ(c)
c = c + 1
Wend
End Sub


Hong Quach

 

[mudraker]

jatman

What you posted will work

I have Environ("UserName") in XP, 2003, NT


--
mudraker

If my reply has assisted or failed to assist you I welcome your
Feedback.

www.thecodecage.com