Can't solve error 1004 with VBA ExportAsFixedFormat

Question

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?

Did you check File_name value? Depends on your computer setup, you will get protected characters -> /. Did you check if you have a value on Z2? — David García Bodego
PageSetup.PrintArea is expecting one Range and you are bringing one string... did you check it? — David García Bodego
Trying your code, on a Windows 7/10 machine: Date is not working properly on the filename, Z2 cell can not be empty, the range it is not ok as you are returning a string when one range was expected and the directory should exist. Fixing that ones, your code works. So Last_row should be a range and Last_row = Range("C1:Y" & Range("Z2").Value2) — David García Bodego

shrivallabha.redij shrivallabha.redij · Accepted Answer · 2019-10-21T10:14:04

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 /\:?*"<>|

Can't solve error 1004 with VBA ExportAsFixedFormat

1 Answers