Run-time error '1004': Method 'ExportAsFixedFormat' of object '_Worksheet' failed

[ffmonkey]

Hi Everyone,

I've been trying (and failing) to solve an error the following bit of VBA for the last few weeks, and I haven't seen someone post online with a similar issue.

Essentially, I have a bit of code that creates a few hundred PDFs based on information held in other sheets. The code looks like this:

Please Login or Register  to view this content.

Randomly I will get the following error: Run-time error '1004': Method 'ExportAsFixedFormat' of object '_Worksheet' failed

The line highlighted is the PDF export line.

Hitting resume continues the code, but there will a PDF from the selection that has been missed.

ufProgress is a userform for marking the progress of the macro for users of the sheet.

As you may be able to tell, savepath refers to a network location (which I do have permissions to access). I just ran the code without error when saving to C:\PDFs.

Any help would be greatly appreciated.

[davesexcel]

If you just get one sheet that errors, my first assumption is that the file name is not valid

[ffmonkey]

I'd agree, however, the filename always looked fine when I go into debug. It's set up in the below lines:

Please Login or Register  to view this content.

The cell on the PDF sheet has the following formula to ensure the length of a filename is four numbers:

=IF(LEN(F2)=4,F2,IF(LEN(F2)=3,"0"&F2,IF(LEN(F2)=2,"00"&F2,"000"&F2)))

So shouldn't be throwing up any errors when all of the values of F2 are 1 - 4 digit numbers.

[davesexcel]

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[ffmonkey]

It's going to be next to impossible to upload a copy of the workbook. The error only occurs when I'm running it when the lastStore value is in the high hundreds, which is far too much data for me to desensitise.

[davesexcel]

what is the actual file name when the error happens?

[ffmonkey]

The issue doesn't occur on the same file every time (otherwise it would be an obvious fix - and I've checked to make sure that the file names aren't error values). They're always a network file path (generally) G:\PDFs\ and a four digit number followed by .pdf. For example:

G:\PDFs\1242.pdf