"Run-time error '1004'. Document not saved." when exporting to PDF with VBA

sj_robertson

New Member
Joined
Mar 21, 2010
Messages
7
I have a macro that runs from a key combo to save the active worksheet to PDF.

It seems that this macro runs correctly and performs the requested operation (export to PDF), however, it throws up a run-time error, which prevents the macro from completing.

Code:
Sub ...
...
...
..
With ActiveSheet
.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        FileName:="C:\...correct path triple-checked!...\" & Cells(11, 10).Value & "_" & Cells(15, 8).Value & ".pdf", _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        OpenAfterPublish:=False
'
End With
'
Sheets("Sheet1").Delete
'
End Sub

VBA repeatedly tells me (highlights during Debug) the offending code starts at .ExportAsFixedFormat and ends after OpenAfterPublish:=False, yet I cannot find anything wrong with the coding, despite having corrected the coding and reverted to my original format numerous times. The only thing I can assume is that the underscore in the filename is causing the issue, yet all resources I have checked specify that underscore is a valid character...

What is even more confusing is that the macro does produce my final PDF (with the file name in the format CellRef_CellRef); it just fails to delete sheet 1 after doing so... If I then, after deleting Sheet1 manually, run the macro again, the macro seems to then run perfectly (overwriting the previously exported file) without throwing up any errors whatsoever.

Any help would be greatly appreciated, as I'm certain I've grown a few more grey hairs since this morning!
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
I should mention that I have attempted:

Application.ScreenUpdating = False

and another Application.(something about Alerts, cannot remember what)

to try and bypass the redundant run-time error alert, however this does not seem to affect run-time error alerts...
 
Upvote 0
The code looks good and perhaps someone else can figure out what is going on to remove the error altogether, however, if all you want to do is supress errors just put this line of code in the first line after the Sub

On Error Resume Next

Downside of this is that if the code experiences a legitimate error you wouldn't know until you went to find the PDF and it wasn't there.
 
Upvote 0
When I removed
Code:
Sheets("Sheet1").Delete
I didn't get an error (without suppressing the error)

-- g
 
Upvote 0
however, if all you want to do is supress errors just put this line of code in the first line after the Sub

On Error Resume Next

Downside of this is that if the code experiences a legitimate error you wouldn't know until you went to find the PDF and it wasn't there.

Thanks, this successfully suppressed the error, which is the main thing at the moment (-as I say, the macro performs flawlessly, so I'm not quite sure what's going on...

When I removed

Code:
Sheets("Sheet1").Delete

I didn't get an error (without suppressing the error)

-- g

That's interesting... Sheet1 is actually created at the beginning of the macro using "Sheets.Add Type:=". The macro then selects other sheets and copies values to Sheet1 before exporting it. I can't see why it would have an issue recognising Sheet1 in order to delete it, if Sheets("Sheet1").Select did not return an error earlier... I'm completely mystified... Perhaps I need to run the sheet deletion in a second macro (though that would defeat the object of a macro, would it not!)?
 
Upvote 0
I think I actually figured out what is causing this.
If there are no cells with data in the ActiveSheet then there is nothing to 'print' into the PDF. I suspect the file that is being created is just a blank PDF page.

You should be able to avoid the error altogether with the following.

Code:
If ActiveSheet.UsedRange.Cells.Count > 1 Then
With ActiveSheet
    .ExportasFixedFormat...
...
End If
 
Sheets("Sheet1").Delete
This code will NOT create a blank PDF. If you absolutely want to create the file (even if it is blank) than the way you have it now is correct.
 
Last edited:
Upvote 0
Maybe the unqualified Cells property is the cause of the error. Notice the dots before Cells below:

Rich (BB code):
FileName:="C:\...correct path triple-checked!...\" & .Cells(11, 10).Value & "_" & .Cells(15, 8).Value & ".pdf", _
 
Upvote 0
Maybe the unqualified Cells property is the cause of the error. Notice the dots before Cells below:

Rich (BB code):
FileName:="C:\...correct path triple-checked!...\" & .Cells(11, 10).Value & "_" & .Cells(15, 8).Value & ".pdf", _

That is an interesting point, although I thought if you didn't qualify Cells that ActiveSheet was implied. Worth a try though if you are still having problems.
 
Upvote 0
If there are no cells with data in the ActiveSheet then there is nothing to 'print' into the PDF. I suspect the file that is being created is just a blank PDF page.

Sheet1 is an invoice created from a template which references various cells in other sheets in the workbook, so it actually contains numerous items of data.


Maybe the unqualified Cells property is the cause of the error. Notice the dots before Cells below:

Rich (BB code):
FileName:="C:\...correct path triple-checked!...\" & .Cells(11, 10).Value & "_" & .Cells(15, 8).Value & ".pdf", _

Hmmm ill have to try this solution later. I'd presumed the With ActiveSheet command would have implied that the cells were on the sheet...

Thanks to all for the continued help! Its much appreciated!
 
Upvote 0
Maybe the unqualified Cells property is the cause of the error. Notice the dots before Cells below:

Rich (BB code):
FileName:="C:\...correct path triple-checked!...\" & .Cells(11, 10).Value & "_" & .Cells(15, 8).Value & ".pdf", _

@Andrew - Thanks a thousand times. I removed the command to suppress errors and qualified the cells as instructed and it now appears to be working flawlessly. Again, many, many thanks :)
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top