Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

PDFCreator - Problem Printing Multiple Sheets to One PDF file

Windows XP SP2
Excel 2007 SP2
PDFCreator 0.9.3

Hi

I have been using the Ken Puls' (ExcelGuru) code for creating a single PDF file from multiple worksheets found here:
http://www.excelguru.ca/node/21#MultiSingle

SUMMARY - Ken's code assumes that jobs added sequentially to the PDFCreator job queue are cumulative, however my problem is that old jobs drop out of the queue before new jobs are added.

I have posted a problematic excerpt of the code and the output.
'........
    Dim i As Integer
    Dim TimerStart As Long
    TimerStart = Timer
    
    Dim oldPrintCount As Integer
    oldPrintCount = pdfjob.cCountOfPrintjobs
    
    Debug.Print
    Debug.Print "lTtlSheets: " & lTtlSheets
    Debug.Print "Initial Printjob Count: " & oldPrintCount & " at " & Timer
    
    If oldPrintCount > 0 Then
                    
        For i = 1 To pdfjob.cCountOfPrintjobs
        
            Debug.Print pdfjob.cPrintjobFilename(i)
            
        Next i
    
    End If

    Debug.Print
    
    'Wait until all print jobs have entered the print queue
    Do Until pdfjob.cCountOfPrintjobs = lTtlSheets _
        Or (Timer - TimerStart > TimerInterval2 And pdfjob.cCountOfPrintjobs = 0)
        
        If oldPrintCount <> pdfjob.cCountOfPrintjobs Then
            Debug.Print "Previous Printjob Count: " & oldPrintCount
            Debug.Print "Current Printjob Count: " & pdfjob.cCountOfPrintjobs & " at " & Timer
            Debug.Print
            oldPrintCount = pdfjob.cCountOfPrintjobs
            
            For i = 1 To pdfjob.cCountOfPrintjobs
            
                Debug.Print pdfjob.cPrintjobFilename(i)
                
            Next i
            
        End If
        
        DoEvents
        
    Loop
'........
So - this code executes once all the required worksheets (3 of them) have had the PrintOut method invoked. I've added some debugging code to illustrate my problem.

Output:
lTtlSheets: 3
Initial Printjob Count: 1 at 45197.67
C:Documents and Settingspootle_flumpLocal SettingsTempPDFCreatorPDFCreatorSpool~PS11C.tmp

Previous Printjob Count: 1
Current Printjob Count: 0 at 45197.94

Previous Printjob Count: 0
Current Printjob Count: 1 at 45198.44
C:Documents and Settingspootle_flumpLocal SettingsTempPDFCreatorPDFCreatorSpool~PS121.tmp

Previous Printjob Count: 1
Current Printjob Count: 0 at 45199.14

Previous Printjob Count: 0
Current Printjob Count: 1 at 45199.64
C:Documents and Settingspootle_flumpLocal SettingsTempPDFCreatorPDFCreatorSpool~PS126.tmp

Previous Printjob Count: 1
Current Printjob Count: 0 at 45200.41
As such, jobs appear to be getting added to the queue but then being processed before the rest of jobs are being added. The result is that the PDF created only contains one of the worksheets. I added the check against the Timer as part of the loop condition because my code was looping forever.

This is an intermittent problem - when I ran this the first couple of times getting the stuff together for this post it ran perfectly!

Any help would be gratefully received.

Thanks


Post your answer or comment

comments powered by Disqus
Hi All,

I have the below code which uses pdfcreator and prints multiple sheets into one pdf.

' Print a Single Worksheet to a PDF File:
Option Explicit

Sub PrintToPDF_Early()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Print to PDF file using PDFCreator
'   (Download from http://sourceforge.net/projects/pdfcreator/)
'   Designed for early bind, set reference to PDFCreator

    Dim pdfjob As PDFCreator.clsPDFCreator
    Dim sPDFName As String
    Dim sPDFPath As String

    '/// Change the output file name here! ///
    sPDFName = "testPDF.pdf"
    sPDFPath = ActiveWorkbook.Path & Application.PathSeparator

    'Check if worksheet is empty and exit if so
    If IsEmpty(ActiveSheet.UsedRange) Then Exit Sub

    Set pdfjob = New PDFCreator.clsPDFCreator

    With pdfjob
        If .cStart("/NoProcessingAtStartup") = False Then
            MsgBox "Can't initialize PDFCreator.", vbCritical + _
                    vbOKOnly, "PrtPDFCreator"
            Exit Sub
        End If
        .cOption("UseAutosave") = 1
        .cOption("UseAutosaveDirectory") = 1
        .cOption("AutosaveDirectory") = sPDFPath
        .cOption("AutosaveFilename") = sPDFName
        .cOption("AutosaveFormat") = 0    ' 0 = PDF
        .cClearCache
    End With

    'Print the document to PDF
    ActiveSheet.PrintOut copies:=1, ActivePrinter:="PDFCreator"

    'Wait until the print job has entered the print queue
    Do Until pdfjob.cCountOfPrintjobs = 1
        DoEvents
    Loop
    pdfjob.cPrinterStop = False

    'Wait until PDF creator is finished then release the objects
    Do Until pdfjob.cCountOfPrintjobs = 0
        DoEvents
    Loop
    pdfjob.cClose
    Set pdfjob = Nothing
