• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro to PDF Worksheets based on Tab name

J

jholbrook

New Member
Hi,


I have 100 workbooks with 10 or so tabs in each, we have to save certain worksheets as pdf's to send out. I'm looking for a macro to PDF worksheets based on tab name. I'd like to not have to do this individually in each workbook as that would still be time consuming to open each workbook to run the macro. All of the workbooks are located in the same file path and can be saved there with the same name as the excel workbook.


Your help is much appreciated.


Thank you,
 
Hi, jholbrook!


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.

http://chandoo.org/forums/topic/command-button-code-and-code-to-saveconvert-the-active-sheet-to-pdf

http://chandoo.org/wp/2009/06/04/excel-to-pdf/

http://chandoo.org/forums/topic/make-a-part-of-a-worksheet-to-pdf

... and the list goes on.


Regards!
 
I've searched through the website and I can't quite find what I'm looking for.


http://chandoo.org/forums/topic/command-button-code-and-code-to-saveconvert-the-active-sheet-to-pdf


This link would work great if it wasn't for only one workbook, but I need to do this for 100 workbooks. Honestly, it wouldn't save too much time if I have to open each workbook (I could just select the sheets and save as PDF).


The vision I have for this macro is to PDF multiple workbooks from one single workbook. The file names and tab names are always the same but we need to do this weekly and is time consuming to open each workbook.


Any help is much appreciated and I have no idea where to even start with the code.
 
Hi, jholbrook!


Try with this code:

-----

[pre]
Code: 
Option Explicit

Sub IAmSoLazyThatIRefuseToAltF8AndRunTheMacro()
' constants
Const ksDir = "x:...path"
Const ksExt = "*.xls*"
' declarations
Dim I As Integer, A As String
' start
Application.EnableEvents = False
' process
A = Dir(ksDir & ksExt)
Do Until A = ""
Workbooks.Open ksDir & A
With ActiveWorkbook
For I = 1 To .Worksheets.Count
.Worksheets(I).ExportAsFixedFormat xlTypePDF, _
ksDir & A & "_" & .Worksheets(I).Name, xlQualityStandard, True, True
Next I
.Close False
End With
A = Dir
Loop
' end
Application.EnableEvents = False
Beep
End Sub
[/pre]
-----


Regards!
 
Thanks for the reply SirJB7. Good one on the macro name, got a chuckle out of me.


A few questions on the code so I can test it out(VBA Novice over here).


Consants:

Const ksDir = I'm assuming file path of where the workbooks are located goes here?

Const ksExt = Name of workbook with tab names?


Process:

A = Dir(ksDir & ksExt)


Overall, I'm confused on where I need to enter the information needed to make this code work.


Thanks for your help.
 
Good day jholbrook


You are correct in your assumptions.


Const ksDir = "x:...path" the workbooks location, put in side the quotes "DDOCUMENTSEXCEL"

Const ksExt = "*.xls*" the name of the work book, put inside the quotes "MYWORKBOOK.XLS" UPPER CASE NOT REQUIRED enter as named
 
Hi, jholbrook!

Follow b(ut)ob(ut)hc guidelines and let us know if you succeed.

Regards!


@b(ut)ob(ut)hc

Hi!

Thanks for the support.

Regards!
 
Code isn't working for me...Its not giving me an error but it doesn't appear to be working at all.


I feel like something is missing. Should the workbook contain the file names or the tab names I want PDF'd?


Here is the code:


Sub PDFMMR()

' constants

Const ksDir = "C:personalExcelPDF"

Const ksExt = "PDFALLTHETHINGS.xls"

' declarations

Dim I As Integer, A As String

' start

Application.EnableEvents = False

' process

A = Dir(ksDir & ksExt)

Do Until A = ""

Workbooks.Open ksDir & A

With ActiveWorkbook

For I = 1 To .Worksheets.Count

.Worksheets(I).ExportAsFixedFormat xlTypePDF, _

ksDir & A & "_" & .Worksheets(I).Name, xlQualityStandard, True, True

Next I

.Close False

End With

A = Dir

Loop

' end

Application.EnableEvents = False

Beep

End Sub
 
Hi, jholbrook!


It works fine for me, just give a look at these files:

https://dl.dropboxusercontent.com/u/60558749/Macro%20to%20PDF%20Worksheets%20based%20on%20Tab%20name%20%28for%20jholbrook%20at%20chandoo.org%29.xlsm

https://dl.dropboxusercontent.com/u/60558749/Z_PDF.rar


The first one is an Excel workbook with a little command button that calls a macro with the exact code as in my previous post, exception made for the value of the constant with the folder: C:Z_PDF


The second one is a compressed file with that folder, C:Z_PDF, which contains:

- 5 workbooks (with 3, 4, 5, 6, & 7 worksheets respectively, and cell A1 of each sheet has a letter starting at "a" and ending at "y")

- 1 compressed file (with the 25 PDF files created by the macro, so as you could check them after running it)


Procedure:

1) Extract folder Z_PDF from compressed file on drive C:

2) Open workbook and press button.


If that doesn't help you, well, I can't do any more from here.


Regards!
 
Hi, jholbrook!

It's a simple compressed file using WinRar program (freely downloadable and you could also use another compressor programs like WinZip, 7-zip,...). I checked it from 2 different computers and it works fine. Please try again from that or another computer and if don't succeed let me know and I'd post the files individually.

Regards!
 
Hi SirJB7!


I was able to extract the files and code works. I did notice it would just PDF each tab and seperate them individually. Although, I can see this code being very useful in the future it isn't exactly what I need at the moment.


I need the pdf's to be combined into one PDF and I only need a few tabs from a workbook not all of them. The tabs should be named the same if that helps. Is there a way to accomplish this?


Thanks!
 
Hi, jholbrook!


With the information available at the topic title "Macro to PDF Worksheets based on Tab name" and this quoted from your first post "I'm looking for a macro to PDF worksheets based on tab name", if the PDF shouldn't have the tab name, how would you name them?


This code saves worksheets by pairs in PDF format, you should only have to adjust the procedure to properly fill array V:

-----

[pre]
Code: 
Option Explicit

Sub IAmSoLazyThatIRefuseToAltF8AndRunTheMacro()
' constants
Const ksDir = "x:...path"
Const ksExt = "*.xls*"
' declarations
Dim I As Integer, A As String, V As Variant
' start
Application.EnableEvents = False
' process
A = Dir(ksDir & ksExt)
Do Until A = ""
Workbooks.Open ksDir & A
With ActiveWorkbook
For I = 1 To .Worksheets.Count - (.Worksheets.Count Mod 2) Step 2
V = Array(.Worksheets(I).Name, .Worksheets(I + 1).Name)
.Worksheets(V).Select
Selection.ExportAsFixedFormat xlTypePDF, _
ksDir & A & "_" & .Worksheets(I).Name, xlQualityStandard, True, True
Next I
.Close False
End With
A = Dir
Loop
' end
Application.EnableEvents = False
Beep
End Sub
[/pre]
-----


Regards!
 
I would name the pdf the same as the excel workbook. What I meant by pdf based on tab name is to pdf certain tabs together based on what they are named. Sorry for the confusion.


I'll try out the code in a few. Thank you.
 
You must log in or register to reply here.
Back
Top