Excel VBA: How to use a unique username or save location for an export pdf macro

Question

I have a macro that prints a uses the internal Excel method ExportAsFixedFormat to print a worksheet as a pdf and save the document to the desktop using a unique file name structure.

ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:="C:\Users\UNIQUE_USER\Desktop\" & Right(Range("D11"), 8) & "_ALL_SALES.pdf", _
     Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=True

This works for me no issue. However if I wanted to share this worksheet to another user at my work (and not require them to rewrite the macro), is there a way to save the pdf document using their unique username or directory. Is there a default directory/path that will work for all users?

Solution

The full path to the root User folder of the currently logged-in user can be retrieved with:

Environ("USERPROFILE")

To save the file to the user's desktop, like you've done, you would use:

Environ("USERPROFILE") & "\Desktop\" & ...

For your specific code as provided

ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=Environ("USERPROFILE") & "\Desktop\" & Right(Range("D11"), 8) & "_ALL_SALES.pdf", _
     Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=True

Comments

[TheUser1024]

You can get your hands on the username in VBA: stackoverflow.com/questions/935160/…

[dasMetzger]

We just switched up IT management who assigned all of us new usernames for web based and desktop applications, but it looks like they did not do that for windows user logons yet. So when I run Environ("USERNAME") I wasn't getting the correct path, but "USERPROFILE" works as it looks at the actual directory structure rather than the username tied to excel. I followed both the commented link and the following link for more information: stackoverflow.com/questions/17551238/…