End Sub

' Print Multiple Worksheets to Multiple PDF Files:

Option Explicit

Sub PrintToPDF_MultiSheet_Early()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Print to PDF file using PDFCreator
'   (Download from http://sourceforge.net/projects/pdfcreator/)
'   Designed for early bind, set reference to PDFCreator

    Dim pdfjob As PDFCreator.clsPDFCreator
    Dim sPDFName As String
    Dim sPDFPath As String
    Dim lSheet As Long

    Set pdfjob = New PDFCreator.clsPDFCreator
    sPDFPath = ActiveWorkbook.Path & Application.PathSeparator

    If pdfjob.cStart("/NoProcessingAtStartup") = False Then
        MsgBox "Can't initialize PDFCreator.", vbCritical + _
                vbOKOnly, "PrtPDFCreator"
        Exit Sub
    End If

    For lSheet = 1 To ActiveWorkbook.Sheets.Count
        'Check if worksheet is empty and skip if so
        If Not IsEmpty(ActiveSheet.UsedRange) Then
            With pdfjob
                '/// Change the output file name here! ///
                sPDFName = "testPDF" & Sheets(lSheet).Name & ".pdf"
                .cOption("UseAutosave") = 1
                .cOption("UseAutosaveDirectory") = 1
                .cOption("AutosaveDirectory") = sPDFPath
                .cOption("AutosaveFilename") = sPDFName
                .cOption("AutosaveFormat") = 0    ' 0 = PDF
                .cClearCache
            End With
    
            'Print the document to PDF
            Worksheets(lSheet).PrintOut copies:=1, ActivePrinter:="¯PDFCreator"¯
    
            'Wait until the print job has entered the print queue
            Do Until pdfjob.cCountOfPrintjobs = 1
                DoEvents
            Loop
            pdfjob.cPrinterStop = False
    
            'Wait until PDF creator is finished then release the objects
            Do Until pdfjob.cCountOfPrintjobs = 0
                DoEvents
            Loop
        End If
    Next lSheet
    pdfjob.cClose
    Set pdfjob = Nothing
End Sub

' Print Multiple Worksheets to a Single PDF File:


Option Explicit
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub PrintToPDF_MultiSheetToOne_Early()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Print to PDF file using PDFCreator
'   (Download from http://sourceforge.net/projects/pdfcreator/)
'   Designed for early bind, set reference to PDFCreator

    Dim pdfjob As PDFCreator.clsPDFCreator
    Dim sPDFName As String
    Dim sPDFPath As String
    Dim lSheet As Long
    Dim lTtlSheets As Long

    '/// Change the output file name here! ///
    sPDFName = "Consolidated.pdf"
    sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
    Set pdfjob = New PDFCreator.clsPDFCreator

    'Make sure the PDF printer can start
    If pdfjob.cStart("/NoProcessingAtStartup") = False Then
        MsgBox "Can't initialize PDFCreator.", vbCritical + _
                vbOKOnly, "Error!"
        Exit Sub
    End If

    'Set all defaults
    With pdfjob
        .cOption("UseAutosave") = 1
        .cOption("UseAutosaveDirectory") = 1
        .cOption("AutosaveDirectory") = sPDFPath
        .cOption("AutosaveFilename") = sPDFName
        .cOption("AutosaveFormat") = 0    ' 0 = PDF
        .cClearCache
    End With

    'Print the document to PDF
    lTtlSheets = Application.Sheets.Count
    For lSheet = 1 To Application.Sheets.Count
        On Error Resume Next 'To deal with chart sheets
        If Not IsEmpty(Application.Sheets(lSheet).UsedRange) Then
            Application.Sheets(lSheet).PrintOut copies:=1, ActivePrinter:="PDFCreator"
        Else
            lTtlSheets = lTtlSheets - 1
        End If
        On Error GoTo 0
    Next lSheet

    'Wait until all print jobs have entered the print queue
    Do Until pdfjob.cCountOfPrintjobs = lTtlSheets
        DoEvents
    Loop

    'Combine all PDFs into a single file and stop the printer
    With pdfjob
        .cCombineAll
        .cPrinterStop = False
    End With

    'Wait until PDF creator is finished then release the objects
    Do Until pdfjob.cCountOfPrintjobs = 0
        DoEvents
    Loop
    MsgBox ("The PDF has been successfully created as " & sPDFName)
    pdfjob.cClose
    Sleep 1000
    Set pdfjob = Nothing
