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!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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.
 
Upvote 0
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
 
Upvote 0
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: 14
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top