Converting multiple excel files in pdf

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

Re: Converting multiple excel files in pdf

Post by HansV »

Here is a macro. Edit the list of exceptions before running it.

Code: Select all

Sub Export2PDF()
    Dim strPath As String
    Dim wsh As Worksheet
    Application.Cursor = xlWait
    ' Get the path of the current workbook
    strPath = ActiveWorkbook.Path & Application.PathSeparator
    ' Loop through the sheets
    For Each wsh In Worksheets
        Select Case wsh.Name
            Case "Pivot1", "Pivot2", "List1", "List2"
                ' Skip these sheets; edit as needed
            Case Else
                ' Show progress in status bar
                Application.StatusBar = "Exporting " & wsh.Name
                ' Export A1:E54 to PDF
                wsh.Range("A1:E54").ExportAsFixedFormat _
                    Type:=xlTypePDF, Filename:=strPath & wsh.Name & ".pdf"
        End Select
    Next wsh
    ' Reset the status bar
    Application.StatusBar = False
    Application.Cursor = xlDefault
End Sub
Regards,
Hans

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

Re: Converting multiple excel files in pdf

Post by Maryrichman »

Hi Hans, thank you for your prompt response. There is a bug with the below part, all yellow. I changed the range to C3:K60 To include only the clean data.
wsh.Range("A1:E54").ExportAsFixedFormat _
Type:=xlTypePDF, Filename:=strPath & wsh.Name & ".pdf"

The excel I am working with is on a remote session, parallels client.

Many thanks
M

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

Re: Converting multiple excel files in pdf

Post by HansV »

I set strPath to the path of the workbook. Does it help if you set strPath to a local folder, e.g.

Code: Select all

    strPath = "C:\Excel\"
where C:\Excel is an existing folder on your hard disk?

Or could the names of one or more worksheets contain characters that are not allowed in file names, such as < or > or |
Regards,
Hans

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

Re: Converting multiple excel files in pdf

Post by Maryrichman »

Hello Hans,
As predicted it must have been the remote session and IT permissions... I opened the spreadsheet via local drive and not Parallels and the above works well. Thank you for your help.
I encountered some problems and just I case someone in the future reads this I also found a workaround. When this macro is run with the range as per above, It seems to ignore the cell range if the page breaks are not set the same for all tabs. If your page break preview is all over the place like mine was, select all sheets to be pdf’d, Ctrl+c individually or ctrl+shift if you have loads. Highlight cell range to be included in pdf. Pretend to want to print them and set to print selection and fit sheet on one page. Then run macro.

Works a treat 😊.
Thank you Hans!
M

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

Re: Converting multiple excel files in pdf

Post by HansV »

Glad that you were able to solve it!
Regards,
Hans

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

Re: Converting multiple excel files in pdf

Post by Maryrichman »

Yes thanks to you👍