Recently, I was accusing ExportAsFixedFormat of causing a problem with some code. It turned out to not be the culprit. But I was suspicious because I very often get a Document Not Saved runtime error when I export to PDF in a loop. When I get that error, I can click Debug and F5, and it happily continues until it errors again. Of course, watching something loop eighty-six times to make sure it doesn’t error kind of defeats the purpose of the loop. Today, I took a stand.
I have this code that changes the page fields on a pivot table and exports a range. The result is eighty-six PDFs in a folder that I sew together into one big report. There are two page fields, so there are two loops. Here’s the inner loop.
For Each rCell In .Range(.Cells(vaRows(i), 2), .Cells(vaRows(i), 2).End(xlDown)).Cells
pt.PivotFields("Final").CurrentPage = rCell.Value
wshDashFuelCust.Range("AK7").Resize(62, 4).ExportAsFixedFormat xlTypePDF, sPath & rCell.Value & "_" & aProducts(i)
DoEvents
Next rCell
You see my pathetic DoEvents attempt at avoiding the error. The error stops on the ExportAsFixedFormat line, so the DoEvents doesn’t actually help. When the error happens, a .tmp file is left in the directory. And when I do the Debug, F5 thing, the .tmp file stays there forever. Clearly this is a temporary file that would someday become a PDF if not for this error.
The .tmp file is my evidence that the processed finished. I can ignore the error and as long as there is no temp file, I’ll know the error never occurred. I rewrote the loop thusly:
For Each rCell In .Range(.Cells(vaRows(i), 2), .Cells(vaRows(i), 2).End(xlDown)).Cells
pt.PivotFields("Final").CurrentPage = rCell.Value
Do
On Error Resume Next
Kill sPath & Dir(sPath & "*.tmp")
wshDashFuelCust.Range("AK7").Resize(62, 4).ExportAsFixedFormat xlTypePDF, sPath & rCell.Value & "_" & aProducts(i)
On Error GoTo 0
Loop While Len(Dir(sPath & "*.tmp")) > 0
DoEvents
Next rCell
As long as there’s a file with a tmp extension, I keep trying to export. The Kill statement needs to be inside the On Error because there won’t be anything to Kill the first time.
After a rigorous test of one time, it worked. I should have put a little loop counter in there to see how many times it errored. And maybe to exit out in case it gets into an infinite loop situation.
