Macro Save to PDF In Excel

[JERRY89]

Dear All,

I do have a macro command that i record but the problem is i do want a dynamic file save location and i want the file name follow my excel file name because i usually will make a few copy of this excel file to be created as pdf file with different file name.Is there a solution for the below macro :

Sub PDF()
'
' PDF Macro
'

'
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"P:\JERRY\Payment advice-15th\Payment Advice_2016-17\2016.10\STM Tech.pdf"
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True
Application.WindowState = xlMinimized
End Sub

[HansV]

Do you want to save the PDF file in the same folder as the Excel workbook?

[JERRY89]

Hi Hans,
Yup. It's better if can save in the same place with the excel file.

[Rudi]

Give this a try.
(Note: I could not test it as I don't have the PDF add-in installed on in Office).

Code: Select all

Sub SaveToPDF()
Dim wbA As Workbook
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
    
    On Error GoTo errHandler
    Set wbA = ActiveWorkbook

    'get active workbook folder, if saved
    strPath = wbA.Path
    If strPath = "" Then
        strPath = Application.DefaultFilePath
    End If
    strPath = strPath & "\"

    'create default name for savng file
    strFile = Split(ActiveWorkbook.Name, ".")(0) & ".pdf"
    strPathFile = strPath & strFile

    'export to PDF
    wbA.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=strPathFile, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
    'confirmation message with file info
    MsgBox "PDF file has been created: " _
           & vbCrLf _
           & strPathFile, vbInformation

exitHandler:
    Exit Sub
errHandler:
    MsgBox "Could not create PDF file", vbExclamation
    Resume exitHandler
End Sub

[JERRY89]

Hi Rudi,

Thanks Ya..It work great..