VBA Compile Error: Syntax Error for Saving Excel in PDF for Excel 2019

Question

I'm getting a "Compile Error: Syntax Error" when running the code below using Excel 2019. It works with the older version Excel, but not on 2019. How can I fix this and what's causing it?

the error line

ReportSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

        ThisWorkbook.Path & "\" & Cell, _

        Quality:=xlQualityStandard, IncludeDocProperties:=True, _

        IgnorePrintAreas:=True, OpenAfterPublish:=False

The entire code

Option Explicit

Private Sub CommandButton1_Click()



Dim MyFolder As String, MyFile As String

Dim StartTime As Double

Dim MinutesElapsed As String

Dim Filename As String

Dim Cell As String

Dim Counter As Long



            If ThisWorkbook.Sheets("Sheet1").Range("C7").Value = vbNullString Then

            MsgBox "Enter Tab Name"

            Exit Sub



            End If


StartTime = Timer



            With Application.FileDialog(msoFileDialogFolderPicker)

               .AllowMultiSelect = False

               .Title = "Select a Folder"

               If .Show = True Then

               MyFolder = .SelectedItems(1)

               End If



               If .SelectedItems.Count = 0 Then Exit Sub

               Err.Clear

            End With


            'Turns settings off
            Application.ScreenUpdating = False

            Application.DisplayStatusBar = False

            Application.EnableEvents = False

            Application.Calculation = xlCalculationManual


            MyFile = Dir(MyFolder & "\", vbReadOnly)


Do While MyFile <> ""

        DoEvents

        On Error GoTo 0

        Workbooks.Open Filename:=MyFolder & "\" & MyFile, UpdateLinks:=False


Dim ReportSheet As Worksheet

Dim MySheet As String

Dim allColumns As Range



MySheet = ThisWorkbook.Sheets("Sheet1").Range("C7").Value



Set ReportSheet = Sheets(MySheet)

Set allColumns = ReportSheet.Columns("N:S")

        allColumns.Hidden = True



        With ReportSheet.PageSetup

         .Zoom = False

         .FitToPagesWide = 1    '.FitToPagesTall = 1

        End With


Filename = ActiveWorkbook.Name



Cell = Replace(Filename, ".xlsx", ".PDF")   

    ReportSheet.Select



    ReportSheet.PageSetup.Orientation = xlLandscape


    ReportSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

        ThisWorkbook.Path & "\" & Cell, _

        Quality:=xlQualityStandard, IncludeDocProperties:=True, _

        IgnorePrintAreas:=True, OpenAfterPublish:=False



Counter = Counter + 1

0

        Workbooks(MyFile).Close SaveChanges:=False

        MyFile = Dir

Loop


    'turns settings back on that you turned off before looping folders



        Application.ScreenUpdating = True

        Application.DisplayStatusBar = True

        Application.EnableEvents = True

        Application.Calculation = xlCalculationManual


MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")

MsgBox "Successfully Converted " & Counter & " Files in " & MinutesElapsed & " minutes", vbInformation



End Sub

Could be the way you are calling your workbook / worksheet. They are not objects. Try Dim wb as workbook: Set wb = ThisWorkbook, Dim ReportSheet as worksheet: Set ReportSheet as wb.worksheets("Sheet1"), and then define MySheet as ReportSheet.Range("C7").value — Mech
– Mech, Commented Feb 20, 2020 at 2:45

Ricardo Diaz · Accepted Answer · 2020-02-20 02:56:41Z

Leaving a blank line when you are using the line continuation _ character is not allowed in any Excel version (AFAIK)

The code giving you trouble should be:

ReportSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & Cell, _
                                Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                                IgnorePrintAreas:=True, OpenAfterPublish:=False

Try indenting your code properly and remove the extra lines.

Code:

Option Explicit

Private Sub CommandButton1_Click()



    Dim MyFolder As String, MyFile As String

    Dim StartTime As Double

    Dim MinutesElapsed As String

    Dim Filename As String

    Dim Cell As String

    Dim Counter As Long



    If ThisWorkbook.Sheets("Sheet1").Range("C7").Value = vbNullString Then

        MsgBox "Enter Tab Name"

        Exit Sub



    End If


    StartTime = Timer



    With Application.FileDialog(msoFileDialogFolderPicker)

        .AllowMultiSelect = False

        .Title = "Select a Folder"

        If .Show = True Then

            MyFolder = .SelectedItems(1)

        End If



        If .SelectedItems.Count = 0 Then Exit Sub

        Err.Clear

    End With


    'Turns settings off
    Application.ScreenUpdating = False

    Application.DisplayStatusBar = False

    Application.EnableEvents = False

    Application.Calculation = xlCalculationManual


    MyFile = Dir(MyFolder & "\", vbReadOnly)


    Do While MyFile <> ""

        DoEvents

        On Error GoTo 0

        Workbooks.Open Filename:=MyFolder & "\" & MyFile, UpdateLinks:=False


        Dim ReportSheet As Worksheet

        Dim MySheet As String

        Dim allColumns As Range



        MySheet = ThisWorkbook.Sheets("Sheet1").Range("C7").Value



        Set ReportSheet = Sheets(MySheet)

        Set allColumns = ReportSheet.Columns("N:S")

        allColumns.Hidden = True



        With ReportSheet.PageSetup

            .Zoom = False

            .FitToPagesWide = 1                  '.FitToPagesTall = 1

        End With


        Filename = ActiveWorkbook.Name



        Cell = Replace(Filename, ".xlsx", ".PDF")

        ReportSheet.Select



        ReportSheet.PageSetup.Orientation = xlLandscape


        ReportSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & Cell, _
                                        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                                        IgnorePrintAreas:=True, OpenAfterPublish:=False



        Counter = Counter + 1



        Workbooks(MyFile).Close SaveChanges:=False

        MyFile = Dir

    Loop


    'turns settings back on that you turned off before looping folders



    Application.ScreenUpdating = True

    Application.DisplayStatusBar = True

    Application.EnableEvents = True

    Application.Calculation = xlCalculationManual


    MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")

    MsgBox "Successfully Converted " & Counter & " Files in " & MinutesElapsed & " minutes", vbInformation



End Sub

Side note: Didn't review other parts of your code

Thanks Ricardo, it must've been when I copied the code over to a new sheet. Extra lines been added as a result. Also, I'm thinking to move some of the code from the VBA into the front sheet, which will make the framework more customizable . I'm wondering if you'll be interested in helping out with that.

Collectives™ on Stack Overflow

VBA Compile Error: Syntax Error for Saving Excel in PDF for Excel 2019

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related