To convert or save each worksheet of excel file as a pdf file.
In this VBA Excel automation we are opening a excel workbook with the help of a dialog box. Then the code loops through each worksheet in the opened workbook. Then each the selected range of a sheet is saved as a .pdf file. Output pdf files will be saved at same location where the source excel file was located.This VBA code is written in .xlsm (macro) file.
1.Open the source excel file using the dialog box.
2.Store the name of the opened workbook and its location in 2 variables, they will be used for navigation and saving file.
3.Loop through each worksheet of the opened workbook.
4.We have used range A1:B10 in the code. So, we shall save only this range in the pdf file.
5.Save the newly added workbook at the same path where source excel file was saved.
Sub SplitAndSaveAsPdf() Application.ScreenUpdating = False Application.DisplayAlerts = False Dim SourceExcelFile As Variant Dim MyWorkBook As String Dim MyWorkbookPath As String Dim wSheet As Worksheet 'Open only excel files using dialog box SourceExcelFile = Application.GetOpenFilename(FileFilter:="Excel Files,*.xlsx") Workbooks.Open (SourceExcelFile) 'Storing the name of selected workbook for future use MyWorkBook = ActiveWorkbook.Name MyWorkbookPath = ActiveWorkbook.Path 'Looping through each worksheet in selected file For Each wSheet In ActiveWorkbook.Sheets 'Save each sheet as pdf file, the newly pdf file will be located where original excel file was located wSheet.Range("A1:B10").ExportAsFixedFormat Type:=xlTypePDF, filename:=MyWorkbookPath & "\" & wSheet.Name & ".pdf", Quality:=xlQualityStandard _ , IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False Next wSheet 'Close source excel file Windows(MyWorkBook).Close End Sub
We have created a command button on the macro file(.xlsm), and this command button is assigned the VBA code.
Once we click on the command button, a dialog box will be shown. We shall select a source excel workbook using this dialog box.
This is the source excel file, it has 3 worksheets. Each worksheet will be converted to a seperate pdf file.
This sample excel file , which we had selected using a dialog box, had 3 worksheets as we can see in the image below.Each of these sheets will be converted to pdf.
After the execution of the code, we can see the 3 output pdf files in the same folder where source excel file was located. These 3 pdf files have the same name as that of their source worksheet name. It means that 2018.pdf file will hold the data and name of worksheets(“2018”).
We can also save the entire workbook using similar approach.
Post you may like