End Sub
But before printing I want to set the pagesetup for eachworksheet as below before printing.

1) Page : Legal
2) From Page 1 to 1

Thanks a lot for your help in advance.

I have code to print out selected sheets from one workbook. Have it and it works great thanks to leahProton.

I now need to save those selected mulitple sheets as a single PDF file, so I found code to do that. (opver all problem is to spool all printing s a single job)

I can get each code to work separately, but not together.

I need some help, please!

Thanks, J

Here is my code to printout the selected sheets"
Sub report()
'----------------------------------------------------------------------------------------------------
'When this procedure is run, it will:
'    select the QUOTE sheet
'    count the number of "Items"
'    lookup the item number on the summary sheet and copy the corresponding sheet name to the printlist array
'* added:
'    it will check quote sheet row 33 for an entry, if true, then add the INDIVIDUAL PRODUCT sheet
'
'    it will add the QUOTE sheet as the last sheet in the list to be printed.
'------------------------------------------------------------------------------------------------------

     Dim PrintList()                                                       'array to hold sheets to print
     Dim SheetCount As Integer
     Dim ItemCount As Integer                                              'number of non-empty cells in Item column, i.e.,
A17 thru A29
     Dim TableRange                                                        'data on SUMMARY sheet
     Dim Item                                                              'current cell in list of QUOTE "Items"
     Dim ItemList                                                          'the "Item" column itself
     Set ItemList = Range("a17:a29")
     Set TableRange = Worksheets("Summary").Range("A7:G37")
     
     Worksheets("quote").Select
     ItemCount = Application.WorksheetFunction.CountA(Range("a17:a29"))    'get the total number of items listed
     
     ReDim PrintList(1 To ItemCount)                                       'size the sheet list array to hold this many sheet
names
     
'Process the "Items" column:
'for each item, check if it has a corresponding sheet name, then,
'if so, copy the sheet name to the print list

     For Each Item In ItemList
          If Item.Value > " " Then                                         '<=== note: changed the test value
from "" (NULL) to " " (space)
               SheetCount = SheetCount + 1
               If Application.WorksheetFunction.VLookup(Item.Value, TableRange, 7) <> "" Then
                    PrintList(SheetCount) = Application.WorksheetFunction.VLookup(Item.Value, TableRange, 7)
               Else
                    SheetCount = SheetCount - 1
               End If
          Else
               Exit For
          End If
     Next Item
     
'---------------- mod ------------------------------
'The following if..then..else block will check for entries in row 33 of the quote sheet.  If there are, then it will
'add the A. INDIVIDUAL PRODUCTS sheet to the list along with the QUOTE sheet; else it just adds the QUOTE sheet.
'---------------------------------------------------

     If Sheets("quote").Range("E33").Value > "0.00" Then
          SheetCount = SheetCount + 2
          ReDim Preserve PrintList(1 To SheetCount)
          PrintList(SheetCount - 1) = "A. INDIVIDUAL PRODUCTS"
          PrintList(SheetCount) = "QUOTE"
     Else
          SheetCount = SheetCount + 1
          ReDim Preserve PrintList(1 To SheetCount)
          PrintList(SheetCount) = "quote"
     End If
     
     Sheets(PrintList()).Select                             'select all sheets in the list
     ActiveWindow.SelectedSheets.PrintOut Copies:=1         'print the sheets
     Sheets("QUOTE").Select
End Sub

AND HERE IS THE CODE TO SAVE MULTIPLE SHEETS AS A SINGLE PDF FILE

  
Option Explicit

