To save an excel file as pdf using VBA excel.
In this VBA excel automation we are opening a excel workbook by using a dialog box. Then selected workbook is saved as a pdf file by this VBA code. Name of the output pdf file will be same as source excel workbook.
- Open the source excel file using the dialog box.
- Store the name of the opened workbook and its location in 2 variables, they will be used for navigation and saving file.
- Save the workbook as a pdf file.
- Output pdf file will be saved at same location where source excel file is located.
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 = Replace(ActiveWorkbook.Name, ".xlsx", "") MyWorkbookPath = ActiveWorkbook.Path 'Looping through each worksheet in selected file 'Save each sheet as pdf file, the newly pdf file will be located where original excel file was located Workbooks(MyWorkBook).ExportAsFixedFormat Type:=xlTypePDF, filename:=MyWorkbookPath & "\" & MyWorkBook & ".pdf", Quality:=xlQualityStandard _ , IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False '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, which we shall use.
After the execution of the code, we can see the 1 output pdf files in created in the same folder where source excel file was placed. This output pdf file has the same name as that of their source workbook’s name. It means that 3_Years_Data.pdf file will hold the data and name of 3_Years_Data.xlsx.
Here data in each worksheet in the source workbook, will be pasted to a separate page in pdf file.
By following this approach, we can dynamically create a separate pdf file for multiple workbooks as well.
Post you may like