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

microsoft excelmicrosoft-excel-2010vba

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?

Best Answer

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
Related Question