Converting multiple excel files in pdf

shreeram.maroo
2StarLounger
Posts: 181
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Converting multiple excel files in pdf

Post by shreeram.maroo »

Hi,

I have a folder wherein there are 15 excel files. Every excel file has 20-30 tabs of which 10-15 tabs are to be printed - I have kept them in print preview format.

Is it possible to convert those 15 excel files into pdf at a go ? I only want to convert those 10-15 tabs of each excel that are in print preview format.

Regards
Shreeram

shreeram.maroo
2StarLounger
Posts: 181
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Re: Converting multiple excel files in pdf

Post by shreeram.maroo »

Just a clarification - I need to convert 15 excel files in 15 separate pdfs. Thanks.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Converting multiple excel files in pdf

Post by HansV »

Are those sheets the only ones in Print Preview?
Best wishes,
Hans

shreeram.maroo
2StarLounger
Posts: 181
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Re: Converting multiple excel files in pdf

Post by shreeram.maroo »

Yup, the sheets to be printed or converted into pdf are in print preview

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Converting multiple excel files in pdf

Post by HansV »

Try this. I haven't tested it myself...

Code: Select all

Sub Convert2PDF()
    ' Change as needed, keep the \ at the end
    Const strFolder = "C:\Excel\"
    Dim strFile As String
    Dim strPDF As String
    Dim wbk As Workbook
    Dim wsh As Worksheet
    Dim f As Boolean
    Dim arr() As Worksheet
    Dim i As Long
    Application.ScreenUpdating = False
    strFile = Dir(strFolder & "*.xls*")
    Do While strFile <> ""
        Set wbk = Workbooks.Open(Filename:=strFolder & strFile, UpdateLinks:=False)
        i = 0
        For Each wsh In wbk.Worksheets
            wsh.Select
            If wbk.Windows(1).View = xlPageLayoutView Then
                i = i + 1
                ReDim Preserve arr(1 To i)
                Set arr(i) = wsh
            End If
        Next wsh
        f = True
        For i = 1 To UBound(arr)
            arr(i).Select Replace:=f
            f = False
        Next i
        i = InStrRev(strFile, ".")
        strPDF = Left(strFile, i) & "pdf"
        Set wsh = ActiveSheet
        wsh.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strPDF
        wbk.Close SaveChanges:=False
        strFile = Dir
    Loop
    Application.ScreenUpdating = True
End Sub
Best wishes,
Hans

shreeram.maroo
2StarLounger
Posts: 181
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Re: Converting multiple excel files in pdf

Post by shreeram.maroo »

shows error

strFile = Dir(strFolder & "*.xls*") is highlighted while debugging

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Converting multiple excel files in pdf

Post by HansV »

What is the error message?
Best wishes,
Hans

shreeram.maroo
2StarLounger
Posts: 181
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Re: Converting multiple excel files in pdf

Post by shreeram.maroo »

Sorry.. it is showing error at wsh.select

I have attached the screenshot
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Converting multiple excel files in pdf

Post by HansV »

Do the workbooks contain hidden worksheets?
Best wishes,
Hans

shreeram.maroo
2StarLounger
Posts: 181
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Re: Converting multiple excel files in pdf

Post by shreeram.maroo »

Yes they do have

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Converting multiple excel files in pdf

Post by HansV »

See if this works better:

Code: Select all

Sub Convert2PDF()
    ' Change as needed, keep the \ at the end
    Const strFolder = "C:\Excel\"
    Dim strFile As String
    Dim strPDF As String
    Dim wbk As Workbook
    Dim wsh As Worksheet
    Dim f As Boolean
    Dim arr() As Worksheet
    Dim i As Long
    Application.ScreenUpdating = False
    strFile = Dir(strFolder & "*.xls*")
    Do While strFile <> ""
        Set wbk = Workbooks.Open(Filename:=strFolder & strFile, UpdateLinks:=False)
        i = 0
        For Each wsh In wbk.Worksheets
            If wsh.Visible = xlSheetVisible Then
                wsh.Select
                If wbk.Windows(1).View = xlPageLayoutView Then
                    i = i + 1
                    ReDim Preserve arr(1 To i)
                    Set arr(i) = wsh
                End If
            End If
        Next wsh
        f = True
        For i = 1 To UBound(arr)
            arr(i).Select Replace:=f
            f = False
        Next i
        i = InStrRev(strFile, ".")
        strPDF = Left(strFile, i) & "pdf"
        Set wsh = ActiveSheet
        wsh.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strPDF
        wbk.Close SaveChanges:=False
        strFile = Dir
    Loop
    Application.ScreenUpdating = True
End Sub
Best wishes,
Hans

shreeram.maroo
2StarLounger
Posts: 181
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Re: Converting multiple excel files in pdf

Post by shreeram.maroo »

Nope.. getting error still.
You do not have the required permissions to view the files attached to this post.

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Converting multiple excel files in pdf

Post by YasserKhalil »

Can you attach samples of the files?

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Converting multiple excel files in pdf

Post by HansV »

The error message means that none of the visible sheets were in Page Layout view.
Is there another way to determine which sheets should be exported?
Best wishes,
Hans

shreeram.maroo
2StarLounger
Posts: 181
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Re: Converting multiple excel files in pdf

Post by shreeram.maroo »

No, I have specifically set those sheets in print layout and that's the only way to determine the sheets to be exported. I have attached few sample files where I am trying to run this macro
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Converting multiple excel files in pdf

Post by HansV »

Ah - the sheets you want are not in Page Layout view but in Page Break Preview.
Change xlPageLayoutView in the code to xlPageBreakPreview.
Also, change the line

Code: Select all

        wsh.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strPDF
to

Code: Select all

        wsh.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFolder & strPDF
if you want to save the PDF files in the same folder as the workbooks.
Best wishes,
Hans

shreeram.maroo
2StarLounger
Posts: 181
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Re: Converting multiple excel files in pdf

Post by shreeram.maroo »

Oops.. sorry for the confusion. I have tried it - works perfect.
Thanks a ton Hans, this saves lot of time and efforts. Much appreciated.

Maryrichman
NewLounger
Posts: 10
Joined: 15 Sep 2020, 14:49

Re: Converting multiple excel files in pdf

Post by Maryrichman »

Hello,
Does anyone know if I can use the above for converting multiple tabs (Hundreds) within my excel file Into individual pdf documents please, these are customers’ letters. The name of the pdf doc must be the individual tab’s name. They all have The same layout so could get away with A1:E54 Range as long as it is all within this range on one pdf page. Thank you all

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Converting multiple excel files in pdf

Post by HansV »

Welcome to Eileen's Lounge!

Yes, that would be possible. Do you want to export ALL worksheets, or are there sheets that you want to exclude?
Best wishes,
Hans

Maryrichman
NewLounger
Posts: 10
Joined: 15 Sep 2020, 14:49

Re: Converting multiple excel files in pdf

Post by Maryrichman »

Thank you for the warm welcome :)

There are sheets (pivot tables, lists etc) that ideally I would like to exclude please.
Many thanks 😊