Sub PrintToPDF_MultiSheetToOne_Late()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Print to PDF file using PDFCreator
'   (Download from http://sourceforge.net/projects/pdfcreator/)
'   Designed for late bind, no references req'd

    Dim pdfjob As Object
    Dim sPDFName As String
    Dim sPDFPath As String
    Dim lSheet As Long
    Dim lTtlSheets As Long

    '/// Change the output file name here!  ///
    sPDFName = "Consolidated.pdf"
    sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
    Set pdfjob = CreateObject("PDFCreator.clsPDFCreator")

    'Make sure the PDF printer can start
    If pdfjob.cStart("/NoProcessingAtStartup") = False Then
        MsgBox "Can't initialize PDFCreator.", vbCritical + _
            vbOKOnly, "Error!"
        Exit Sub
    End If

    'Set all defaults
    With pdfjob
        .cOption("UseAutosave") = 1
        .cOption("UseAutosaveDirectory") = 1
        .cOption("AutosaveDirectory") = sPDFPath
        .cOption("AutosaveFilename") = sPDFName
        .cOption("AutosaveFormat") = 0    ' 0 = PDF
        .cClearCache
    End With

    'Print the document to PDF
    lTtlSheets = Application.Sheets.Count
    For lSheet = 1 To Application.Sheets.Count
        On Error Resume Next 'To deal with chart sheets
        If Not IsEmpty(Application.Sheets(lSheet).UsedRange) Then
            Application.Sheets(lSheet).PrintOut copies:=1, ActivePrinter:="PDFCreator"
        Else
            lTtlSheets = lTtlSheets - 1
        End If
        On Error GoTo 0
    Next lSheet

    'Wait until all print jobs have entered the print queue
    Do Until pdfjob.cCountOfPrintjobs = lTtlSheets
        DoEvents
    Loop

    'Combine all PDFs into a single file and stop the printer
    With pdfjob
        .cCombineAll
        .cPrinterStop = False
    End With

    'Wait until PDF creator is finished then release the objects
    Do Until pdfjob.cCountOfPrintjobs = 0
        DoEvents
    Loop
    pdfjob.cClose
    Set pdfjob = Nothing
End Sub


Hi All,

I have the below macro which prints all excel sheets to pdf using pdfcreator as printer, it works absolutely fine but I want to come up with a userform where all excel sheets of a workbook will be listed down and then the user will be able to select the sheets and create the pdf as per his requirement. The Userform will do the following things for user.

1) Userform will allow user to select the sheets which he want to print to pdf.
2) It will allow him to select a option wherein he will able to print multiple sheets to one pdf.
3) If he doesn't select that option it will print each sheet to a separate pdf and save it on a selected path.

I have come up with a userform but I need a help to incorporate the below macro to the same so that they work as per above requirements.

' Print Multiple Worksheets to a Single PDF File:
Option Explicit
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub PrintToPDF_MultiSheetToOne_Early()


    Dim pdfjob As PDFCreator.clsPDFCreator
    Dim sPDFName As String
    Dim sPDFPath As String
    Dim lSheet As Long
    Dim lTtlSheets As Long

    '/// Change the output file name here! ///
    sPDFName = "Consolidated.pdf"
    sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
    Set pdfjob = New PDFCreator.clsPDFCreator

    'Make sure the PDF printer can start
    If pdfjob.cStart("/NoProcessingAtStartup") = False Then
        MsgBox "Can't initialize PDFCreator.", vbCritical + _
                vbOKOnly, "Error!"
        Exit Sub
    End If

    'Set all defaults
    With pdfjob
        .cOption("UseAutosave") = 1
        .cOption("UseAutosaveDirectory") = 1
        .cOption("AutosaveDirectory") = sPDFPath
        .cOption("AutosaveFilename") = sPDFName
        .cOption("AutosaveFormat") = 0    ' 0 = PDF
        .cClearCache
    End With

    'Print the document to PDF
    lTtlSheets = Application.Sheets.Count
    For lSheet = 1 To Application.Sheets.Count
        On Error Resume Next 'To deal with chart sheets
        If Not IsEmpty(Application.Sheets(lSheet).UsedRange) Then
            Application.Sheets(lSheet).PrintOut copies:=1, ActivePrinter:="PDFCreator"
        Else
            lTtlSheets = lTtlSheets - 1
        End If
        On Error GoTo 0
    Next lSheet

    'Wait until all print jobs have entered the print queue
    Do Until pdfjob.cCountOfPrintjobs = lTtlSheets
        DoEvents
    Loop

    'Combine all PDFs into a single file and stop the printer
    With pdfjob
        .cCombineAll
        .cPrinterStop = False
    End With

    'Wait until PDF creator is finished then release the objects
    Do Until pdfjob.cCountOfPrintjobs = 0
        DoEvents
    Loop
    MsgBox ("The PDF has been successfully created as " & sPDFName)
    pdfjob.cClose
    Sleep 1000
    Set pdfjob = Nothing
End Sub
Attached is my macro file.

Thanks a lot for your help in advance.

