How to use VBA environ username

[LadyT]

Hi,
I have created a macro in excel to save an excel file into PDF format and then save - which is then saved on the current user c:\drive
The problem is that need the path to default to the current username so that the macro can run on different machines.

The macro is:

Sub Macro4()
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\smith\Dropbox (Company)\Test with MG\DFA (August 26, 2015) - Copy.pdf" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True
End Sub

I am not sure where & how to use the Environ VBA code to make this work.
Any help would be greatly appreciated.
Thanks.
L.

 

[NoS]

maybe like this ?

Sub Macro4()
'
' Macro4 Macro
'
Dim TheUser As String
    
TheUser = Environ("UserName")

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\" & TheUser & "\Dropbox (Company)\Test with MG\DFA (August 26, 2015) - Copy.pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True

End Sub

 

[p45cal]

You might not have to use environ at all and you might get away with 2 lines:

p = CreateObject("Scripting.FileSystemObject").GetParentFolderName(CreateObject("WScript.Shell").SpecialFolders("Desktop"))
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        p & "\Dropbox (Company)\Test with MG\DFA (August 26, 2015) - Copy.pdf" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True

You might want to use a different thing from Desktop, so run through the options; each msgbox shows the name, the directory then the parent directory - take your choice:

Sub blah()
Set yy = CreateObject("WScript.Shell")
Set fs = CreateObject("Scripting.FileSystemObject")
xx = Array("AllUsersDesktop", "AllUsersStartMenu", "AllUsersPrograms", "AllUsersStartup", "Desktop", "Favorites", "Fonts", "MyDocuments", "NetHood", "PrintHood", "Recent", "SendTo", "StartMenu", "Startup", "Templates")
For Each thing In xx
   MsgBox thing & vbLf & yy.SpecialFolders(thing) & vbLf & fs.GetParentFolderName(yy.SpecialFolders(thing))
Next thing
End Sub