CODEWITHSUNDEEP
excelvba
Eboyer
Eboyer

Reputation: 107

Can't solve error 1004 with VBA ExportAsFixedFormat

I've been putting up together a small macro in VBA to save a table as a PDF in Excel. This is the code I've written: 

Sub ExportPDF()
    Dim File_name As String

    File_name = "C:\Test\" & _
      "Rapport du " & Date & "_Assurances.pdf"

    Sheets("Assurances.Rapport").Select

    Dim Last_row As String

    Last_row = "C1:Y" & Range("Z2").Value
    ActiveSheet.PageSetup.PrintArea = Last_row

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    FileName:=File_name, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=True

    Sheets("Assurances").Select
End Sub

I've run it on my personal Mac, with Excel 2019 and it seems to be working, I've also run it on windows 7 with Excel 2019, also working.

But I tried running it on Mac and Windows 10, both running Excel 2016, and it didn't work on either one of them: On Mac, it opens a print window, waiting for me to select a printer.

On Windows, it gives me the following error:

"Run-time error '1004': Document not saved. The Document may be open, or an error may have been encountered when saving."

The error point to the 4 lines of the "ExportAsFixedFormat".

What could have caused the problem?

Upvotes: 2

Views: 5592

Answers (1)

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

Try changing this line:

File_name = "C:\Test\" & _ "Rapport du " & Date & "_Assurances.pdf""

to

File_name = "C:\Test\" & _ "Rapport du " & Format(Date,"dd-mm-yyyy") & "_Assurances.pdf"

and then test. Date format having "\" is perhaps causing the issue which is prohibited by windows in a file name by default.

Following characters are to be avoided /\:?*"<>|

Upvotes: 2

Related Questions