Free Microsoft Excel 2013 Quick Reference

Printing to PDF format using CutePDF Writer in VBA

Hi All

I use CutePDF writer - installs as a printer so you can "print" Excel books to PDF format.

I'd like to loop through all my XLS files in a folder and print them all the PDF format giving them the same name as the XLS filename (with the correct extension though).

Can this be done?

I'm comfortable with the file search and looping sections, its just the controlling of the PDF side of things.

By recording a macro that does it on one file, it will do it but the PDF program prompts for a filename and the Macro appears not be able to control that window. Can I pass a parameter to the PDF program with the filename already in it?

thanks
Barry


I have an excel workbook with 11 sheets and when I print to the .pdf writer
it will print 10 of 11 and ask for 11 to be printed seperately. I am using
CutePDF Writer. I would like to print all of them as a single .pdf, how can
I fix this?

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

Hi,

I have a document, which needs to be collated each month and printed to PDF.
I have already worked out a way to collate all the summaries I need just for printing and in to position them in the correct order but I need help with the selection of printer.

I recorded a macro, selecting my PDF writer, which works fine.
However, certain people have different printer names for PDF writer around the office, which means I need to input some kind of If iserror procedure to enable different PDF writer selections.

Inevitably, when the macro fails to find the printer specified in the module, an error occurs.
Anyone knows how I can more or less include conditions around the printer selection?

thanks
simon

We have a very strange issue with the use of SendKeys to print to pdf. It seems to work only with excel 2000. We have variables to give the name of the file we want (name of workbook & sheet), but when we try to use the same code in a 2003 version of excel, the dialog box needs to be filled in manually - not very efficient when you have 2000 sheets to print. I have read in many places that the use of SendKeys is not recommended but I have yet to find a different way to print to PDF using what we already have - Acrobat 7.0. Thanks in advance

HERE IS THE CODE

Sub printToPdf()

Dim x As Integer
Dim i As Integer
Dim j As Integer
Dim sheetName As String
Dim bookName As String
Dim compAbbr As String
Dim numReports As Integer
Dim WordObj As Word.Application
Dim PSFileName As String, PDFFileName As String, DistillerCall As String
Dim ReturnValue As Variant
Dim keepOpen

Workbooks("pointBook.xls").Activate
Sheets("specSheet").Select
numReports = Range("b10").Value
compAbbr = Range("b8").Value

For x = 1 To 10

Workbooks("BookName.xls").Activate
Sheets("SheetName").Select
Cells(1, 132 + x).Activate

'determine if section was generated
If x > 1 Then
Sheets("specSheet").Select
If ActiveSheet.CheckBoxes("Check Box " & x - 1).Value = xlOff Then
GoTo 500
End If
End If

Sheets("SheetName").Select
keepOpen = 1
bookName = ""

For i = 1 To numReports

keepOpen = 1
Workbooks("BookName.xls").Activate
Sheets("SheetName").Select
ActiveCell.Offset(1, 0).Activate
If ActiveCell.Value = bookName Then
keepOpen = 2
GoTo 300
End If
If i > 1 Then
Workbooks(bookName).Save
Workbooks(bookName).Close
End If

bookName = ActiveCell.Value

300 If bookName = "" Then
GoTo 200
End If

sheetName = Cells(ActiveCell.Row, 2).Value

'Define the path and filenames
PSFileName = "c:folderName" & compAbbr & "ReportspdfParts" & sheetName & "_" & bookName & ".PS"
PDFFileName = "c:folderName" & compAbbr & "ReportspdfParts" & sheetName & "_" & bookName & ".PDF"
ActiveCell.Offset(0, 12).Value = PDFFileName

'open workbook
If keepOpen = 2 Then
GoTo 400
End If
Workbooks.Open "c:folderName" & compAbbr & "ReportsexcelParts" & bookName

400 Workbooks(bookName).Activate
Sheets(sheetName).Select

SendKeys PSFileName & "{ENTER}", False
ActiveSheet.PrintOut , PrintToFile:=True

'Add double quotes around the PS filename and PDF filename:
PSFileName = Chr(34) & PSFileName & Chr(34)
PDFFileName = Chr(34) & PDFFileName & Chr(34)
DistillerCall = "C:Program FilesAdobeAcrobat"

200 Next i

Workbooks(bookName).Save
Workbooks(bookName).Close

500 Next x

End Sub

Hi

I have writte a simple macro to print to PDF using Adobe PDF however I am missing the option to ask when you want to save the file.

Would you know what I need to add to promte to where I can save and open it after it has been saved? I have predetermind the file name and I thought that may cause the issue



In addition once the file is saved Adobe cannot reopen the as it has some deconding error, I really do not know what I would need to add.

I really appreciate any kind of help

Thank you in advance

Hiya

Newbie in the forums, I want to print to a pdf file using excel, I have 4 workbooks each with a front sheet that needs to be made into pdf to be distributed by email via Outlook (one email to multiple recipients with 4 pdf files).

