save as pdf with ranges in file name

Arie Bos

Board Regular
Joined
Mar 25, 2016
Messages
153
Office Version
  1. 365
Platform
  1. Windows
Hello everyone,

I made a workbook with several user forms in it.

I would like to:
  1. 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"
  2. 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
  3. 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.
This is my code so far...

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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Arie Bos

Board Regular
Joined
Mar 25, 2016
Messages
153
Office Version
  1. 365
Platform
  1. Windows
I have already found answers on this forum.
and

Thanks to all previous contributors!
 

Forum statistics

Threads
1,137,061
Messages
5,679,388
Members
419,825
Latest member
MegastarMagus

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