I have Adobe Acrobat 6.0 Prof and Microsoft Office Prof Ed 2003, with AcrobatPDFMaker installed in Excel. I am an end-user, not a developer, though I own a big, fat book on Using Excel VBA, and also am working through Wrox's Beginning VB.NET 2003. On the other hand, I have previously written a good amount of VB code to automate stuff for myself, and what I am trying to do OUGHT to be a piece of cake!

Anyway, I want to write a procedure in VBA for Excel which 'prints' a worksheet to the PDFMaker, giving it a name different from the Excel worksheet itself. I then want to have the code launch Acrobat (if it hasn't launched already via PDFMaker), print the PDF file to my trusty HP printer, and then return to Excel. (And, yes, I have added the appropriate references under the Tools menu in Excel's VB Editor.)

I have struggled with this nut for a good bit but am not succeeding in even getting Acrobat or PDFMaker to launch. (Grrrrrrrrr.....) If someone could even point me to a website or book that can walk me through programming Acrobat and PDFMaker with VB I would be ecstatic. I have looked high and low and have found nothing. Help!!!



ArtDecade

__________________
ArtDecade

What your trying to do is by no means "simple". Why? Adobe made it so. It's against the license of Adobe PDFMaker to use it programatically (I don't think it's changedm at least). You must be a logged-in user printing yourself from a program.

As a result, they made it hard to work with programatically- this is by design. So hard, I have never been successful in using an adobe product as the real-time generator of PDF files. I have always had to go to products designed to be developed against - to do exactly the kind of thing your talking about. A search on google should turn up a few possibilities for you.



Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee

I had a similar problem in that I needed to create a lot of reports in Access 2000 in a single run and issue them in PDF format. I wrote a routine to do this for me, whilst a little clunky, it worked well and saved me HEAPS of time and work.

The routine uses the fact that the PDFWriter is a "printer", by setting it as the default printer I could simply use the built in command to print the report and have it refer to PDFWriter. When the DoCmd.OpenReport is executed, the PDF Dialog box will appear for the filename, the SendKeys can be setup (prior to issuing the DoCmd.OpenReport command) to issue these keystrokes for you as in the following lines of code...

strLvSendKeys = savename & "~"
DoEvents
SendKeys (strLvSendKeys)
DoCmd.OpenReport vReportType2, acViewNormal

Step by Step
1. You would enclose the above in a loop to loop through the filenames programmatically
2. Setup the "savename"
3. Setup SendKeys variable...a tilde at the end signifies hitting the ENTER key
4. perform a DOEVENTS to ensure nothing interferes...seems to work better
5. issue the SenKeys you previously setup
6. Issue the command to print the file/report

As soon as the dialog box opens, the keystrokes in SendKeys will be "typed", the tilde will issue the command to print and the PDF will be created using the filename setup.

I found it best to issue the first report outside the loop, by NOT implementing the tilde caused it to open the dialog box, issue the keystrokes but not execute it, this way I could ensure the save directory was correct, all subsequent reports were issued within the loop with the tilde added.

Hope this helps


Tom