Excel VBA to go to a folder and save all Excel Docs as seperare PDF files

Question

ive been working on some code for a while but cannot seem to get it to work.. think im nowhere near if im honest..

I want to program a button into an excel doc to go to a folder i.e. D:\Work\

which has lots of excel spreadsheets in, and save everyone one as a separate PDF doc?

thanks in advance

Answer 1

The code below Loop through all files and then save all worksheets with in a workbook as PDF. I have commented the code to help you understand it.

Option Explicit

Sub Loop_Dir_for_Excel_Workbooks()
    Dim strWorkbook As String
    Dim wbktoExport As Workbook
    Dim strSourceExcelLocation As String

    strSourceExcelLocation = "D:\Work\XLS\"

    'Search all Excel files in the directory with .xls, .xlsx, xlsm extensions
    strWorkbook = Dir(strSourceExcelLocation & "*.xls*")

    Do While Len(strWorkbook) > 0
        'Open the workbook
        wbktoExport = Workbooks.Open(strWorkbook)

        'Export all sheets as single PDF
        Call Export_Excel_as_PDF(wbktoExport)

        'Get next workbook
        strWorkbook = Dir

        'Close Excel workbook without making changes
        wbktoExport.Close False
    Loop
End Sub

Sub Export_Excel_as_PDF(ByRef wbk As Workbook)
    Dim strTargetPDFLocation As String

    strTargetPDFLocation = "D:\Work\PDF\"
    'Select all worksheets in the opened workbook
    wbk.Sheets.Select

    'Activate first worksheet
    wbk.Sheets(1).Activate

    'Export as PDF
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        strTargetPDFLocation & wbk.Name & ".pdf" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False

End Sub