PDA

View Full Version : Solved: Converting Excel Sheet into PDF



jungix
06-19-2006, 04:56 AM
Hi,

I know how to do it myself by printing and using ADOBE PDF Converter, but is there a way to save a Worksheet from Excel in a macro?

I know there's a AvtiveWorksheet.SaveAs method, but is there a matching FileFormat?

lucas
06-19-2006, 06:44 AM
Hi jungix,
I use this in MS Word to print pdf files. It basically uses a macro to select the pdf printer for you....maybe you can change it to suit your needs...

if it looks like it might work for you and you can't get it worked out...post back here for some help.

Sub PrintPDF()
Dim Folder As String
Dim strFileName As String
Dim doc As Document
Dim DocPath As String
DocPath = ("C:\Temp\") ' change folder name here
strFileName = Dir$("C:\Temp\" & "\*.doc") 'Change your folder name here
Do Until strFileName = ""
'Change your folder name here
Set doc = Application.Documents.Open("C:\Temp\" & "\" & strFileName)
ActivePrinter = "Acrobat PDFWriter"
ActiveDocument.PrintOut
doc.Close
strFileName = Dir$()
Loop
End Sub

jungix
06-19-2006, 08:24 AM
Thanks to your post I succeeded in activating my printer with the following code:

ActiveWindow.SelectedSheets.PrintOut , ActivePrinter:="Acrobat PDFWriter"

This opens a message box asking me where to save it, and it works. But what I wanted to do was saving it directly by giving the file name, so I used:

ActiveWindow.SelectedSheets.PrintOut , ActivePrinter:="Acrobat PDFWriter", PrToFileName:="C:\Test.pdf"

It wouldn't print unless I ticked off "Do not send fonds to Adobe PDF" in the printee properties, which I did.

Now it prints a file, but unfortunately I can't read it (Adobe says it's not a PDf file or corrupted). Note that the size of the file jumped from 9ko to 66ko in the second method.

Can anyone help me with this? I don't know if it is related, but I changed the property of the printer using ctrl+P and not a VBA command in the macro.

lucas
06-19-2006, 08:54 AM
could we see all of this code please?

Ken Puls
06-19-2006, 10:27 AM
Hi jungix,

Do you have to use Adobe? If you can install a copy of Sourceforge's PDF creator (free), you could use one of the code samples from my site:

Printing Worksheets To A PDF File (http://www.excelguru.ca/node/21)

PS... Hi Steve! :hi:

lucas
06-19-2006, 10:28 AM
Hi stranger...good to see you

jungix
06-19-2006, 10:31 AM
That was pretty much all the code.



Public Sub Test()
Worksheets("s").Activate

ActiveWindow.SelectedSheets.PrintOut , ActivePrinter:="Acrobat PDF", PrToFileName:="C:\Test.pdf"

End Sub


Without the PrToFileName:="C:\Test.pdf" there is a prompt asking me for the file name and it works. But with it it appears that the file is a PS (I can open it and convert it to PDF manually. I found some advice asking me to convert it first to ps and then to pdf but it doesn't work.

I found this:

Private Sub CommandButton1_Click()

' Define the postscript and .pdf file names.
Dim PSFileName as String
Dim PDFFileName as String
PSFileName = "c:\myPostScript.ps"
PDFFileName = "c:\myPDF.pdf"

' Print the Excel range to the postscript file
Dim MySheet As WorkSheet
Set MySheet = ActiveSheet
MySheet.Range("myRange").PrintOut copies:=1, preview:=False, ActivePrinter:="Acrobat Distiller", _
printtofile:=True, collate:=True, prttofilename:=PSFileName

' Convert the postscript file to .pdf
Dim myPDF As PdfDistiller
Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, ""

End Sub

But when I try I get an error message: User defined type not defined at the line: Dim myPDF As PdfDistiller. But I don['t know how to define a PDFDistiller. Isn't it possible to just convert the file to PDF directly with PrToFileName?

lucas
06-19-2006, 11:05 AM
did you set a reference to the pdf distiller in excel:


Dim myPDF As PdfDistiller

jungix
06-19-2006, 11:09 AM
How am I supposed to do this?

Ken Puls
06-19-2006, 11:11 AM
How am I supposed to do this?
In the VBE, go to Tools|References

Scroll down the list until you see something that resembles PDFDistiller and check the box.

HTH,

jungix
06-19-2006, 11:48 AM
Thank yoyu very much to all of you guys. :bow:

Now it's working

smithy73
06-29-2006, 11:24 PM
:help
I wonder if you guys have had trouble with looping through PDF creator ?
Sounds Odd right?

I have a process that goes through and creates a single page report.
This single page report is then PDF'd using Ken's spectacular script. All good!

The single page report is a single sheet workbook created and saved - no problem.

Problem arises when I loop through and create the second single page report (same different day) I get the ["Can't initialize PDFCreator."] error.

Can the references unload/unplug themselves ?
Is there a buffer I can clear ?
:doh:

Any assistance appreciated.

PS. BTW: Probably the best forum goin' around on VBA - Many Thanks

jungix
06-30-2006, 05:13 AM
It's because you have to keep only one pdfdistiller for your loop. You only have to declare


Dim myPDF As PdfDistiller
Set myPDF = New PdfDistiller


once.

smithy73
06-30-2006, 06:40 AM
Excellent - I'll give that a go - thankyou for the help.
[Using a sleep command at the moment as a patch]

Ken Puls
06-30-2006, 08:42 AM
Smithy73,

Just to clarify, you're calling the PrintToPDF_Early routine, letting it run completely through, then calling it again, is that correct?

For some reason, it seems that PDFCreator needs to completely unload between routines, which takes it a little time. If you've got it working using a sleep command, I might stay with that.

I did have an individual email me as well, who is getting the "failed" message the second time he runs any routine. I"m going to work on something to clear that up if I can. Curious to know how things work out for you though.

Cheers!

smithy73
06-30-2006, 04:39 PM
Here's what I added (in bold):


Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub BlahBlah

<<SNIP BLAHBLAH>>
'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 the PDF file shows up then release the objects
Do Until Dir(sPDFPath & sPDFName) <> ""
DoEvents
Loop

Sleep (5000)

pdfjob.cClose
Set pdfjob = Nothing


<</SNIP BLAHBLAH>>

End Sub

Not knowing a huge amount about how VBA interacts with other programs this is what I reckon is going on.

First loop through the code is all good. When the second loop comes around no go.
I think this is due to PDFCreator remaining open/still holding the print job and VBA attempting to open another 'session'.
The problem also arises if the 1st crash isn't tidied up (PDFCreator - EndTask) and therefore attempts to open another.

I put your :bow:superb:bow: code in a loop on its own and created pdfs with a consecutive number as the filename - I found that given VBA takes up 100% of CPU usage while running it appeared there are no resources to clear the PDFCreator job.

The sleep command rests the CPU usage (in this case for 5 seconds) long enough to clear the job. A do..until continues holds CPU Usage to 100%.


Thanks for the assistance - been looking for a workable pdf process for 6 months until I found this.

smithy73
06-30-2006, 05:29 PM
Just past 300 loops (50k PDF) created with a 5 second Sleep.

No problems yet - 700 to go.

smithy73
07-01-2006, 04:20 AM
Smithy73,

Just to clarify, you're calling the PrintToPDF_Early routine, letting it run completely through, then calling it again, is that correct?



Yep.

** Procedure
** PrintToPDF_Early routine
** Rest of Procedure

Wolfgang
07-02-2006, 06:43 AM
Hi Ken...

I used your program plus Sourceforge's PDF creator and it worked on my system exactly once...

Even after a complete re-boot I was not able to get past this error message ["Can't initialize PDFCreator."]

I am using Office 2003 Pro on XP-Home SP-2 German Version...

Best regards,
Wolfgang

Ken Puls
07-02-2006, 09:18 PM
Hi Ken...

I used your program plus Sourceforge's PDF creator and it worked on my system exactly once...

Even after a complete re-boot I was not able to get past this error message ["Can't initialize PDFCreator."]

I am using Office 2003 Pro on XP-Home SP-2 German Version...

Best regards,
Wolfgang

Hi Wolfgang,

I've received a similar issue via email from someone. Open your taskmanager and locate the PDFCreator.exe process that is running. Kill it, and you'll be good to run the code again. I working on a routine to do exactly that in lieu of the error.

Cheers,