I have adobe distiller and adobe PDFWriter, once I have an idea of how to give parameters to either of the pdf creators i can rinse and repeat with the others and expand my knowledge of adapting programs outside of excel and VBA.

Any help on manipulating the print to pdf creator would be great.

Thanks in advance

Chunkynut

(sorry if this is not concise, I'm only on my second caffine hit of the day)

I have a 58-worksheet workbook that I want to print to PDF in its
entirety as one document. Each worksheet is sized to fit on its own
"page" in the PDF.

For some reason, when I print to PDF, it gets broken into three
segments: the first is 34, the second is 5, and the last is 19 pages.

What is in Excel that is causing this? I have other similarly sized
workbooks that print to PDF as one PDF document so I highly doubt it's
Adobe Acrobat's fault.

Thanks -

Brian Mc

I'm trying to write a routine that will print a pdf page. I need to make
this a routine because I have to loop through a large set of data and print a
pdf page for each row of the data.

My typical method for writing a routine is to record a macro and then modify
the module I created. Unfortunately, when I print to pdf using acrobat
distiller, the macro does not record all the steps. In particular is does
not collect the filename or location. Here is the macro I created when I
recorded my keystrokes:

Sub pdfPrint()
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub

I need to specify the filename and location in the macro (different for each
row).
Maybe I should be using another tool for creating the pdf files? Any help
would be appreciated.

Ecxel 2003/Acrobat Distiller 5

I have a project where I'm using VBA to update multiple excel
workbooks and creating custom copies of the workbooks as PDF files.
The custom copies of the workbooks are customized by what sheets and
individual wants to see. I have successfully used PDFwriter to
suppress the "Save as PDF File" dialog by programatically changing the
registry entries for PDFFileName, bExecViewer and bDocInfo and using
the printOut method of excel to get what I need. This works well for
all but two of the workbooks. These two workbooks create the PDF files
as programmed but at the end of the print to PDF before control
returns to the VBA code, the "Save as PDF File" dialog pop's up. I
acts as if either PDFwriter or Excel does not recognize that it had
already output a PDF copy of the workbook.

This is anoying as I have not been able to figure out what is
triggering the dialog box to pop up after the print to PDF is
complete. Has anyone experienced this or have any possible solutions
for this situation? I'm using Excel 2000 and Acrobat 5.0.

( I realize that this is more of printing newsgroup issue, but having
gotten no results there (after a few days....))

---> problem with acrobat (when printing to PDF's) not printing the
value of the cell, but printing the graphic of the page (ex: lines, borders,
and fill)

case 1
the cell values have been formatted by VB code: none of the values print,
only the graphic of the page (ex: borders etc...); if I select a cell (any
cell) and reset the font (thru the usual excel / format / cells...) ; it
seems to consistently be OK, and everything prints

case 2
the cell values are simple cell entries: some lines of values print, then
several lines do not , then some following do... but, as with above the
format of the page does print; resetting the font does nothing

I cannot tell if these two conditions (above) are firm; it is what seems to
happen, but it may be only what happens MOST of the time, and then only by
coincidence

the format is standard Ariel 8 or 10pt

thanks in advance
mark

Hi,

I am trying to create a Macro in Excel 2007 that will print to PDF and save to a specific file name. I tried to record my steps using the macro recorder and then change parts to variable however all I got from the recorder was this...

Application.ActivePrinter = "Adobe PDF on Ne03:"
    ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,""Adobe PDF on Ne03:"",,TRUE,,FALSE)"
Can any one help me with what to add to this so I can add a variable for the File name and for the location for the PDF to save?

Thank you for any help.

I have a 58-worksheet workbook that I want to print to PDF in its
entirety as one document. Each worksheet is sized to fit on its own
"page" in the PDF.

For some reason, when I print to PDF, it gets broken into three
segments: the first is 34, the second is 5, and the last is 19 pages.

What is in Excel that is causing this? I have other similarly sized
workbooks that print to PDF as one PDF document so I highly doubt it's
Adobe Acrobat's fault.

Thanks -

Brian Mc

Hi All,

I have a macro working perfect in Excel 2007, but I cannot get it working in excel 2003.

Can anybody help me to find workaround for 2003.

Thanks
M

Here is what I need:

1. Print to PDF ( I have cute PDF in 2003 )
2. Save this PDF with the name RangeF10, to a path "S:PONUDE" & Range("B69").

Sub
Button3_Click()
Calculate
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"S:PONUDE" & Range("B69").Value & "" &
Sheets("PONUDA").Range("F10").Value & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End sub


Hi all. Need help printing commission statements. I have a group of 50 employees receiving commissions. The commissions are summarized in an excel sheet. I created a statement form that has a bunch of vlookup formulas using an employee id and looking to the summary spreadsheet. Currently I load an employee # print to pdf then repeat 50 times. Looking for solution to automate with a macro. Help!

Hi All,

My workbook is essentially a list of suppliers, I have various information on the service they have provided dates, invoice number and amount owing. By first selecting all rows and organising by the Suppliers name I get a neat list which I can apply the subtotal function to. This accurately splits out my suppliers showing a subtotal under each for the amount I owe them and putting in a page break between each supplier.

In the past I paid them all by cheque so I simply printed this all out and gave them a copy as their remittance. I now pay them by bacs and they receive their remittance by email. Most want/require their remittance in the form of a pdf. The problem is that when I print to PDF it saves the entire workbook as one document.

I need a macro to save each supplier to a different pdf document with the supplier name. I have had a go at this but am relatively useless with VBA.

Do
Selection.Copy
Range("AL2").Select
ActiveSheet.Paste
ActiveSheet.Range("$V$3:$V$63356").AutoFilter Field:=1, Criteria1:=Range("AL2")
Dim FP As String, FN As String
FP = "W:Simon"
FN = Range("AL2").Value
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FP & FN & Format(Date, " dd-mm-yy"), Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
ActiveSheet.ShowAllData
Columns("AK:AK").Select
Selection.Find(What:=Range("AL2").Value, After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select
Selection.Offset(1, 0).Select
Loop Until IsEmpty(Range("AL2"))
Range("B3").Select
End Sub

Any help at all would be very much appreciated and save me a lot of time each month. Thanks in advance

Hi,
I'm trying to print to PDF as it is displayed in Excel. As in, dotted lines print dotted instead of solid. Or, gridlines print light gray, etc. It looks fine in print preview, but it seems like there's a big loss of fidelity when you print to PDF. Is there a known solution? I'm using 2010.

Thanks,

Dan

( I realize that this is more of printing newsgroup issue, but having
gotten no results there (after a few days....))

---> problem with acrobat (when printing to PDF's) not printing the
value of the cell, but printing the graphic of the page (ex: lines, borders,
and fill)

case 1
the cell values have been formatted by VB code: none of the values print,
only the graphic of the page (ex: borders etc...); if I select a cell (any
cell) and reset the font (thru the usual excel / format / cells...) ; it
seems to consistently be OK, and everything prints

case 2
the cell values are simple cell entries: some lines of values print, then
several lines do not , then some following do... but, as with above the
format of the page does print; resetting the font does nothing

I cannot tell if these two conditions (above) are firm; it is what seems to
happen, but it may be only what happens MOST of the time, and then only by
coincidence

the format is standard Ariel 8 or 10pt

thanks in advance
mark

i'm trying to use sum function in vba code...

(mydollars is a valid range name and
the =sum(mydollars) works fine in a cell)


	VB:
	
rng3.Name = "myDollars" 
mysum = ":=sum(mydollars)" 

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

thank you.

Hi all.

I want to use a macro to automate the creation of a PDF file as a I have few invoices to do every month. I have searched the net hi & lo & this is the closest I have but the problem is that after creating the PDF it hangs when trying to close the PDFCreator app. I am using Excel 2002 on XP SP3.

Code:
Sub PrintToPDF_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

    '/// 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 = CreateObject("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
It appears that the PDFCreator is not releasing the printjob so as to close the pdfcreator.

Any ideas would be appreciated.

I have a series of large Excel spreadsheets that need to be printed to
pdf. In the print preview, the last few columns are missing, even when
they are included in the print area. Any help would be gratefully
received as these large data sheets are a mainstay of our business...

--
DRAWIST
------------------------------------------------------------------------
DRAWIST's Profile: http://www.excelforum.com/member.php...o&userid=25398
View this thread: http://www.excelforum.com/showthread...hreadid=388695

Hi,

I was wondering if anyone has ever expericed an excel document that doesn't completely print to pdf? It prints fine to the printer but when printing to pdf a fair amount (around 2 inches) of the bottom gets cut off (print preview shows it cut off too).

It only happens to this document but with various versions of office and pdf writers. I've attached a copy of the file and here's a link: http://www.movecommunications.com/ex/pdfcutoff.xls if anyone has any ideas.

Thanks so much!

Running WinXP, Excel 2003 SP2 and Acrobat Distiller 5.0.5 - Excel
crashed often when trying to print to PDF. Grantd these are large
complex files with images, VBA, mass data and formulas.

There are no ad-ins in the XLStart folder. The computer also freezes
occasionally when working with large files, multiple open at a time.

Any ideas?? I already hafve 4gig ram in there.

Thansk in advance,

Sam

Hi

I'm wondering how to set up a shortcut button that will print to PDF. The button should be on the toolbar. I've noticed I can add a button that does the print... command, but it seems that'd bring up the print dialog box and I'd have to switch the active printer to pdf each time, and hit print. I'd like to do this without VBA if possible.

Also, would the process be any different in 07?

Thanks

I have a series of large Excel spreadsheets that need to be printed to pdf. In the print preview, the last few columns are missing, even when they are included in the print area. Any help would be gratefully received as these large data sheets are a mainstay of our business...