Macro to print entire workbook to PDF

[sal11235]

originally posted at Ozgrid yesterday but no avail
http://www.ozgrid.com/forum/showthread.php?t=152918

really need an answer to this now so I'm posting here.

Morning guvnors

problem : I'm trying to automate the task of converting an entire Excel workbook to PDF but having recorded the action I still get a prompt which I don't want (that's quite poetic isnt it, prompt, want)

In native Excel I would just say

FILE ---> PRINT ---> change the printer to "Adobe Pdf"---> print what: Entire Workbook.

The following code does all this perfectly, but the macro recorder doesn't seem to capture Excel prompting me for the destination to save the file. (I don't want Excel to prompt me as the macro will be part of a larger piece of code that goes into each file in a folder and converts it to PDF so the user can leave it and do something else).

I am happy for the PDF to save in the location that Excel suggests so just want to click "Yes". But not sure how to invoke this part of the code. Here is the code I already have (but it doesn't get me past the yes/no prompt)

ActiveWorkbook.PrintOut Copies:=1, Collate:=True

Any suggestions please!


Maybe even a piece of code that will just click "Yes" to whatever prompt is on the screen?

 

[Ralajer]

Check out this forum posting by "macropod" at http://www.tek-tips.com/viewthread.cfm?qid=1249943 I've tested it and it works. It uses Acrobat Distiller to create the file so you will have to add a reference and change a setting but that all explained in the post. It appears to be completed automated so change the default save location in the code.

Hope this helps.

 

[sal11235]

thank you Rob :=). Got a couple of tips from Ozgrid as well so I'll try those as well.