Trying to print a sheet into a pdf and allowing the user to choose the file name and destination. I am not sure why the code is not working. Excel highlights the If line and says "Object variable or With block variable not set"

Sub Pdf_Purchase_Order()
'

Dim fileSave As FileDialog
Set fileSave = Application.FileDialog(msoFileDialogSaveAs)

Sheets("Digital PO").Select

With fileSave

    If .Show = -1 Then

        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:=.SelectedItems(1), _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, openafterpublish:=False

    End If
End With

    MsgBox "PO exported to PDF successfully!"

End Sub

Trying to print a sheet into a pdf and allowing the user to choose the file name and destination. I am not sure why the code is not working. Excel highlights the If line and says "Object variable or With block variable not set"

Sub Pdf_Purchase_Order()
'

Dim fileSave As FileDialog
Set fileSave = Application.FileDialog(msoFileDialogSaveAs)

Sheets("Digital PO").Select

With fileSave

    If .Show = -1 Then

        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:=.SelectedItems(1), _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, openafterpublish:=False

    End If
End With

    MsgBox "PO exported to PDF successfully!"

End Sub

• excel
• vba
• pdf
• path
• filenames

• 4 Is this on a Mac ? If so see stackoverflow/questions/51416802 – CDP1802 Commented Mar 20 at 15:34
• Yes, it is on a mac... I have tried the ideas from the link you have sent but not working. Going to try below code and see what happens. Will the below code work for a mac? – Toni Marais Commented Mar 21 at 13:00
• In the mac code, the function creates a new folder everytime, right? How do I create the folder and then use that path for all future prints. I want all the PO's filed in the same place. For reference, the PO blanks once it has been pdfed and the same sheet is used for the next one. – Toni Marais Commented Mar 21 at 13:39

1 Answer 1

Export Worksheet to PDF

• IMO, using the GetSaveAsFilename method of the Application object seems more appropriate.

Sub Pdf_Purchase_Order()

    ' Define constants.
    Const SHEET_NAME As String = "Digital PO"
    Const FILE_FILTER As String = "PDF Files (*.pdf),*.pdf"
    
    ' Reference the workbook.
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    ' Build the destination folder path based on the path of the workbook.
    Dim FolderPath As String: FolderPath = wb.Path ' the same as the workbook
    If Len(FolderPath) = 0 Then
        MsgBox "The workbook """ & wb.Name & """ was never saved!", _
            vbExclamation
        Exit Sub
    End If
    'FolderPath = FolderPath & Application.PathSeparator & "MyPDFs"
    
    ' Let the user enter (choose) the file name.
    ' Note that if the file exists, it will be overwritten without confirmation.
    Dim FilePath As Variant: FilePath = Application.GetSaveAsFilename( _
        InitialFileName:=FolderPath, _
        FileFilter:=FILE_FILTER)
    If FilePath = False Then
        MsgBox "Abandoned exporting PO to PDF!", vbExclamation
        Exit Sub
    End If
    
    ' Ensure saving the workbook before exporting.
    'If Not wb.Saved Then wb.Save
    
    ' Reference the worksheet (no need to select).
    Dim ws As Worksheet:
    On Error Resume Next
        Set ws = wb.Sheets(SHEET_NAME)
    On Error GoTo 0
    If ws Is Nothing Then
        MsgBox "The sheet """ & SHEET_NAME & """ doesn't exist in workbook """ _
            & wb.Name & """!", vbExclamation
        Exit Sub
    End If
    
    ' Export to PDF.
    ws.ExportAsFixedFormat _
        Type:=xlTypePDF, Filename:=FilePath, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False

    ' Inform.
    MsgBox "PO exported to PDF.", vbInformation

End Sub