Print to pdf and save without prompts

G

Guest

I have a macro which takes a selection of each page in a workbook. (specific
to each page) This is then printed to pdf and at the moment I have to save
each filename as I go along. Which is obviously time consuming. Is there a
way of naming these without a prompt coming up automatically and saving it in
a new folder in a given location?

Cheers in advance

Jeff
 
G

Guest

This is my somewhat untidy code so far....

Sheets("Total").Select
Range("A1:M40").Select
Application.ActivePrinter = "Adobe PDF on Ne00:"
Selection.PrintOut Copies:=1, ActivePrinter:="Adobe PDF on Ne00:",
Collate _
:=True
Range("N4:AC38").Select
Selection.PrintOut Copies:=1, Collate:=True
Sheets("Sanction").Select
ActiveSheet.ChartObjects("Chart 2").Activate
Application.Run "OnActivate"
Windows("FSPI Main.xls:1").LargeScroll ToRight:=-1
ActiveWindow.Visible = False
Windows("FSPI Main.xls:1").Activate
Range("A1:M40").Select
Application.Run "OnActivate"
Selection.PrintOut Copies:=1, Collate:=True
Range("N1:AC38").Select
Selection.PrintOut Copies:=1, Collate:=True
Sheets("Burglary").Select
Application.Run "OnActivate"
Range("A1:M41").Select
Selection.PrintOut Copies:=1, Collate:=True
Range("N1:AC39").Select
Selection.PrintOut Copies:=1, Collate:=True
Sheets("Violent").Select
Application.Run "OnActivate"
Range("A1:M39").Select
Selection.PrintOut Copies:=1, Collate:=True
Range("N1:AC40").Select
Selection.PrintOut Copies:=1, Collate:=True
Sheets("VCPP").Select
Application.Run "OnActivate"
Range("A1:M41").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.PrintOut Copies:=1, Collate:=True
Range("N7:AC41").Select
Selection.PrintOut Copies:=1, Collate:=True
Sheets("Robbery_Source").Select
Application.Run "OnActivate"
Sheets("Robbery").Select
Application.Run "OnActivate"
Range("A1:M40").Select
Range("M40").Activate
Selection.Copy
Application.CutCopyMode = False
Selection.PrintOut Copies:=1, Collate:=True
Range("N3:AC37").Select
Selection.PrintOut Copies:=1, Collate:=True
Range("X39").Select
Sheets("Vehicle_Source").Select
Application.Run "OnActivate"
Sheets("Vehicle").Select
Application.Run "OnActivate"
Range("A1:M41").Select
Selection.PrintOut Copies:=1, Collate:=True
Range("N9").Select
ActiveWindow.SmallScroll Down:=-12
Range("N2:AC40").Select
ActiveWindow.SmallScroll Down:=-12
Selection.PrintOut Copies:=1, Collate:=True
Sheets("ClassA_Source").Select
Application.Run "OnActivate"
Sheets("ClassA").Select
Application.Run "OnActivate"
Range("A1:M41").Select
Selection.PrintOut Copies:=1, Collate:=True
Sheets("Criminal_Damage_Source").Select
Application.Run "OnActivate"
Sheets("Criminal_Damage").Select
Application.Run "OnActivate"
Range("A1:M40").Select
ActiveWindow.SmallScroll Down:=6
Range("A1:M41").Select
Range("M41").Activate
Selection.PrintOut Copies:=1, Collate:=True
Sheets("Criminal_Damage").Select
Range("N3:AC39").Select
Selection.PrintOut Copies:=1, Collate:=True
Sheets("Incidents source").Select
Application.Run "OnActivate"
Sheets("Incidents").Select
Application.Run "OnActivate"
Range("A1:M45").Select
Selection.PrintOut Copies:=1, Collate:=True
Range("N2:AD41").Select
Selection.PrintOut Copies:=1, Collate:=True
Sheets("CHC Presentation").Select
Application.Run "OnActivate"
Range("A1:M63").Select
Selection.PrintOut Copies:=1, Collate:=True
Sheets("CHC + CRIB").Select
Application.Run "OnActivate"
Sheets("CHCsource").Select
Application.Run "OnActivate"
Sheets("Emergency Calls").Select
Application.Run "OnActivate"
Range("A1:M39").Select
Selection.PrintOut Copies:=1, Collate:=True
Range("N4:AD39").Select
Selection.PrintOut Copies:=1, Collate:=True
Sheets("999 History").Select
Application.Run "OnActivate"
Sheets("Searches Presentation").Select
Application.Run "OnActivate"
Range("A1:M47").Select
Selection.PrintOut Copies:=1, Collate:=True
Range("N3:AD40").Select
Selection.PrintOut Copies:=1, Collate:=True
Sheets("PACE Searches History").Select
Application.Run "OnActivate"
Sheets("Stops Presentation").Select
Application.Run "OnActivate"
Range("A1:M47").Select
Selection.PrintOut Copies:=1, Collate:=True
ActiveWindow.SmallScroll Down:=-21
Range("N9:AD43").Select
Range("AD43").Activate
Selection.PrintOut Copies:=1, Collate:=True
Sheets("PACE Stops History").Select
Application.Run "OnActivate"
ActiveWindow.SmallScroll ToRight:=30
ActiveWindow.SmallScroll Down:=0
Sheets("Adult Casualties").Select
Application.Run "OnActivate"
Range("A1:M46").Select
Selection.PrintOut Copies:=1, Collate:=True
Range("N1:AD47").Select
ActiveWindow.SmallScroll Down:=0
Range("N2:AD48").Select
ActiveWindow.SmallScroll Down:=-6
Selection.PrintOut Copies:=1, Collate:=True
Sheets("Adult RTC Histories").Select
Application.Run "OnActivate"
Sheets("Child Casualties").Select
ActiveSheet.ChartObjects("Chart 1").Activate
Application.Run "OnActivate"
ActiveWindow.Visible = False
Windows("FSPI Main.xls:1").Activate
Range("A1:M46").Select
Application.Run "OnActivate"
Selection.PrintOut Copies:=1, Collate:=True
Range("N3:AC46").Select
Range("AC46").Activate
Selection.PrintOut Copies:=1, Collate:=True
Sheets("Complaints").Select
ActiveSheet.ChartObjects("Chart 1").Activate
Application.Run "OnActivate"
ActiveWindow.Visible = False
Windows("FSPI Main.xls:1").Activate
Range("A1:M51").Select
Application.Run "OnActivate"
Selection.PrintOut Copies:=1, Collate:=True
ActiveWindow.SmallScroll Down:=-30
Range("N1:AB39").Select
Selection.PrintOut Copies:=1, Collate:=True
Sheets("Complaints Histories").Select
Application.Run "OnActivate"
Sheets("Priorities").Select
Application.Run "OnActivate"
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Update Procedure").Select
Application.Run "OnActivate"
End Sub
 
G

Guest

Hey Jeff,
I found this discussion between RC and Darcy from July last year in Excel
General Questions with the header “Macro printing to a file†and I managed to
solve the PDF saving problem. The original suggestion was to save numerous
files, I’m saving only one at a time. I’m running Excel 2003 and Acrobat 6.0.
The trick to get rid of the saving prompt was to change the printer
properties in the Control Panel – Printers and Faxes – Adobe PDF – Properties
– Printing defaults.
You cannot set these properties permanently within the properties in Excel,
as soon you have reset to your Standard Printer the options are selected
again.
Clear the selections for “Prompt for Adobe PDF filename†AND “Do not send
fonts to “Adobe PDFâ€â€. Both are important, the latter for the driver to make
the Postscript files that’ll be deleted after conversion to PDF-files.
I have a shorter solution for 1 sheet only but if you want it I can post it
too!
Best regards
Mats
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro for all Excel Workbooks to print to PDF 2
Save as PDF not working on a domained Windows 7/Vista box 2
automate save as of excel-report to pdf-document. 1
close/open workbook without prompts 5
Word Macro to insert text and save as PDF 4
Save as PDF 2
Print as pdf and save as xls with filename from a datafield 1
Print to PDF 2

Top