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

alkaline55

New Member
Joined
Jan 24, 2020
Messages
28
Office Version
  1. 2016
Platform
  1. Windows
Hi everyone, I'm still new to VBA and this is an interesting problem :)

Like the title says, I have many xlsx files located in a folder, I'd like to:
Loop through each one, select the first 6 sheets, and save as a PDF with the same name

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..

Any guidance is much appreciated!
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,719
Office Version
  1. 365
Platform
  1. Windows
Cross posted Loop through all excel files in folder, save down certain worksheets as PDF with same name - OzGrid Free Excel/VBA Help Forum

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,358
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Untested, but try this after you enter the correct path to your folder for the variable myPath:
VBA Code:
Sub alkaline55()
Dim Fso As Object, Fldr, myPath As String, f As Object
Set Fso = CreateObject("Scripting.FileSystemObject")
myPath = "C:\Users\Joe\Documents\Test\"  'Change path to the path for the folder whose files you want to loop through
Set Fldr = Fso.getfolder(myPath).Files
Application.ScreenUpdating = False
For Each f In Fldr
    If f.Name Like "*.xlsx" Then
        Workbooks.Open f.Name
        With Workbooks(f.Name)
            If .Sheets.Count >= 6 Then
                For i = 1 To 6
                    .Sheets(i).Select Replace:=False
                Next i
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                myPath & Replace(f.Name, ".xlsx", ".pdf"), Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
                False
                Workbooks(f.Name).Close savechanges:=False
            Else
                MsgBox "File: " & f.Name & " has fewer than 6 sheets and will be skipped"
            End If
        End With
    End If
Next f
Application.ScreenUpdating = True
End Sub
 

alkaline55

New Member
Joined
Jan 24, 2020
Messages
28
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Thank you for your quick reply! I receive an odd run time error telling me it can't find 'Lemon,Liz.xslx' even though it is in the folder path. Image if helpful..
 

Attachments

  • Capture.PNG
    Capture.PNG
    77.6 KB · Views: 6

Watch MrExcel Video

Forum statistics

Threads
1,118,480
Messages
5,572,380
Members
412,460
Latest member
asmi_1758
Top