Hi all,

I was wondering how i would go about modifying the code i have below so that
i can print multiple sheets to a single PDF file with each worksheet printed
being a different tab. (if that last part isn't possible, then 1 pdf file
with each page being a separate sheet from the workbook.)

Note: my workbook has 15 sheets, only 1 sheet is visible at a time. the
print function will unhide/hide the sheets so that they can be printed with
the code below. (not all 15 sheets will be printed to pdf, only 4)

here is the code i am using to print to a pdf file:

If printToPDF And (pageID(i) = "Invoice" Or pageID(i) = "Withdrawls" Or
pageID(i) = "Deposits" Or pageID(i) = "Sales Journal" Or pageID(i) =
"Summary" Or pageID(i) = "Inventory Costs") Then
pb.Caption3 = "Printing" & pageID(i)
Set myPDF = New PdfDistiller
PSfilename = path & "temp.ps"
If pageID(i) = "Invoice" Then
PDFfilename = path & order & ".pdf"
Else
PDFfilename = path & pageID(i + 2) & " " & Year(Date) &
".pdf"
End If
ActiveWindow.SelectedSheets.PrintOut copies:=1,
ActivePrinter:=ChoosePrinter(pdfPrinter), preview:=False, printtofile:=True,
collate:=True, prtofilename:=PSfilename
pb.Caption3 = "Converting to PDF...."
myPDF.FileToPDF PSfilename, PDFfilename, ""

Kill PSfilename
On Error Resume Next
Kill path & "*.log"
On Error GoTo 0
Set myPDF = Nothing
End If

i have tried printing each sheet to a .ps file and then using
mypdf.filetopdf to convert it, but i haven't been able to get it to accept
multiple input names.

If you need my entire printdocuments sub, let me know and i'll paste that

tia!
J

I have print areas in 4 separate worksheets. Using Ctrl + select sheets, then
print to Adobe Acrobat results in 4 separate files. Adobe wants a new file
name at the outset of printing each area. Is there any way to print these
areas to one PDF file?

When I select and print multiple sheets within the same workbook, they appear
to be arbitrarily broken up into different files when printed rather than
being printed as one single group. My goal is to be able to print multiple
sheets into one pdf file using Acrobat distiller.

Initially I thought this could have been an adobe-specific problem.
However, when I send the document to OneNote, the job is split into the same
different parts. Also, when I print to a paper printer, I notice what
appears to be a slight pause between the different parts.

It appears to be a general Excel issue. I've tried ensuring page setup
properties are the same, adjusted sheet names, etc. but to no avail. Has
anyone had this problem and figured out how to solve it?

Hello everyone. I am trying to simplify a printing function in on one of my worksheets. It is a report that needs to be printed for multiple people. I am a beginner at VBA and the code below basically takes all the ID numbers from column "A" and pastes them into "M2" to print. What it does not do is print these pages to a PDF printer. Can anyone help me modify this code to be able to print each page to one PDF file?

Thanks for any help.

Sub Mailmerge_3()
Dim endrow As Long
If Application.Dialogs(xlDialogPrinterSetup).Show = False Then Exit Sub
Application.Dialogs(xlDialogPrinterSetup).Show
    endrow = Cells(Rows.Count, "A").End(xlUp).Row
 For i = 2 To endrow
    If Cells(i, 1).Value <> "" And Cells(i, 1).Value <> " " Then
         Range("M2").Value = Cells(i, 1).Value
         Sheets("Report").PrintOut Copies:=1, Collate:=True
   End If
 Next i
End Sub


Is there a macro written or a way to write a macro that will take certain excel sheets and combine them to create one PDF?

I have the excel add-in that allows a user to export either the entire workbook or a single sheet to a PDF file.

Greetings,

On some Excel worksheets when I try to print multiple tabs to a pdf file Adobe wants me to enter a separate file name for each page. On other Excel worksheets the multiple page pdf printing works fine.

What am I missing to be able to print the multiple tabs (pages) into a single pdf file?

Thanks,
phippsto

I have print macros that print several different items from more than one workbook all at once. It works fine when printing to paper - you go to the printer and pick it all up in order.

The problem is when I try to print it to a PDF - it prints each item to a separate file.

The code below doesn't work - the second item printed just replaces the first item printed.


	VB:
	
 PrintToPDF() 
    Dim strNewFileNameHere As String 
    strNewFileNameHere = "SomeFileName" & ".pdf" 
    ThisWorkbook.Sheets("Sheet1").PrintOut ActivePrinter:="Acrobat PDFWriter on LPT1:", PrintToFile:=True,
