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

Free Microsoft Excel 2013 Quick Reference

Printing Multiple PDF files using VBA Code

I have a client who developed a very simple MS Access application where he loads data for about 250 stores in a table and prints a report for each store. He wanted to print 250 different reports in pdf form, one for each store, and put the reports in the same folder with a different name for each store. I solved the problem by using VBA code to open the report in design mode, changing the Report caption to the desired report file name, and saving the report. Then I opened the report for print (he had set his PDF printer as the default printer) and applied the filter to limit the report to the desired store. I looped through all the stores till they were all "printed".

Now, I have another department with a similar request, however, his "database" is in Excel. Does anyone know how I can "print" the selected Print Area to the default printer with a specific file name.

Thanks, Eddie


Post your answer or comment

comments powered by Disqus
I wish to open a pdf file using vba. Does anyone know whether this can be done?

Once a week I need to print multiple (from 2 to 75 and more) tif files stored on my HD

The files are all named in this way : xxxxx-yy.tif
where xxxxx is a 5 character number and y is the pagenr.
example : 01254-1.tif, 01254-2.tif,..., 01254-12.tif

For the moment I use some VBA code to filter the tif-names, I import the selected files and print them. (=quite slow)

My question : is there a way to print the files directly to the printer instead of import & print ?

I'm looking for an alternative in VBA for this : http://www.anzio.com/support/documen...intwizprog.htm
(this Print Wizard only prints text-based files)

I manage a number of .pdf files that I sort and create a uniqe combined
(combined into one .pdf) bundle for each client. I know I can
associate .pdfs in Excel as hyperlinks and query then and sort based on
client requirements.

Does anyone have any ideas on how to automatically print a select set
of .pdf documents, combining them into one .pdf document using VBA
code?

Thank you,

Hi

I have a file which is to be sent out to different recipients (at least
200). Although the file is same I need to send this to various
recipients with different file names. I have a list that is to be used
to name the files.
For example I have a file called Report.xls and have a list a, b,
c...z. (in a file Nemes.xls). I want a code that will save the file
report.xls as a.xls, b.xls.... z.xls in the specified folder/location.

I know this is possible using VBA code.
Can you helpme by giving some useful code?

Karthik Bhat
(Bangalore)

Is it possible to count the number of pages for a PDF file using VBA? I have over 500 pdf file info that I need to input daily. The specific filenames of the PDF files are placed in COlumn A while their corresponding (total) page count are inputted in Column B. Anybody got brilliant ideas?

Thanks!

Using the VBA code, I like to print (on the default printer) an existing file called "XYZ.pdf" that resides in the same folder as the Excel file. What would be VBA code for it?

Important: I am not trying to make a pdf file out of an excel sheet/range. All I want is a VBA macro that prints a specific, existing pdf file (in the same folder as the excel file) to my default printer to print the entire pdf file on paper.
Computer OS: Windows 7
Excel Version: Office 2007

Hello All,

Please, does anyone know a simple code to print a specific page of a pdf file using Excel VBA.

I have a spreadsheet that contains the pdf file name, the page i would like to print and quantities of that page.

Please help.

Many thanks
Charlie

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 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.

so i am working on a code that will print multiple PDF files. the only thing i need is the print command. right now the pdf is being opened using explorer which is using adobe reader 8

I have some embedded PDF files in Excel file. I want to save them in a separate folder as seperate files, by pressing a button in toolbar.
How to do that using VBA?

Thanks

Is it possible to merge pdf files using vba in excel? I have a list in excel, defining which files need to be combined.

I have the following:
- a list with files
1.pdf
2.pdf
3.pdf
4.pdf
5.pdf
6.pdf
7.pdf
8.pdf
- I have an excel file with the following sheet:
File list,first pdf;last pdf
merge1.pdf;1.pdf;3.pdf
merge2.pdf;4.pdf
merge3.pdf;5.pdf;8.pdf

In other words, sometimes a file has to be renamed (given in the case for merge2.pdf), sometimes I need to combine multiple pdf's.
In the case of merge1.pdf I need to combine 1.pdf, 2.pdf and 3.pdf.
For merge3.pdf I need to combine 5.pdf, 6.pdf, 7.pdf and 8.pdf

