VBA command button - save as pdf and write to specific folder

[Robert_G]

Below is my code and works perfectly. Only problem I have is if I email this "Temp folder" where this spreadsheet is located, you can see it will be looking for my desktop specific location on my computer.

What I would like to do, and not sure if it's possible, is to write a pdf copy to location of this temp folder no matter if it's on my computer or to whomever I email this to.

Like it would look at the spreadsheet address that is open, and use that location.

Is this possible?









Private Sub CommandButton4_Click()


Application.ScreenUpdating = False
Sheet2.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="C:\Users\RobertG\Desktop\BSE\BSE Certificates" & Range("W10") & "_" & Range("U69") & ".pdf", _
OpenAfterPublish:=True
Application.ScreenUpdating = True

Sheet2.PrintOut

End Sub

 

[Robert_G]

Just bumping the topic with any suggestions or if it’s possible.

 

[Logit]

.

Option Explicit


Sub CommandButton1_Click()




Application.ScreenUpdating = False
Sheet2.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="C:\Users\" & Environ("username") & "\Desktop\BSE\BSE Certificates" & Sheet2.Range("W10").Value & "_" & Sheet2.Range("U69").Value & ".pdf", _
OpenAfterPublish:=False
Application.ScreenUpdating = True


Sheet2.PrintOut


End Sub