Convert Excel to PDF via VB code

[wayne]

I'm fairly new to Excel programming. I'm trying to automate the process
to covert reports generated by an Excel macro to PDF files (one report
has to be saved as a PDF file, then the macro will clean up the Excel
sheet and create another report, save the new one as another PDF file.
Excel should continue the procedure until the macro tells Excel to
stop).

I tried the "record macro" function to record the process but no VB
code was recorded for the procedure to convert an Excel range to a PDF
file. I have asked all my friends for help but nobody knows what VB
code could tell Excel to do the job.

Any suggestion will be highly appreciated.

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

[Tom Ogilvy]

Excel has no built in capability to do it. The usual method is to use the
pdfwriter print driver from Adobe Acrobat. Do you have Adobe Acrobat - the
whole product, not just the reader?

You can also look for third party solutions

http://www.pdfplanet.com

--
regards,
Tom Ogilvy

wayne <wayne...@excelforum-nospam.com> wrote in message
news:wayne...@excelforum-nospam.com...

[Kevin Stecyk]

Wayne and Tom,

I went hunting on the net to find info regarding Adobe PDF documents and
VBA. This appears to be a commonly asked question, so I thought it might be
worth a bit of hunting. I have not test neither of the following code. You
are on your own. But I do provide the URLs where I got the information, so
you can always go to the source to learn more.

Hope this helps.

Regards,
Kevin

http://www.rdpslides.com/psfaq/FAQ00053.htm

In the Excel Visual Basic Editor, make sure you include a reference to
Acrobat Distiller.

Here is the code....

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

Last updated 12/5/2002 12:26:39 AM

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
http://www.brookpub.co.uk/freestuff.htm

Excel macros for Acrobat users
=======================

download excel.zip

This zip file contains two Excel template that perform batch operations when
creating PDF files from Microsoft Excel. You must be running a full version
of Acrobat for the macro to work, i.e. not Reader.

xls2pdf.xlt - prints a directory of xls files to PDFWriter thus creating a
bunch of PDF files. It is assumed that the files are all in the same
directory. You can edit the 'in' directory and 'out' directory parameters in
the macro.

printsheetsandcombine.xlt - prints all the sheets in a workbook to PDFWriter
and combines them to create a single PDF of a workbook. By default the
single file is create at c:\temp\wks1.pdf. You can change this in the macro.

If you have a modicum of VBA experience you can of course combine the two
templates!

NOTE: Neither macro determines the flavour of Windows you are using. This is
important because the PDFWriter ini file is in a different location under
WinNT than Win95/98. You must edit the WriteINIFile procedure in the macros
for them to work correctly for your platform. This is not difficult. Just
find your way to the WriteIniFile macro in the template and comment out the
line that does not apply as follows:

'*********
svPDFIni = "c:\windows\system\pdfwritr.ini" 'CARE: Windows 95/98
'svPDFIni = "c:\winnt\system32\spool\drivers\w32x86\2\__pdf.ini" 'CARE:
Windows NT
'*********

"Tom Ogilvy" <twog...@msn.com> wrote in message
news:vq11jr5...@news.supernews.com...