Loop through all excel files in folder, save down certain worksheets as PDF with same name

  • Hi everyone, in the process of learning VBA and appreciate help on these great forums..

    I have many xlsx files located in a folder, I'd like to:

    Loop through each one, select the first 6 sheets, and save down as a PDF with the same name (can be in same directory or in a new folder)

    For example Lemon,Liz.xlsx has about 10 tabs (could be more added later) but I want only the first 6 (will always be located first) to be saved down as Lemon,Liz.pdf then loop to the next name.

    Thank you for any guidance.

    Also, I posted this question here as I'm in a bit of a pinch and spreading out the resources


  • Awesome! This works great! One small thing is when the file names are saved it includes the folder location in the PDF name. I tried a couple things and can't seem to make it go away..

    So "TESTLemon, Liz.pdf"

  • remove myPath &

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                    Replace(f.Name, ".xlsx", ".pdf"), Quality:=xlQualityStandard, _
                    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
  • if sheets are less than 6 it won't close the excel file so in else statement use

                    MsgBox "File: " & f.Name & " has fewer than 6 sheets and will be skipped"
                    Workbooks(f.Name).Close savechanges:=False
  • Quick follow up on this...is there a way to ignore an external links prompt when running the excel to PDF code? Each time the code looks to open the file, I'm prompted with the below screenshot. I'd like to bypass this as "Don't Update" and not see this pop as it interrupts the loop..

  • Thank you that seems to have worked :), I also added UpdateLinks:=False below for good measure..

    For Each f In Fldr
        If f.Name Like "*.xlsx" Then
            Workbooks.Open Filename:=f, UpdateLinks:=False
            With Workbooks(f.Name)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!