PrToFileName:=strNewFileNameHere 
    ThisWorkbook.Sheets("Sheet2").PrintOut ActivePrinter:="Acrobat PDFWriter on LPT1:", PrintToFile:=True,
PrToFileName:=strNewFileNameHere 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
How can I call several different print macros and get them all to print to the same pdf?

Thank you.

Hello all and thanks in advance!!

I have a text only spreadsheet that is 70 rows high and 150 columns long. I'd like to have this printed as one long sheet as a poster/plot at a print shop. I have been trying to save my sheet as a PDF to give to the print shop (which is what they have asked for), but when I save it text disappears from the cells. I am using Excel Mac 2011. I currently have my settings as 18 pt font size, portrait, fit to one page wide and one page tall, and custom paper size of 42 inches wide and 20 inches tall.

Thanks for any help!!
Stacey

Hi... just wondering if anyone can give me some advice.

Over the last couple of years, I have developed (in Excel) a 10-page (A3 size) report for my employer with many data tables and a few graphs. It makes extensive use of the camera tool (picture link), pivot tables and VLOOKUP formulas to extract data stored in various worksheets and display/format it on the master sheet. I then use VBA to update the report for approx 1200 locations, one at a time, and print the master sheet to a pdf file (using either Adobe Acrobat and/or pdfCreator) - resulting in 1200 pdf report files for distribution.

The problem is that as the report has grown in size (it started off as one A3 page), the time taken to produce the pdf files has increased to the extent that if run on one computer alone, the generation of 1200 pdf files can take 24 hours (the longer the vba code runs, the slower it seems to go). If other computers are available, I can have a few running at the same time, which reduces the overall time, but if an error or problem is discovered midway through this process, the process must be started all over again. It just seems very inefficient.

Does anyone have any experience with producing large quantities of pdf report-like files from an Excel workbook, and suggestions on how I might make this process more efficient? I've tried many times to improve the way data is stored and retrieved within the workbook and also made attempts to make more use of MS Access, but I seem to go around in circles - implementing a change, then finding it is either unworkable or makes little difference.

Dear All,

I am very new to excel programming. I need a VB Script to print multiple excel sheets into one PDF file.

Let me give you the background:

I have more than 50 Excel Workbooks each with multiple sheets which can be categorized into the groups: (Note that all the type of sheets exist in all the workbooks but the number of sheets in each group is not fixed)

Following are the categories of WorkSheets in each Excel Workbook.

Sheets with names starting with D represent Division-wise reports e.g: D001, D002, D003, D004Sheets with names starting with L represent Level-wise reports e.g: L001, L002, L003, L004Sheets with names starting with S represent Store-wise reports e.g: S001, S002, S003, S004Sheets with names starting with Z represent Zone-wise reports e.g: Z001, Z002, ZC03, ZC04Sheets with names starting with C represent Coach Zone-wise reports e.g: ZC03, ZC04Sheets with names starting with N represent New Zone-wise reports e.g: NZ001, NZ002, NZC03, ZC04
For each WorkBook, I need a macro/vbscript to print...

All sheets with names starting with D into one PDF file.All sheets with names starting with L into one PDF file.All sheets with names starting with S into one PDF file.All sheets with names starting with Z into one PDF file.All sheets with names starting with ZC into one PDF file.All sheets with names starting with N into one PDF file.This is a real urgent requirement for my company. Kindly help. I am using Excel 2007.

Hi,

I have a woorkbook (master.xls) with several worksheets. I am trying to make a macro that will print each worksheet as a separate pdf file (A pdf writer is my default printer), and name the files according to the text in cell A1.

Can someone please help me in writing suck a macro?

I found this code online but get te error message; "User-defined type not defined" in line Dimpdfjob as pdf creatonr.clsPDF creator.

