CODEWITHSUNDEEP
excelvbapdf
Geographos
Geographos

Reputation: 1456

Saving document as a PDF with modified filename

I am trying to save my Excel file as a PDF but with custom file name.

I would only add a piece to the prompted filename from the Excel file.

According to the queries here:

Save excel as PDF in current folder using current workbook name

Save excel as PDF in current folder using current workbook name

My code looks as follows:

Sub DPPtoPDF()

    ThisWorkbook.Sheets.Select

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
      ThisWorkbook.Path & "\" & ThisWorkbook.Name, _
      Quality:=xlQualityStandard, IncludeDocProperties:=True, _
      IgnorePrintAreas:=False, OpenAfterPublish:=True

    Sheets("Frontsheet").Select

End Sub

From this code we know the PDF filename will be the Excel filename.

I tried something like this:

ThisWorkbook.Sheets.Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= &fName $ "-Route-Aprooval.pdf" _
  ThisWorkbook.Path & "\" & ThisWorkbook.Name, _
  Quality:=xlQualityStandard, IncludeDocProperties:=True, _
  IgnorePrintAreas:=False, OpenAfterPublish:=True

I get an error as per in the picture below.

enter image description here

I can see something is up, as my code is turning red.

The code:

ThisWorkbook.Sheets.Select

 ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ 
   " &fName $ "-Route-Aprooval.pdf""
 ThisWorkbook.Path & "\" & ThisWorkbook.Name, _
   Quality:=xlQualityStandard, IncludeDocProperties:=True, _
   IgnorePrintAreas:=False, OpenAfterPublish:=True

also doesn't work.

Any code behind the Filename:= destroys whole code.

I want to keep the name as in Excel, but add another part of the name after dash (per the image above).

Where should I place my new output filename?

Upvotes: 0

Views: 857

Answers (1)

Daghan
Daghan

Reputation: 769

Try the code below. 

Sub DPPtoPDF()
  Dim Custom_Name as string
  ThisWorkbook.Sheets.Select

  Custom_Name= ThisWorkbook.Name & "-route approval" & ".pdf"

   ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
   ThisWorkbook.Path & "\" & Custom_Name, _
   Quality:=xlQualityStandard, IncludeDocProperties:=True, _
   IgnorePrintAreas:=False, OpenAfterPublish:=True

   Sheets("Frontsheet").Select

   End Sub

Upvotes: 1

Related Questions