Converting multiple excel files in pdf
-
- 2StarLounger
- Posts: 181
- Joined: 19 Feb 2016, 16:54
- Location: Veraval, India
Converting multiple excel files in pdf
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
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
-
- 2StarLounger
- Posts: 181
- Joined: 19 Feb 2016, 16:54
- Location: Veraval, India
Re: Converting multiple excel files in pdf
Just a clarification - I need to convert 15 excel files in 15 separate pdfs. Thanks.
-
- Administrator
- Posts: 78583
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Converting multiple excel files in pdf
Are those sheets the only ones in Print Preview?
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 181
- Joined: 19 Feb 2016, 16:54
- Location: Veraval, India
Re: Converting multiple excel files in pdf
Yup, the sheets to be printed or converted into pdf are in print preview
-
- Administrator
- Posts: 78583
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Converting multiple excel files in pdf
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
Hans
-
- 2StarLounger
- Posts: 181
- Joined: 19 Feb 2016, 16:54
- Location: Veraval, India
Re: Converting multiple excel files in pdf
shows error
strFile = Dir(strFolder & "*.xls*") is highlighted while debugging
strFile = Dir(strFolder & "*.xls*") is highlighted while debugging
-
- Administrator
- Posts: 78583
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 2StarLounger
- Posts: 181
- Joined: 19 Feb 2016, 16:54
- Location: Veraval, India
Re: Converting multiple excel files in pdf
Sorry.. it is showing error at wsh.select
I have attached the screenshot
I have attached the screenshot
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78583
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Converting multiple excel files in pdf
Do the workbooks contain hidden worksheets?
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 181
- Joined: 19 Feb 2016, 16:54
- Location: Veraval, India
Re: Converting multiple excel files in pdf
Yes they do have
-
- Administrator
- Posts: 78583
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Converting multiple excel files in pdf
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
Hans
-
- 2StarLounger
- Posts: 181
- Joined: 19 Feb 2016, 16:54
- Location: Veraval, India
Re: Converting multiple excel files in pdf
Nope.. getting error still.
You do not have the required permissions to view the files attached to this post.
-
- PlatinumLounger
- Posts: 4931
- Joined: 31 Aug 2016, 09:02
Re: Converting multiple excel files in pdf
Can you attach samples of the files?
-
- Administrator
- Posts: 78583
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Converting multiple excel files in pdf
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?
Is there another way to determine which sheets should be exported?
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 181
- Joined: 19 Feb 2016, 16:54
- Location: Veraval, India
Re: Converting multiple excel files in pdf
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.
-
- Administrator
- Posts: 78583
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Converting multiple excel files in pdf
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
to
if you want to save the PDF files in the same folder as the workbooks.
Change xlPageLayoutView in the code to xlPageBreakPreview.
Also, change the line
Code: Select all
wsh.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strPDF
Code: Select all
wsh.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFolder & strPDF
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 181
- Joined: 19 Feb 2016, 16:54
- Location: Veraval, India
Re: Converting multiple excel files in pdf
Oops.. sorry for the confusion. I have tried it - works perfect.
Thanks a ton Hans, this saves lot of time and efforts. Much appreciated.
Thanks a ton Hans, this saves lot of time and efforts. Much appreciated.
-
- NewLounger
- Posts: 10
- Joined: 15 Sep 2020, 14:49
Re: Converting multiple excel files in pdf
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
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
-
- Administrator
- Posts: 78583
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Converting multiple excel files in pdf
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?
Yes, that would be possible. Do you want to export ALL worksheets, or are there sheets that you want to exclude?
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 10
- Joined: 15 Sep 2020, 14:49
Re: Converting multiple excel files in pdf
Thank you for the warm welcome :)
There are sheets (pivot tables, lists etc) that ideally I would like to exclude please.
Many thanks
There are sheets (pivot tables, lists etc) that ideally I would like to exclude please.
Many thanks