Sub
PrintToPDF_Early()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Print to PDF file using PDFCreator
'   (Download from http://sourceforge.net/projects/pdfcreator/)
'   Designed for early bind, set reference to PDFCreator

    Dim pdfjob As PDFCreator.clsPDFCreator
    Dim sPDFName As String
    Dim sPDFPath As String

    '/// Change the output file name here! ///
    sPDFName = Range("A1").Value
    sPDFPath = ActiveWorkbook.Path & Application.PathSeparator

    'Check if worksheet is empty and exit if so
    If IsEmpty(ActiveSheet.UsedRange) Then Exit Sub

    Set pdfjob = New PDFCreator.clsPDFCreator

    With pdfjob
        If .cStart("/NoProcessingAtStartup") = False Then
            MsgBox "Can't initialize PDFCreator.", vbCritical + _
                    vbOKOnly, "PrtPDFCreator"
            Exit Sub
        End If
        .cOption("UseAutosave") = 1
        .cOption("UseAutosaveDirectory") = 1
        .cOption("AutosaveDirectory") = sPDFPath
        .cOption("AutosaveFilename") = sPDFName
        .cOption("AutosaveFormat") = 0    ' 0 = PDF
        .cClearCache
    End With

    'Print the document to PDF
    ActiveSheet.PrintOut copies:=1, ActivePrinter:="PDFCreator"

    'Wait until the print job has entered the print queue
    Do Until pdfjob.cCountOfPrintjobs = 1
        DoEvents
    Loop
    pdfjob.cPrinterStop = False

    'Wait until PDF creator is finished then release the objects
    Do Until pdfjob.cCountOfPrintjobs = 0
        DoEvents
    Loop
    pdfjob.cClose
    Set pdfjob = Nothing
End Sub

Gnoke

Hello all...

I am trying to print 8 sheets on one paper...

(EXAMPLE)

SHEET 1
A B C D
1XXXX XXXX XXXXX XXXX
2XXXX XXX XXXX XXXX
3XXX XXXX XXXX XXXX
4XXXX XXX XXXX XXXX
5XXXX XXXX XXXX XXXX

SHEET 2
A B C D
1XXXX XXXX XXXXX XXXX
2XXXX XXX XXXX XXXX
3XXX XXXX XXXX XXXX
4XXXX XXX XXXX XXXX
5XXXX XXXX XXXX XXXX

Because I only have like 5-6 lines per sheet i want to condense them
into one page

thanks.

T.

--
onesidered
------------------------------------------------------------------------
onesidered's Profile: http://www.excelforum.com/member.php...o&userid=25977
View this thread: http://www.excelforum.com/showthread...hreadid=394382

Hi,

I have a large spreadsheet that I would like to print 3 different sheets to
one file (on different sheets) other than the one I am in. Does anyone have
sample code that would do this.

Thanks in advance for any help.

Adella

Hello all...

I am trying to print 8 sheets on one paper...

(EXAMPLE)

SHEET 1
A B C D
1XXXX XXXX XXXXX XXXX
2XXXX XXX XXXX XXXX
3XXX XXXX XXXX XXXX
4XXXX XXX XXXX XXXX
5XXXX XXXX XXXX XXXX

SHEET 2
A B C D
1XXXX XXXX XXXXX XXXX
2XXXX XXX XXXX XXXX
3XXX XXXX XXXX XXXX
4XXXX XXX XXXX XXXX
5XXXX XXXX XXXX XXXX

Because I only have like 5-6 lines per sheet i want to condense them into one page

thanks.

T.

I need help trying to copy data from multiple sheets to one single sheet. I am pretty sure this is possible. The problem I am running into is that the number of sheets at any given time is dynamic. The numbering of the sheets is from 000 to 999 (they must be a three-digit code). The other issue I am having is I only want to copy the rows in each sheet that have an "x" in column "A". Also, the row in which the first "x" occurs can differ from sheet to sheet. I have attached a copy of the spreadsheet that indicates how the spreadsheet is layed out. Any help is appreciated.

I have a problem I could use some help with. I am trying to create a macro to print my spreadsheet to a pdf file. I tried recording the macro and the code I have posted below is what was suggested. However, when I try to open the pdf file I get an error message that says the file is incompatable or corrupted. Any suggestions on how to fix this problem? The code I am using is placed within a "do" loop. My intent is that the name of the pdf file would be taken from the value in the cell named "FileName2".

Application.ActivePrinter = "CutePDF Writer on CPW2:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"CutePDF Writer on CPW2:", PrToFileName:=Range("FileName2").Value


Using a macro, how can i convert one excel sheet to one pdf file using loops etc.

e.g. Go to sheet named "sheet1"
save this sheet as "sheet1.pdf"..

Go to "sheet2"
save this sheet as "sheet2.pdf"

etc

Hi,

I need some help with the creation of a macro in Excel.

I have in my excel-file created a drop-down list that looks up different names/codes from a list (in another sheet) and when I select a name/code a sheet gets populated. After that I push a button that prints the sheet to a PDF-file (I am using PDF995) where I have to specify the filename and location. This works well as long as I don’t need to generate a lot of different PDF’s (then it gets time consuming).

The thing I would like to do now is to create a macro that prints all the different sheets that’s gets generated from the different names/codes that I now need to select and print “manually”.

To further illustrate what I mean:

The list might look like this:
Peter
Fredrik
Jimmy
Jenny
Cecilia

