vba to save a file to a location without knowing what the username is

[shahdelsol]

I am trying to save a file to a folder on a desktop without knowing what username is and I came up with this code but it doesn't work. Can anyone correct me? Thanks

Sub SaveAsPDF()
    
    
    Dim myloc As String
    
    myloc = "C:\Users\" & Environ("username") & "\desktop\" & " \ books \ """
    
 
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "myloc" & ActiveSheet.Range("J1").Value & " " & ActiveSheet.Range("C10").Value & ".pdf" _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=False
    
End Sub

 

[igold]

Just at a quick glance- if you have your path right- you are generating a double backslash in this line between desktop and books... You have backslash at the end of desktop and also one at the start of books... It also look like you have some added spaces in books, but that could just be the way the code posted.

myloc = "C:\Users\" & Environ("username") & "\desktop\" & " \ books \ """

 

[shahdelsol]

Just at a quick glance- if you have your path right- you are generating a double backslash in this line between desktop and books... You have backslash at the end of desktop and also one at the start of books... It also look like you have some added spaces in books, but that could just be the way the code posted.

myloc = "C:\Users\" & Environ("username") & "\desktop\" & " \ books \ """

You are correct. I noticed those after I posted but even after I corrected it, it didn't work however I am not getting any error but I don't see any file being saved either. Any suggestions?

Here is the correction:

myloc = "C:\Users\" & Environ("username") & "\desktop" & "\books\"

 

[shahdelsol]

Never mind it is working now. I had to remove " " from myloc. Thanks for help

 

[igold]

Great, I am glad you got it working. I was happy to help. Thanks for the feedback!

 

[jolivanes]

No need to know Username.
Change references as required.

Sub Or_So()
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        CreateObject("WScript.Shell").specialfolders("Desktop") & "\Test Report.pdf" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
End Sub