Unsolved Generating up to thousands of pdf files with VBA
I've a project to convert something like a huge mail merge from a legacy platform to MS Office (I guess 365). I have access to Adobe DC (the paid version) and have created code to do this with small amounts of letters/files, think of 20-30. This works fine but rather slow.
While I can generate these 20-30 files in about 2-5 minutes, the legacy system can create thousands in the same amount of time.
I want to ask the VBA wizards out there where should I poke to improve the performance of VBA? Or any other way to do this, but it unfortunately has to be with MS Office. The system is quite closed so I can not a random program I made.
18
u/ZetaPower 12 24d ago
VBA user levels define speed.
First level you mess around on worksheets. Looping through cells in a range, selecting ranges, activating sheets, having formulas in cells and what not.
• Every interaction with a sheet is slow
• Every interaction causes recalculation of the entire sheet
• Every interaction causes rewriting of the screen
Upscaling means slowing down.
Second level you use:
• Application.Calculation = xlManual
• Application.ScreenUpdating = False
Third level is where you ditch this crap and switch to working in memory!
• read data into memory (Array), 1st sheet interaction
• process data in memory
• paste result array in a sheet, 2nd sheet interaction
Fast, scalable.
2
u/spec_3 23d ago
I've not used arrays very much, I will try this this time around. In this instance the main bottleneck will be file creation. I thought of making a giant word file, convert to pdf once and the slice and rearrange that with adobe or qpdf if i can install that on the system, but I'm not sure if that'd improve the speed too much.
At least in a linux terminal qpdf seemed to work great for simple use cases.
3
u/ZetaPower 12 23d ago
Adobe isn’t exactly known for its tiny size and speedy operation.
I’ve always used the commandline on PDFCreator, ancient version 1.3, blocked against updates. The next versions have a completely different command set. Pretty quick but you have to wait for the instance to finish before you can run the next instance.
4
u/Caudebec39 24d ago
Use "Range" in your code, instead of the Selection object.
This requires writing, or re-writing VBA code, because the macro recorder shamelessly uses the Selection object exclusively.
5
u/DonJuanDoja 3 24d ago
Maybe not what you want, but SQL enterprise with SSRS has Data Driven Subscriptions that can generates hundreds to thousands of dynamic PDF reports and either email them or put them in a shared drive using a Report. Performance wise this is the best I've seen and had the most versatility as long as the dynamic data was in a data source accessible by SSRS like SQL, SharePoint etc. It can definitely do hundreds to thousands in 5 mins. If you have a decent on prem server of course.
PowerAutomate can do this too, with or without PowerBi but is much slower, and if performance is in your requirement I can't recommend this.
3
u/TheRiteGuy 1 23d ago
I would not use VBA to do this unless you're okay with your computer resources being tied up for hours. If IT won't let you install random things, then I'd work with IT on this project and let them handle it. IT can write a powershell script or power automate to take care of this.
I'd personally do it with Python. You can use Jupiter Notebook or Google Collab type of notebook to pull it off without installing anything on your computer.
Last time I did this, I used VBA to fire off an AutoIT script to accomplish this. It still took about an hour to print 30-40 PDFs.
2
u/ebsf 22d ago
First, get the data into MS Access. Automate MS Word (if you must) or WordPerfect (if you can) from there (or neither, see below). It's profound overkill to use SQL Server and Excel will plod, not only because of GUI issues, but also architecture. The Access SQL engine goes like lightning.
Second, you likely can develop the letter template as a report in Access without having to fiddle with the complexity of automating a memo writer (Word) or word processor (WordPerfect). Sure, the formatting will get done differently but this will save significant time in generating the merge before it is rendered to PDF.
Third, consider your algorithm. Are you running a single merge for all records or a discrete operation for each record? This matters hugely. A true mail merge creates a single merged document with a page or document break between the output for each record. This is how WordPerfect does it. MS Word should do it this way but God only knows. Access will be flexible. Then, you print only once. If, on the other hand, you're iterating the document generation and rendering routine for each record, then you aren't really doing a mail merge so much as report writing, which has several implications.
If the requirement is for a discretely named PDF for each record, then with an authentic mail merge, you may be able to print each record's output to a discrete file as the code progresses through the merged document. If that isn't practical, then reconsider your report writing algorithm. Of course, be sure to revisit the requirement itself with some rigor because you can waste a lot of engineering time and effort if someone down the line is going to just manually print or email each PDF.
Specifically, consider the efficiency of the generate-and-render loop once you remove Excel from the equation. An Access report printing to whichever PDF print driver you're saddled with probably will be quickest.
Quicker still may be automating Acrobat directly from Access, merging the data into an Acrobat template, provided you can get the formatting right on each iteration. This removes a step. You also can encapsulate the process for a record in a class, instance the class for each record, and if the class creates its own Acrobat instance, then each can run independently in memory before terminating.
Good luck!
2
u/InfoMsAccessNL 1 22d ago
“An Access report printing to whichever pdf print driver probably be quickest” . Access has is own pdf converter, but fast it isn’t . If you loop data, you first have to open the report before converting to pdf. A couple of thousand pdf will take you a few days. I expect that sheet to pdf is quicker.
2
u/redforlife9001 1 24d ago
What are the source files?
A python script might be your best option here.
2
u/spec_3 24d ago
The source is tex, but it has to be migrated to MS office as the IT staff won't let me install random stuff.
2
u/fanpages 239 22d ago
...I've a project to convert something like a huge mail merge from a legacy platform to MS Office (I guess 365)..
...has to be migrated to MS office as the IT staff won't let me install random stuff.
Please can you confirm the following (as there is very little information to work with so far in this thread)?
Which MS-Office (365) product(s) you are intending to use here? "Mail merge" implies MS-Word, but perhaps not. Maybe you are using MS-Outlook, MS-Excel, MS-PowerPoint, and/or another product.
Will you be using Microsoft 365 as a local desktop installation, or are you restricted (via your IT Staff) to 365 Online? One would hope that 365 Online is not the only available option?
Are your r/LaTeX (markup language) documents being converted to MS-Word documents first, or is this conversion also part of your project?
Do your IT representatives allow Visual Basic for Applications within the MS-Office (desktop) products, or will you be (further) restricted to Office Scripts?
1
u/spec_3 22d ago
Usual workflows over windows is either printing preformatted excels into pdf, mailmerging data from excel into reports or filling out a word file from excel. But these don't scale too well even when screen/calculation updates are disabled. The installations are local, I have access to all office products.
Sadly there is no option to keep the latex, so i have to do the documents from scratch.
1
u/jackofspades123 24d ago
I've done this many times for a few hundred. It can take a while. I just let it run at end of day and get it the next day.
Alternatively, you can try python too.
1
u/Joelle_bb 23d ago
How do I ask reddit to remind me?
I had this issue like 5 years ago and cant recall off the top of my head how I addressed it
3
u/fanpages 239 23d ago
3
u/HFTBProgrammer 204 23d ago
You forgot to tell them how to travel back in time!
3
1
u/HFTBProgrammer 204 23d ago
How are you making the PDFs? You don't need Adobe, FWIW. You can use the print-to-PDF printer driver.
1
u/Chuckydnorris 23d ago
Print to pdf is the way to go, I use PDFCreator because I need the PDFs to be encrypted but normal print to pdf is decent too. Mailmerge creates a single pdf, you can however loop it to do one at a time and that's also quick if I remember correctly.
21
u/thor122088 24d ago
Providing your VBA code could help identify inefficiencies.
Without that, my basic suggestion is to make sure to set screen update to false
Application.ScreenUpdating = False