Creating a new pdf document using AcroEXch in VBScript

Question

Hi

I am looking to automate the conversion of an excel sheet into a pdf document (I do not want to manually print the report generated in excel as a pdf document every morning). For now, I would like to create a button in excel that will run the macro to automatically generate the pdf document, but this button will eventually not be used.

Im also new to VB, but have read up on the AcroEXch SDK. Seems like I should be using AcroEXch.PDDoc.Create, but this is not quite right (because I cannot specify an input file to be printed/created as a new pdf document).

Any ideas on how I can create a brand new pdf file? Thanks in advance.

Answer 1

Have you considered CuteFTP or PDFCreator, both are free. I have sucessfully used PDFCreator with VBA and I have heard that CuteFTP is good.

Answer 2

I think i found the answer. Here is one solution someone at work suggested (if anyone finds it useful, then great).

There is no available method in the AcroEXch class (or set of methods that I know of) to convert a non-pdf file to a pdf file. Instead, you have to use the pdf Distiller to first convert the file to postscript and then you can write to pdf, using the PDFDistiller class. Here's a snippet of the code:

'1. open excel being converted to pdf:
xlReport.activate xlReport.range("a1").select

dim PdfFilePath PdfFilePath = ""

dim PsFilePath PsFilePath = ""

'2. Print Excel file to postscript file xlBook.activesheet.PrintOut , , 1, , "Adobe PDF on Ne01:" ,TRUE, , PsFilePath

Dim oDistiller Set oDistiller = CreateObject("PDFDistiller.PDFDistiller.1")

oDistiller.FileToPDF sPsFilePath, sPdfFilePath, ""

' Close Excel - do not save. 'COMMENTED OUT BELOW 3 LINES FOR DEBUG xlApp.displayalerts=false xlApp.quit set xlApp=nothing

Answer 3

I don't know exactly what your circumstance is and what tools you have access to, but reading your description, it sounds like you simply want to have an Excel file converted for you with a click.

It would be helpful if you had posted whether you have Adobo Acrobat Professional, latest version of Excel, or other converters that are available on the market.

If you have Acrobat Pro installed, your office apps (word, excel, outlook, etc) should already have a "Convert to PDF" button in the toolbars, in combination with Excel command line argument it shouldn't be too hard to cook up a Windows Scheduled Task that periodically convert excel files for you.