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.
|
1 2 3 4 5 6 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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.
I love your DoEvents remark. Most of the times I’ve tried to use it have been similarly hopeful/futile. It’s kind of like how I used to run CodeCleaner fairly often to try to fix something, something that generally turned out to be bad code.
It’s been a while since I’ve done the kind of project you describe, but I think the following worked. Iterate through the page fields, saving the results off as values-only worksheets, all in the same workbook. Then just select all sheets and print (save?) the results to PDF. I probably just did the printing (saving?) manually, but it’s only one workbook.
“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”
Dick,
1. Had you thought of using slicers instead of manipulating the page field? Its just an alternate control method, but I have used it for PDFing and have never had your “Document Not saved” error. (Perhaps it forces Excel to keep up with VBA?)
2. Have you tried rearranging the pivot and using the “Insert page break after each item” control under [Field Settings] for the pivot field? One benefit is that it produces only one PDF file with multiple pages.
Hmmm, interesting ideas. I could copy and paste all to one worksheet with page breaks and just print that one worksheet.
I only use slicers for a better UI experience, but I’ll try them and see if it makes a difference.
The pivot table feeds a different area that’s formatted prettily, so the page breaks thing wouldn’t work. Wouldn’t it be nice if we could format pivot tables to look respectable?
Thanks for the suggestions.
Respectable is in the eye of the beholder.
As in:
Dude1: BEHOLD!!!
Dude2: Ooh. Respectable!
In a similar scenario I selected sheets for export and saved with ExportAsFixedFormat to PDF in one shot.
Is there any advantage to export them separately and sew together after saving?
They’re all the same page. I just change a pivot table page filter and print the same range over and over. That’s why they’re exported separately and sewn together.
This subject was also recently discussed on Clearly and Simply
http://www.clearlyandsimply.com/clearly_and_simply/2015/07/export-excel-dashboards-improved-version.html
Suggested solution there was to place each dashboard on a separate sheet in a temporary Excel workbook, export the entire workbook to PDF, and then close the temporary workbook without saving.
What do you use to sew PDFs together?
I’m definitely going to try putting it all in one workbook. I use PDF Split and Merge. I can script its command line to insert bookmarks and other cool stuff. I suppose I’ll lose that if I go the temporary workbook route.
I quite like the Show Report Filter Pages Option for this kind of thing. If you were doing it manually from the menu options it would add an extra 80 odd tabs to your workbook in seconds and you could print then them all to PDF in one go. It’s only one line of VBA.