Excel and printing to PDF Automatically 1

[Woodyuk]

I have an excel sheet, with a macro which then prints it. I want this to print to a PDF. Im using adobe acrobat 6 professional. Now I have added to this excel sheet the following command in a macro:

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

This then uses the default printer which is adobe.
However it asks where I want to save, and what I want to call it.

I just need it to be put int the same location, and give it the name of the excel sheet, and over write the old PDF as this runs every day

The reason im using this in this way is I then email the pdf for people to view and print out.

I know I could just send the excel sheet, but if someone wants to print this out, it doesnt remember the print layout.

Any suggestions or help would be welcomed

 

[monkeybarfan]

Download the free open office software and use Calc, which can do it as one of the standard options.

 

[Woodyuk]

Thanks for that, I will have a look. Did look at this a few years ago for something else but dont think certain sheets ran due to macros in them. Will check it out

 

[Woodyuk]

Right tried this, and does the same as excel it seems. Also didnt seem to want to know about macros written in excel.

Does anyone know of another way to do this?

 

[TonyJollans]

I don't have Acrobat 6 on any machine at the moment to check but there is an Acrobat setting called something like "prompt for pdf filename". I don't have Acrobat 7 in front of me either but in Acrobat 7 it is reachable from the Adobe PDF Menu which the Acrobat AddIn adds to Office applications; in Acrobat 6 I think you have to find it somewhere within Acrobat itself and it is not immediately obvious. Sorry that's not a lot of help but perhaps it's enough encouragement for you to know that it is there - somewhere

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[Woodyuk]

Thank you for that, how obivous was that. Saw the option, and sorted.

Just had to get it to use the sendkey functions to send it too pdf, instead of create. Many thanks for your help