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

alkaline3 · Jan 26th 2020

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

https://www.mrexcel.com/board/…f-with-same-name.1121933/

sobi_256 · Jan 27th 2020

i looked at the other website change

Code

'From
Workbooks.Open f.Name 
'to
Workbooks.Open Filename:=f

alkaline3 · Jan 27th 2020

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"

sobi_256 · Jan 27th 2020

remove myPath &

Code

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                Replace(f.Name, ".xlsx", ".pdf"), Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
                True

alkaline3 · Jan 27th 2020

I tried that but the files no longer save out to the location..

alkaline3 · Jan 27th 2020

Also only the folder name is included in the pdf file name, not the entire path

sobi_256 · Jan 27th 2020

if sheets are less than 6 it won't close the excel file so in else statement use

Code

Else
                MsgBox "File: " & f.Name & " has fewer than 6 sheets and will be skipped"
                Workbooks(f.Name).Close savechanges:=False

sobi_256 · Jan 27th 2020

it is saved in C:\Users\user\Documents

alkaline3 · Jan 27th 2020

Appears to still have the issue

sobi_256 · Jan 27th 2020

Code

'try this. After test in mypath put\ 
'the output pdfs names wont have test shown


Sub alkaline55()
Dim Fso As Object, Fldr, myPath As String, f As Object
Set Fso = CreateObject("Scripting.FileSystemObject")
myPath = "C:\Users\user\Downloads\test\"  'Change path to the path for the folder whose files you want to loop through
Set Fldr = Fso.getfolder(myPath).Files
'Set f = Fldr.Files

Application.ScreenUpdating = False
For Each f In Fldr
    If f.Name Like "*.xls?" Then
        'Workbooks.Open "f.Name"
        Workbooks.Open Filename:=f
        
        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:= _
                True
            
                Workbooks(f.Name).Close savechanges:=False
            Else
                MsgBox "File: " & f.Name & " has fewer than 6 sheets and will be skipped"
                Workbooks(f.Name).Close savechanges:=False
            End If
        End With
    End If
Next f
Application.ScreenUpdating = True
End Sub

Display More

alkaline3 · Jan 27th 2020

Yes that has solved it!! Thanks so much for taking a look and helping out

alkaline3 · Jan 28th 2020

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

sobi_256 · Jan 28th 2020

maybe try

Code

Application.DisplayAlerts = False

alkaline3 · Jan 28th 2020

I tried that after a bit of googling but that didn't do the trick

sobi_256 · Jan 28th 2020

hmm i didn't get this error
place it like this

Code

    Application.AskToUpdateLinks = False
    Workbooks.Open
    Application.AskToUpdateLinks = True

alkaline3 · Jan 29th 2020

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

Code

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

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

Participate now!

Share

Tags