Loop Folder - export Workbooks as PDFs

[Guzman]

Hi !

Hi ! Maybe you can help me with this.

I'm trying to:

1. loop through a folder of excel files
2. export as PDF sheets 2 to 5 of each file
3. name each PDF with the value of cell A1 in sheet 1 of each file

I have this:

Sub export_PDF_Test()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

' Path
Folder = ThisWorkbook.Path & “\”
File = Dir(ruta & “*.xlsx”)
PathFile = ruta & archivo

' Loop through folder
Do While File <>  ""

  Set l2 = Workbooks.Open(PathFile)
  l2.Sheets(Array(2, 3, 4, 5)).Select

   ' export as PDF each file, sheets (2 to 5)
  *** code: to export as PDF (exportasfixedformat) ***

  l2.Close True
  File = Dir()
Loop

End Sub

Any idea?
THANKS.

 

[Chihiro]

Something like below.

Sub export_PDF_Test()
Dim path As String
Dim Fname As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

' Path
path = ThisWorkbook.path & "\"
Fname = "*.xlsx"
pathfile = Dir(path & Fname)

' Loop through folder
Do While pathfile <> ""

  Set l2 = Workbooks.Open(pathfile)
  l2.Sheets(Array(2, 3, 4, 5)).Select

  ' export as PDF each file, sheets (2 to 5)
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        Folder & l2.Sheets(1).Cells(1, 1).Value & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenafterPublish:=False

  l2.Close True
  pathfile = Dir
Loop

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[Guzman]

Hello!

Something is happening that doesn't work.
I get an 1004 error message when:

Set l2 = Workbooks.Open(pathfile)

What do you suggest?
Thanks.

 

[Chihiro]

Do you have the workbook with macro and other workbooks in same folder? If not, you'll need to edit "path=" line.

 

[Guzman]

Yes I do.

Don't get why I have that error, because when I was trying to code what I wanted, I arrived until here and that part was ok.
The problem started when generating the PDF.

Sub export_PDF_Test()

'  Thisworkbook.name = "Export PDF"

Application.ScreenUpdating = True
Application.DisplayAlerts = True

ruta = ThisWorkbook.Path & "\"
archivo = Dir(ruta & "*.xlsx")
RutaCompleta = ruta & archivo

Do While archivo <> ""

    Set l2 = Workbooks.Open(RutaCompleta)
    l2.Activate
          
    l2.Sheets(Array(2, 3, 4, 5)).Select
    ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=ruta & archivo

    l2.Close True
    Debug.Print archivo
    archivo = Dir()
Loop

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

The problem is when generating the PDF, instead of doing each file, it makes thisworkbook. It makes a PDF of the workbook sheet of thisworkbook ("Export PDF").

l2.Sheets(Array(2, 3, 4, 5)).Select
    ActiveSheet.ExportAsFixedFormat

I Think this is what I need to change, but I don't know how.

Any ideas?
Thanks

 

[Chihiro]

There was minor error in my code. Instead of saving the file in same folder as the workbook. It would save in default save location (in my case it was same as the test folder). Amended code as below.

I had no issue with your code. Generated PDF in same folder as workbooks.

Sub export_PDF_Test()
Dim path As String
Dim Fname As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

' Path
path = ThisWorkbook.path & "\"
Fname = "*.xlsx"
pathfile = Dir(path & Fname)

' Loop through folder
Do While pathfile <> ""

  Set l2 = Workbooks.Open(path & pathfile)
  l2.Sheets(Array(2, 3, 4, 5)).Select

  ' export as PDF each file, sheets (2 to 5)
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        path & l2.Sheets(1).Cells(1, 1).Value & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenafterPublish:=False

  l2.Close True
  pathfile = Dir
Loop

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[Guzman]

Hi !! Sorry that I replied late.

Yes, silly me !!


I was coding in Thisworkbook module and was always exporting thisworkbook.activesheet instead of each file.

I've added a new module and the script works perfectly!

Sub Export_as_PDF()

Application.ScreenUpdating = True
Application.DisplayAlerts = True

ruta = ThisWorkbook.Path & "\"
archivo = Dir(ruta & "*.xlsx")
RutaCompleta = ruta & archivo

Do While archivo <> ""

    Set l2 = Workbooks.Open(RutaCompleta)
    l2.Activate
          
    l2.Sheets(Array(2, 3, 4, 5)).Select
    ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=ruta & archivo

    l2.Close True
    Debug.Print archivo & " archivo"
    archivo = Dir()
Loop

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

Probably this code should be adjusted so the sheets to export is not a fixed number, but a variable.

Thanks for the help!