Share via

Excel Macro - create PDF

Anonymous
2022-12-09T17:09:54+00:00

I'm using the below code on an excel sheet to create a PDF however it saves a local copy which I don't want, I just want it to generate a PDF for me to view.

What am I doing wrong... all help greatly appreciated.

Private Sub CommandButton1_Click() 

Application.ScreenUpdating = False 

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, \_ 

       OpenAfterPublish:=True 

Application.ScreenUpdating = True

End Sub

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Nikolino 2,120 Reputation points
    2022-12-12T12:22:35+00:00

    Public Sub PDFv_Export()
    Dim vntReturn As Variant
    vntReturn = Application.GetSaveAsFilename(InitialFileName:=strFilename, _
    FileFilter:="PDF-File (*.pdf), *.pdf)", Title:="Generate PDF")
    If vntReturn <> False Then
    Call ThisWorkbook.ExportAsFixedFormat(Type:=xlTypePDF, _
    Filename:=vntReturn, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=True)
    End If
    End Sub

    Paste into a simple "Button" control. As far as I've tested it works.

    3 people found this answer helpful.
    0 comments
  2. Nikolino 2,120 Reputation points
    2022-12-09T17:37:54+00:00

    Enter the code as given below and then it will work :-).

    Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, OpenAfterPublish:=True
    Application.ScreenUpdating = True
    End Sub

    I wish you a lot of fun and success with Excel :-).

    2 people found this answer helpful.
    0 comments
  3. Anonymous
    2022-12-12T13:19:08+00:00

    That's brilliant, thank you for your help

    0 comments
  4. Anonymous
    2022-12-12T08:40:11+00:00

    Enter the code as given below and then it will work :-).

    Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, OpenAfterPublish:=True
    Application.ScreenUpdating = True
    End Sub

    I wish you a lot of fun and success with Excel :-).

    Hi NikolinoDE, thank you for your message but this still doesn't work. It generates and opens the PDF but also saves a local copy to my hard drive as the same name as the Excel sheet. I would like it to create a PDF document and open it but not to save it automatically. Any other ideas :)

    0 comments