how to print to pdf?

[Jlorga]

Hi, i'm new to vb stuff and i need fill an Excel Sheet with some data
from XML files and then i need to print that sheet to a .pdf file. And
doing this from a .hta application.

I do everything and i have a macro in the excel sheet that do this:
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True,
PrToFileName:=filename

And in the Excel application i call this macro with the run command,
like this:
ExcelAppA.run "print2pdf"
And with this everything works, but the thing is that i need to pass
the filename as a parameter to the macro, and when i do that i don't
get any errors, but i also don't get any .pdf file.
I print the filename with the entire path, and i'm sure it's ok.

I need this for yeasterday, so if anyone can halp me on this? Is there
any way to do it without a macro, like calling the printOut method from
the .hta application?

Thanks,

 

[weavtennis]

I did a "macro>record" for my PrimoPDF (virtual) printer... and got:

Application.ActivePrinter = "PrimoPDF on Ne00:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:=
"PrimoPDF on Ne00:", Collate:=True

You can then use the "sendkeys" to "type" the filename into the dialog
for the filename...

'you can set any valid text (including subdirectory information) to a
variable
printFileNamer = "Weavtennis.pdf"

Application.ActivePrinter = "PrimoPDF on Ne00:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

'Sets the wait time for the new application to appear (mine is less
than 2 seconds)
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 2
WaitTimer = TimeSerial(newHour, newMinute, newSecond)
Application.Wait WaitTimer

'sends the text in "printFileNamer" to the dialog box for the printing
application.
SendKeys (printFileNamer)
SendKeys ("{ENTER}") 'executes the print in the printer dialog...

Anyway, it works for me - your execution keys may need to be tweaked
for yours to work...

 

[Jlorga]

That would do the trick, i copied your example, and change it to my own
configuration, but i couldn't make it work, i do this in the excel
macro:

nameFile = "F:\proj\teste.pdf"
Application.ActivePrinter = "Adobe PDF em Ne01:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 2
WaitTimer = TimeSerial(newHour, newMinute, newSecond)
Application.Wait WaitTimer

SendKeys (nameFile)
SendKeys ("{ENTER}")

but when i run the macro i got the dialog box to choose where to print
the file and its name.
I need to do anything to get SendKeys to work?
And why exactly you use the timer?

Thanks again

 

[weavtennis]

“And why exactly you use the timer?”

The timer allows enough time for the printer dialog to open (when a lot
is going on in the application… or on the computer, it could take a
while for the dialog box to open.)

I need to do anything to get SendKeys to work?

You may need to do the following:
1) note down EXACTLY what keystrokes are required to use the printer
dialog. Do not use the mouse for any activity once the dialog box is
up. Remember, you are sending keystrokes – not mouse clicks.

2) look at the Excel VBA help for “sendkeys”… the list of acceptable
text string replacements for keystrokes is there… compose your sendkeys
statement to exactly match what is needed for the dialog (e.g., step
#1).

Hope that helps…

 

[Guest]

Hi,
I found this discussion between RC and Darcy from July last year in Excel
General Questions with the header “Macro printing to a file†and I managed to
solve the PDF saving problem. The original suggestion was to save numerous
files, I’m saving only one at a time. I’m running Excel 2003 and Acrobat 6.0.
The trick to get rid of the saving prompt was to change the printer
properties in the Control Panel – Printers and Faxes – Adobe PDF – Properties
– Printing defaults.
You cannot set these properties permanently within the properties in Excel,
as soon you have reset to your Standard Printer the options are selected
again.
Clear the selections for “Prompt for Adobe PDF filename†AND “Do not send
fonts to “Adobe PDFâ€â€. Both are important, the latter for the driver to make
the Postscript files that’ll be deleted after conversion to PDF-files.
Here’s my code:

'**** PDF Creator
Public Sub PrintPDF(DCSel, IXSel)
Dim pdfDist As New ACRODISTXLib.PdfDistiller
Dim pdfPrinter, pdfName
Set fs = CreateObject("Scripting.FileSystemObject")
StdPrinter = Application.ActivePrinter
pdfUser = "pdf" & Application.UserName
pdfPrinter = Range(pdfUser).Value
pdfFilePath = "P:\"
pdfName = Range(DCSel) & " - " & Range(IXSel) & " - " _
& Range("PN1") & " - " & Format(Range("DocDate"), "YYMMDD")
psFileName = pdfFilePath & "\" & pdfName & ".PS"
pdfFileName = Left(psFileName, Len(psFileName) - 2) & "pdf"
Sheets("PrintDoc").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, preview:=False,
ActivePrinter:=pdfPrinter, _
printtofile:=True, collate:=True, PrToFileName:=psFileName
pdfDist.FileToPDF psFileName, pdfFileName, ""
Set pdfDist = Nothing
fs.DeleteFile pdfFilePath & "\*.PS"
fs.DeleteFile pdfFilePath & "\*.LOG"
Application.ActivePrinter = StdPrinter
End Sub
'*** End PDF Creator

A couple of notes:
As my colleagues are using the same program but does not necessarily have
the same Adobe driver on the same port, (mine is “Adobe PDF on NE06:â€)
I have put the various users printer names in the workbook and the program
looks the appropriate user up and select his/her printer.
Furthermore I created our special naming structure of the document set by
the programs previous selections/conditions. Please note that the pdfName
shall not have any “.pdf†extension.
Good Luck!