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

sydneylacey

New Member
Joined
Feb 13, 2018
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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:

VBA 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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,087
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
 
Solution

sydneylacey

New Member
Joined
Feb 13, 2018
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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".
 

Forum statistics

Threads
1,148,006
Messages
5,744,311
Members
423,859
Latest member
joannetung

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top