Create a PDF file for each Excel Worksheet
| Sometimes it’s nececessary to generate a separate PDF file for each worksheet within an Excel workbook. This can be a big waste of time; especially if there are many sheets and/or you have to regenerate the PDFs every time the content changes!\r\n\r\nIf you are using Microsoft Office 2007 or later, the following VBA macro automates the process. The Fname variable can be used to customize the PDF file names that are generated. \r\n\r\n Option Explicit\r\n \r\n Sub createPDFfiles()\r\n Dim ws As Worksheet\r\n Dim Fname As String\r\n For Each ws In ActiveWorkbook.Worksheets\r\n On Error Resume Next ‘Continue if an error occurs\r\n\r\n ‘ Name PDF files based on the worksheet Index (e.g Annex 1.1.1, Annex 1.1.2, etc.)\r\n Fname = “Annex 1.1.” & ws.Index & “_result”\r\n\r\n ‘ If you want to name the PDF files differently just change the Fname variable above to\r\n ‘ whatever you like. For example if you changed Fname to:\r\n ‘\r\n ‘ Fname = “C:\myFolder\pdfs\” & ActiveWorkbook.Name & “-“ & ws.Name\r\n ‘\r\n ‘ The files would be stored in C:\myFolder\pdfs, and named using the\r\n ‘ spreadsheet file name and the worksheet name.\r\n ‘ \r\n ‘ WARNING: Using worksheet names may cause errors if the names contain characters that Windows\r\n ‘ does not accept in file names. See below for a list of characters that you need to avoid.\r\n ‘\r\n ws.ExportAsFixedFormat _\r\n Type:=xlTypePDF, _\r\n Filename:=Fname, _\r\n Quality:=xlQualityStandard, _\r\n IncludeDocProperties:=True, _\r\n IgnorePrintAreas:=False\r\n Next ws\r\n End Sub\r\n\r\nThe following characters are not allowed in Windows file names, they will cause the macro to fail if you use them:\r\n\r\n < (less than)\r\n > (greater than)\r\n : (colon)\r\n ” (double quote)\r\n / (forward slash)\r\n \ (backslash)\r\n | (vertical bar or pipe)\r\n ? (question mark)\r\n * (asterisk) |
Written on April 26, 2011