When I in my input-sheet select Peter the sheet I want to print to PDF gets populated. I want the macro to go through my list from Peter to Cecilia and generate different PDF-files for each name in the list. I also would like the files to get saved with the name specified in a cell in my sheet.

Hope you understand what I mean.

Thanks for any help in advance.

Hello all,

I have an ActiveX button in a spreadsheet which when clicked by a user, will prompt them for a filename and then Excel will save a specified sheet to a PDF file of that filename.

This all works fine but then I want the filename and path to be written to a cell/range in another sheet:

Sheets("Sheet1").Select
    Range("C4").Select
    Range("C4").Value = strPDFFile
This does not seem to be working for me - I'd appreciate some advice.
(It seemed to work for me earlier but I am stumped as to what I have changed!)

I've copied out the relevant code below.
To reproduce:
Open a new spreadsheet (or use attached - you have to Enable Macros)
Create an ActiveX button called "CommandButton1" (default) on Sheet1.
Copy the code below.
Draw a rectangle or type some text in a cell on Sheet2 (if the sheet is blank, the PDF won't save)
Click the [ActiveX] button in Sheet1 to work the code.
When prompted, enter a name in the SaveAs dialog and choose a path - e.g. C:Testtest.pdf
Click on Save.
--> The PDF gets created in C:Test (as C:Testtest.pdf) - This is good.
--> The filename and path does not get writen to the C4 range in Sheet1, as I want it to in the "Sub CommandButton1_Click()"

I hope someone can advise Thanks! Here is the code:

' ****************************************
' Function to create a PDF file 
' ****************************************

Function Create_PDF(Myvar As Object, FixedFilePathName As String, OverwriteIfFileExist As Boolean) As String

Dim FileFormatstr As String
Dim PDFfilename As String
Dim Fname As Variant
If FixedFilePathName = "" Then
    
'Open the GetSaveAsFilename dialog to enter a file name for the PDF:
 FileFormatstr = "PDF Files (*.pdf), *.pdf"
 Fname = Application.GetSaveAsFilename("", filefilter:=FileFormatstr, Title:="Create PDF")
    
'If you cancel this dialog Then exit the function...
  If Fname = False Then Exit Function
    
'Else take the value entered by user (i.e. set PDFfilename as the string value)
  Else
    Fname = PDFfilename
  End If
     

'If OverwriteIfFileExist = False we test if the PDF
'already exist in the folder and Exit the function if that is True
If OverwriteIfFileExist = False Then
If Dir(Fname) <> "" Then Exit Function
End If

' Begin exporting to the PDF and publish after creating
On Error Resume Next
Myvar.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=Fname, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
On Error GoTo 0
    
End Function

' ****************************************
' Click the button to run
' ****************************************
Sub CommandButton1_Click()

Dim strPDFFile As String

      
    strPDFFile = Create_PDF(Sheets("Sheet2"), "", True)
    
    ' Write the filename and path to the sheet
    Sheets("Sheet1").Select
    Range("C4").Select
    Range("C4").Value = strPDFFile

    
End Sub


Hi everyone.

I am working on a userform in which user can select diffrent sheets for print.
I want all selected sheets to be printed in a single pdf and it could be great if it not ask for a path to save file.
Thanx in advance.

My problem is my code will only print one sheet befor going to the next counter. I'm trying to get to print multiple sheet equal to the value of cell J80. Before going on to the nect counter. Example if cell J80=5 it should print 5 sheet as one print job and then move to next counter

here is my code

A Big thanks in advance


	VB:
	
 doprint() 
     '
     ' doprint Macro
     
     
    Dim i As Integer 
    Dim oCell As Range 
     
     
     
    strjobnumber = InputBox("Start in Job Number?", " First Job to 
    Print", 0) 
    endjobnumber = InputBox("Finish in Job Number?", " Last Job to 
    Print", 0) 
     
    Range("I40").Select 
    Range("I41").Select 
     
     
    For Counter = strjobnumber To endjobnumber 
        Sheets("Pieces").Activate 
        Range("L5").Value = Counter 
         
        ***Here I would Like To grabe the value of cell J80)*** 
         
        Sheets("BatchSheet1").Activate 
         
        ****Here I would Like tp print the number of sheet equal To 
        the value of Cell J80 As one print job*** 
        ****Currently it will only print one sheet befor going To Next 
        counter******* 
         
        ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, 
        Copies:=1, Collate _ 
        :=True 
    Next Counter 
    Sheets("Pieces").Activate 
    Range("$A$1").Select 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines



No luck finding an answer? You could always try Google.