In a recent project undertaken by Datanology, a workbook had become large enough to be cumbersome in opening, saving, closing and general navigation. Over 1,500 worksheets of generated invoices were stored in the workbook and this was growing at a rate of around 10 per day.
A solution was required to deal with this growth and the slow process of navigating through the spreadsheet, but the invoices needed to be stored for many years as well as being indexed and easily locatable. The overall solution was to implement a new code in the VBA that generated the new worksheet as an invoice within the system and instead create a PDF file that was output to a folder so the workbook didn’t need to store it and therefore the growth issue would have been addressed.
As it was necessary to export all the current invoice worksheets, the code below was used to export ALL worksheets in the current workbook to a single folder, named the same as the worksheet name but as a PDF with .pdf extension. Whilst there may have been a few sheets we didn’t need exporting, it was deemed far more efficient to export all the sheets and then delete the files that were not required from the folder.
The code below only sook a few moments to run, and following the process all 1,500+ invoices were stored as their own PDF in the folder, allowing us to then delete the PDFs that were not invoices and not required, and also remove the tabs from the workbook bringing it down in size, making it more manageable and far more user friendly. The next stage of ensuring each new invoice is created as it’s own PDF and not stored in the workbook will use a very slightly adapted version of the code below, but none-the-less it will work in the same way.
' Change C:\PDFS OUT\ 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:\PDFS OUT\" & nm & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _