VBA to save worksheet as PDF (opens dialog box for end user)

[sydneylacey]

I'm in the process of expanding my VBA knowledge/skills and I've hit a snag in my current little self-assigned learning project. I have a simple dynamic calendar sheet that I'd like for end-users to be able to save as PDF to a location they select with the name pre-loaded based on the contents of a particular cell. The worksheet will have two buttons (one to clear the contents and reset the calendar and one to active the dialogue box to save the pdf).

I thought I was on the right track. Running the code opens the save as dialogue box and it has the correct file name but actually hitting the Save button gives me the Run-time error "1004" message.

I'm sure it's an easy fix for someone with more skill than this newbie has under her belt.

Here's my code:

Private Sub clear_btn_Click()
Call ClearContents
End Sub

Private Sub SavePDF_btn_Click()
Call Save_Excel_As_PDF
End Sub

Sub Save_Excel_As_PDF()

    Dim PDFFilename As Variant
   
    PDFFilename = Application.GetSaveAsFilename( _
        InitialFileName:=ActiveSheet.Range("B3").Value, _
        FileFilter:="PDF, *.pdf", _
        Title:="Save As PDF")

        With ActiveSheet.PageSetup
        .Orientation = x1Landscape
        .PrintArea = "$B$3:$H$14"
        .Zoom = False
        .FitToPagesTall = 1
        .FitToPagesWide = 1
    End With
       
    ActiveSheet.ExportAsFixedFormat _
        Type:=x1TypePDF, _
        Filename:=ActiveSheet.Range("B3").Value, _
        Quality:=x1QualityStandard, _
        IncludeDocProperties:=False, _
        IgnorePrintAreas:=False, _
        From:=1, _
        To:=1, _
        OpenAfterPubllish:=True
    
End Sub

 

[John_w]

You have some spelling mistakes which are probably causing the error. These are caught if you have Option Explicit at the top of the module, meaning that all variables must be declared (Dim statements) and Excel constants such as xlLandscape must be correct.

As a start, change as follows:

x1Landscape -> xlLandscape
x1TypePDF -> xlTypePDF
Filename:=ActiveSheet.Range("B3").Value, _ - > Filename:=PDFFilename, _
x1QualityStandard -> xlQualityStandard
OpenAfterPubllish -> OpenAfterPublish

 

[sydneylacey]

You have some spelling mistakes which are probably causing the error. These are caught if you have Option Explicit at the top of the module, meaning that all variables must be declared (Dim statements) and Excel constants such as xlLandscape must be correct.

As a start, change as follows:

x1Landscape -> xlLandscape
x1TypePDF -> xlTypePDF
Filename:=ActiveSheet.Range("B3").Value, _ - > Filename:=PDFFilename, _
x1QualityStandard -> xlQualityStandard
OpenAfterPubllish -> OpenAfterPublish

Thanks, John. I'll fix those errors and give it another try. Must be time for new glasses. I appreciate the response. Maybe someday I'll be able to swap the "newbie" label for "partially competent".