What do you do when you’ve got a bunch of tabular data and need each record converted into a separate PDF file? Surprisingly, you can use Excel to do that.
Why?
We have a client who was receiving data from an external vendor and needed to convert the table of data into separate PDF report files so they could upload it into their document management system. They knew what they wanted it to look like, but weren’t sure how to take the tabular data and put it into the report template.
How?
The client provided us with an Excel document with two worksheets. The first one had sample records in it that needed to be converted to PDF. The second worksheet had a template designed by the customer in the format they wanted to see the final report in.
To get the job done, Josh wrote an Excel macro that took the data for each record, plugged it into the template, and then exported the template worksheet to PDF. Luckily, VBA has a method for exporting worksheets to PDFs.
The one issue that we ran into was that the PDF documents were larger than the required size that the client was looking for. Space is a concern when you’re storing items in a document management system and you don’t want your database to get bloated. To make them smaller, Josh was able to wire it up to use the Adobe Acrobat printer if it was available on the user’s machine. The Adobe Acrobat printer creates PDFs in a slightly more efficient way and therefore met the space requirements.
Problems come in all shapes an sizes. I guess you could call us “problem solvers”. Contact us if you’ve got a project or problem you’d like our help tackling.
Technology used
- Excel VBA Macro
