1004 Error when Exporting Excel to PDF

Question

I am currently trying to save an excel document as both an .xls and a PDF with one button. It worked for a bit, but now it gives me an error message and highlights the line begining with ActiveSheet.ExportAsFixedFormat. If I take it out it then highlights the MsgBox line to show an problem. I am wondering if this is caused by the fact that there are 3 commands in this function?

Sub Rectangle1_Click()
Dim Path As String
Dim FileName1 As String
Dim FileName2 As String
Path1 = "FILE_PATH_IS_HERE"
Path2 = "FILE_PATH_IS_HERE"
FileName1 = Range("J7")
FileName2 = Range("G3")
ActiveWorkbook.SaveAs Filename:=Path1 & FileName1 & FileName2 & ".xls", FileFormat:=xlNormal

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path2 & FileName1 & FileName2, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

MsgBox "Invoice Saved As Excel Doc and PDF.", , "I Systems"

Thanks for all your help in advance!

Answer 1

'Don't work
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=".\FOLDER\fileName.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

Because the path is relative with .\

You must have a path which is absolute as below with Application.ActiveWorkbook.Path

'work
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Application.ActiveWorkbook.Path+"\FOLDER\fileName.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

Answer 2

I had a similiar problem and the answers above didn't work for me. After an hour of pulling my hair out, here was the source of my problem.

I was passing a cell value as part of generating the filename. I was doing this in the format of fileName:= ActiveWorkbook.Path & "\" & CStr(Workbooks.Cells(i,j).Value) & ".pdf". The text in the cell itself was formatted to be in two rows (i.e. "top row text" + (Alt+K) + "bottom row text"). While the string looks normal in Debug.print, MsgBox, or value previews, I am thinking that there is a hidden character which encodes the new line for the cell. I believe this hidden character causes the error when passed as part of the fileName argument. I'm guessing Excel doesn't pick it up but the OS's file name system does.

In any case, this fixed the issue for me. Best of luck, this was a frustrating one to figure out!

Answer 3

Check your date settings, had a similar problem and my date settings was dd/mm/yyyy, changed to dd mm yyyy without "/" and it works. I use the date as part of filename.

Answer 4

I had a problem on a computer, where in Excel the PDF-Export options in the PDF-Export-Dialog were set to PDF/A-compatible. With this option disabled, the Export worked. I did not find an option to change this setting with the ExportAsFixedFormat function in VBA.