Any ideas?

I have an Excel 2003 file containing a sheet for requesting reimbursement of travel expenses available on the office network. Employees can open, edit, print, and save to their directory on their desk top or laptop. When the user prints, a VBA routine sorts the records and checks for missing information (cells colored red by conditional formatting).

If information is missing, the print process is cancelled and a message box tells the user to fill in the information.

If all is OK, the form prints.

I have a PDF file of 100 pictures from the company holiday party. I want Excel to display a picture from the PDF file for about two seconds just before the print process is completed. Ideally, Excel would automatically increment to the next picture each time the print process is run. After the last photo is shown, return to the first.

I am somewhat new to VBA so please be gentle (and explicit). If this works, it will impress my coworkers and lead them to think I know what I am doing.

Thanks so much.

I am wanting to use VBA code to open a text file with the
import wizard. Does anyone know how I would invoke the
import wizard to open a text file in VBA? Any assistance
on this matter would be greatly appreaciated.

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

Hi,
I have a macro the open a pdf file and print it.

'In a Module...
Option Explicit

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _
ByVal hwnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long

Private Const SW_HIDE As Long = 0
Private Const SW_SHOWNORMAL As Long = 1
Private Const SW_SHOWMAXIMIZED As Long = 3
Private Const SW_SHOWMINIMIZED As Long = 2

Sub Button1_Click()

ShellExecute Application.hwnd, "open", "D:DocumentsLayoutaa.PDF", vbNullString, "D:", SW_SHOWNORMAL
ShellExecute Application.hwnd, "print", "D:DocumentsLayoutaa.PDF", vbNullString, "D:", SW_SHOWNORMAL

End Sub

Could anyone help me in order to:
- open multiple PDF files (all in a same folder),
- print them and
- Close them (pdf)

With a possibility of setting up the page setup (A3 / A4 - Portrait / Landscape)

Regards
RV

So far, what I have is:

I'm trying to print a given Acrobat Reader PDF file from VBA. (Office 2000 &
Acrobat Reader 6)
I've included the reference to the Adobe Acrobat Control for ActiveX
(pdf.ocx),
but I'm unable to actually create an instance of the PDF control.
I've also tried putting a Microsoft Webbrowser control an my Excel
worksheet, and calling the
Webbrowser1.Navigate "someserversomefoldersomefile.pdf" method.
The webbrowser control indeed shows the PDF file, but I did not find a way
to print it's contents from VBA.

I'm looking for someone who can put me on the right track for this seemingly
simple problem.

Matthias Claes
matthias.claes(-at-)stadsbader.com

Hi,

I'm currently trying to add the "Solver" reference using VBA code but for some reason my code keeps coming with errors. I have tried using the codes provided in the following thread but I still get the same error even though the code seems to work for others.

Autoload reference library by name

The error I get is:

Run-time error '1004':
Application-defined of object-defined error
The current code I have at the moment that I wish to use is:


	VB:
	
 
Dim k As String 
 
j = Application.Version 
k = "C:Program FilesMicrosoft OfficeOffice" & j & "LibrarySOLVERSOLVER32.Dll" 
Application.ThisWorkbook.VBProject.References.AddFromFile "C:Program FilesMicrosoft OfficeOffice14LibrarySOLVERSOLVER32.DLL" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I am using Microsoft Excel and the VBA compiler to build this, so my theory is that it doesn't like being referred to as "VBProject".

The reason, I am doing this is because when I transfer my file to a computer with an older version of Solver, my program throws a fit and doesn't wish to work hence I require this code to make sure that the solver reference is always applied.

Many Thanks,

Vincent

Hello!
The output of my excel model is a CashFlow (CF). I wrote a macro that runs the CF for a list of companies (as a loop, company A, then company B, etc) and storage (as values) the resutling CFs (one company = one worksheet) in a second file (which later is "saved as" whatever name the user defines). The worksheets of this second file are renamed with the name of the active company. The format for all of the worksheets is the same (of course, it comes from the Cash Flow output)

