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

Robert_G

New Member
Joined
Jul 13, 2018
Messages
48
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
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,849
.
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,511
Messages
5,596,576
Members
414,079
Latest member
Frills

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top