Need to print two worksheets to .pdfs

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Michael McClellan - 07 Jun 2004 02:24 GMT

OK. I use the following code to print two worksheets of a larger excel
file to .pdfs. The code executes fine with no errors, but creates two
.pdfs that are 0kb in size and when I try to open them I get a message
that says: "A file read error has occured."

Here is the code:

'Print the PO and PL to .pdf files
ChDir "R:\Orders\" & CurrDir
Application.StatusBar = "Generating " & ".pdf, please wait..."
Sheets("Purchase Order").Select
Range("A1:G53").Select
Selection.PrintOut Copies:=1, PrintToFile:=True,
PrToFileName:="R:\Orders\" & "HM" & DirVar1 & DirVar2 & " - " &
DirVar3 & " - " & DirVar4 & "\" & varInvoice & " - PO.pdf",
ActivePrinter:="Acrobat PDFWriter"
'Kill PDFFileName
Range("A1").Select
Application.StatusBar = False

Application.StatusBar = "Generating " & ".pdf, please wait..."
Sheets("Packing List").Select
'Range("Packing List").Select
Selection.PrintOut Copies:=1, PrintToFile:=True,
PrToFileName:="R:\Orders\" & "HM" & DirVar1 & DirVar2 & " - " &
DirVar3 & " - " & DirVar4 & "\" & varInvoice & " - PL.pdf",
ActivePrinter:="Acrobat PDFWriter"
'Kill PDFFileName
Range("A1").Select
Application.StatusBar = False

Reply to this Message

Paul Cundle - 07 Jun 2004 15:56 GMT

I may be off track, but are you sure you want to be printing using the
option PrintToFile:=True ?
I've used two PDF writers and, although neither were Adobe's own, they both
expected to be treated as normal printers. I suspect that you are outputting
two spool files which just happen to have a .pdf extension because you
forced one upon them. This would explain why Acrobat Reader can't open them,
though I'm unsure why you'd get 0 length files.

Try changing to PrintToFile:=False and running again. It might ask you for a
filename, and if it does then just give it one and see what happens. If it
works and you want the automatic filename again then I'm afraid I can't
help.

On another note, your code seems to have some unnecessary bits in it:

> Application.StatusBar = "Generating " & ".pdf, please wait..."

Why not just Application.StatusBar = "Generating .pdf, please wait..." ? It
won't really help, but would be a little tidier.

> Sheets("Purchase Order").Select
> Range("A1:G53").Select
> Selection.PrintOut Copies:=1 .... etc etc

This could be shortened to the following:

> Range("Purchase Order!A1:G53").PrintOut Copies:=1 .... etc etc

This is not only shorter but will print the stated range without
unnecessarily changing the display. Not a major issue, but it would allow
you to remove:

> Range("A1").Select

from the bottom of the macro as well, thus leaving the user's cell selection
as it was.

Hope some of that helps.
Paul C,
--

Reply to this Message

Rate this thread: