CODEWITHSUNDEEP
excelvba
Elijah
Elijah

Reputation: 11

"Run-time error 1004" with VBA code initiated PDF export

I'm trying to export a sheet to PDF, but when I run the code I keep getting a 1004 error.

I believe that it is linked to the folder path to the save destination. I've tried defining the file path in different ways but still get the error.

This code is supposed to pull a part of the file name from cell B1 and the file path from the location of the worksheet. The PDF is supposed to be saved to the location of the worksheet. I'm fairly new to VBA. Any help is appreciated!

Here is the code

Sub ExportAsPDFTest()

Dim Name As String
Dim Preface As String


Name = Cells(1, "B").Value
Preface = "PreR Summer 2019 - "

ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    FileName:=ActiveWorkbook.Path & Preface & Name & ".pdf", _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=False, _
    IgnorePrintAreas:=False, _
    From:=1, _
    To:=1, _
    OpenAfterPublish:=False

End Sub

Upvotes: 0

Views: 1738

Answers (2)

Louis
Louis

Reputation: 3632

You're missing a backslash \ (or / if you're on Mac) after ActiveWorkbook.Path. You can use Application.PathSeparator so it will work on both:

Sub ExportAsPDFTest()
    Dim Name As String
    Dim Preface As String

    Name = Cells(1, "B").Value
    Preface = "PreR Summer 2019 - "

    ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=ActiveWorkbook.Path & Application.PathSeparator & Preface & Name & ".pdf", _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=False, _
        IgnorePrintAreas:=False, _
        From:=1, _
        To:=1, _
        OpenAfterPublish:=False
End Sub

Hope this helps.

Upvotes: 1

Drinkwater
Drinkwater

Reputation: 23

Please add msgbox Activeworkbook.path to check your save path, you need add one more "\"

Upvotes: 0

Related Questions