Arie Bos
Board Regular
- Joined
- Mar 25, 2016
- Messages
- 224
- Office Version
- 365
- Platform
- Windows
Hello everyone,
I made a workbook with several user forms in it.
I would like to:
I made a workbook with several user forms in it.
I would like to:
- Save one sheet ("Overview") as a pdf, using a few named cells from another sheet ("Data"). In below code, the pdf is still named "myPDFFile.pdf", but I would like to have it called "<date> - <CompanyName> - <Material>.pdf"
- Allow the user to save the excel file under the same name as the pdf for further editing, but not under the original name, so that one always stays blank
- Check if a previous file name already exist, delete these (both pdf and xlsx) and save the new version under the same name. Alternatively, the new version could be saved with a (2) at the end of the file name.
VBA Code:
Private Sub CommandButton1_Click()
Worksheets("Overview").Activate
'Create and assign variables
Dim saveLocation As String
saveLocation = "D:\Projects\SSD\SonIQ\Sales\IM results\myPDFFile.pdf"
Dim makedate As String
makedate = Me.txtDate.Text
Dim client As String
client = Range("Data!CompanyName")
Dim material As String
material = Range("Data!Material")
'Save Active Sheet(s) as PDF
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=saveLocation, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True
Unload Me
End Sub