save as pdf with ranges in file name

[Arie Bos]

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...

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

 

[Arie Bos]

I have already found answers on this forum.

Setting the name of a PDF file export

Hey there! I have some VBA code that I slightly adapted from the internet and it's working pretty well. The code takes a page on my workbook, exports it to a PDF, then attaches it to an email. I work for a non-profit and I'm hoping to use this to speed up invoicing for some of our training...

www.mrexcel.com

and

I need help with VBA to print to a default printer

Hi, I have the following code which at the click of a button saves my excel file to a pdf into a given folder location. The code works perfectly but i would like to take it a step further and either, prior to saving or after saving to pdf, print the document at the click of the same button. The...

www.mrexcel.com

Thanks to all previous contributors!