PDA

View Full Version : Excel to PDF



PSL
09-30-2009, 03:22 AM
Am Trying to write a code to print all excel files in a folder to PDF.

A simple record shows

Sub Macro1()


Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9")).Select
Sheets("1").Activate
Application.ActivePrinter = "Adobe PDF on Ne00:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveCell.Offset(7, 10).Range("A1").Select
End Sub


I could write a code so as to run the same for every excel file in a folder. However,

1. Each time, excel would prompt "Save as". Can it be set as default as the excel file name?
2. Also have to turn off "View PDF Results" for each file


Have tried to look up on the net. Can't seem to find anything.
Thanks in advance

cheers,

PSL

mdmackillop
09-30-2009, 10:29 AM
These would need to be set in Adobe. I've tried such printing in other PDF print programmes with mixed success and unexpected results. I've no access to Adobe PDF.

Dr.K
09-30-2009, 01:01 PM
You can do all of that with Registry Keys, but its largely undocumented.

You can even print to PDF silently, but that requires editing binary Registry Keys, and so is a little tricky from VBA.

Ask yourself how badly you need this feature; how deep down the rabbit hole are you willing to go? I spent weeks developing my PDF printing code.