Now, I have been asked to create a "delta run", and I think that I have successfully created the code for most of the task. Basically I created the macro that after selecting the companies the user wants to run,
it goes and select the parameters for the first run, then
run each company in the loop while export the resulting CFs to a second file (let’s call this second file A)
go back to the main model and re-set the parameters for the second run
run each company in the loop (again) and export the resulting CFs to a third file (let’s call this second file B)
then creates a copy of the file A and rename it “delta”, and then reopen the file A
So now I have the 3 files that I need open (Delta, A and B) and what I need the macro to do is go to each worksheet in the delta file and make cell C16 (to say something) Delta!C16=A!C16-B!C16


	VB:
	
 
Windows(delta).Activate 
Sheets(Company1).Select 
Range("C16").Select 
ActiveCell.FormulaR1C1 = _ "='[A.xlsx]Company1'!RC-'[B.xlsx]Company1'!RC" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
(I am sorry, but how should I use the code tags?)

Once I have the firs cell with the right formula then I can go and auto fill the rest of the cells in the CF
In a simple sentence: How can I insert functions (addition, subtract, etc) in a macro to relate cells from different files?

Thanks! and let me know if I wasn't clear in the explanation
pdatx

Hi All,

I have around 100 pdf files. I need to print specific pages from those files (e.g. 5 to 10).
I want to build a macro to do this task automatically. Could you please provide me some sources.

Thanks,

MG.

Hi everyone. I've searched on here and Googled but have yet to come up with anything aside from the code (posted at the bottom).

I have a Worksheet (statement) that is feed off of an ID #, when the ID# changes in a particular cell (P1) all of the information and format automatically change, which is great.

However, I need to run (print to PDF) 100 of these which is tedious and moderately time consuming. I would like to know if there is a macro I could record that would change the ID #, print the worksheet to PDF and save the PDF with a specific name (which obviously has to change w/ 100 statements being run in a string).

I'm really not sure exactly where to start, I've tried recording a macro to do this, but it is no help. The only thing I found useful while Googling was this:


	VB:
	
 PrintingTest() 
    Dim sFileName As String 
     'Change here to an appropriate file name
    sFileName = "C:V080506.pdf" 
     'Prints three copies.
    PrintPDF2 sFileName, 3 
    MsgBox "Data has been sent. " & vbLf & _ 
    "Please close the instance of Acrobat Reader after printing." 
End Sub 
 
-------------------------------------------------------------------------------- 
 
Sub PrintPDF2(ByVal FileName As String, Optional Copies As Long = 1) 
     '+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
     '++ Prints the PDF files using a command line.
     '++ Written by Masaru Kaji aka Colo
     '++ Syntax
     '++ FileName : Required String expression that specifies a file name
     '++           - may include directory or folder, and drive..
     '++ Copies : Optional Long. The number of copies to print.
     '++          If omitted one copy is printed.
     '+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    Const PrinterName As String = """Microsoft Office Document Image Writer""" 
    Const DriverName As String = """Microsoft Office Document Image Writer""" 
    Const PortName As String = """Microsoft Office Document Image Writer port:""" 
    Dim cnt As Long 
    Set myShell = CreateObject("WScript.Shell") 
    For cnt = 1 To Copies 
        myShell.Run ("AcroRd32.exe /t " & FileName & " " & PrinterName & " " & DriverName & " " & PortName) 
    Next 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
But I am not sure if this will work with a batch of 100 that needs to go.

Thanks for any help in advance,
have a good one
-JVT

I want to merge two excel files (both have) only one excel sheet using VBA code. Can any one give me hint or help me out to carry out this task?

Hi all

I have a short question on disallowing printing when creating PDF files with PDF Creator.

This is my code, the important part is in bold.

Still, I can print the file in Acrobat.

Thank you for your time and comments.

Wigi

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 = "testWithoutPrinting.pdf"
sPDFPath = "C:"

'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

.cOption("PDFDisallowPrinting") = 1

.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

Hi all,

Could someone help me to convert Excel file to Text (txt) file using VBA code.

I have data in range A3:C10 of Excel Spreadsheet and I need VBA code to read then write this data into text file and save as .txt file

Thanks in advance.

Cheers,
bogia


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