Datanology recently completed a project where the clients Excel workbook was used to auto create and store invoices as a new tab in the workbook, with the tab name determined by the invoice number. As the business grew, so did the number of invoices that were stored in the workbook which made it quite difficult to navigate to them to view, amend or just confirm the invoice was recorded. The workbook was also beginning to increase in size, slowing down the opening and saving of the document. It also caused a few nerves regarding safety of the data in the event of accidental deletion or corruption.
Whilst datanology suggested 2 solutions, it was considered more important to create individual PDFs and XLS files of these tabs of invoices (over 2,500) to start with. A manual process to do this would take several weeks of constant copying, moving, save as etc to create 2 different files – one XLS and one PDF. Therefore a script was written in VBA that would deal with this process and whilst it may take an hour or 2 to complete, the process is entirely automated so a spare desktop PC for instance could carry out this task outside of business hours.
The example code below is designed to take the activeworkbook, and create a PDF of each and every worksheet with the filename taken from the tab name – ie the invoice number. If you have a similar requirement, feel free to use the code below, or if you need any assistance, contact us through the form below or leave a comment / message.
' PDF Export Macro
' Change C:\Exports\ to your folder path where you need the files saved
' Save Each Worksheet to a separate PDF file.
Dim ws As Worksheet
For Each ws In Worksheets
nm = ws.Name
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="C:\Exports\" & nm & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _