Files automatically open when printed to file

**bhastings** · 10-20-2010, 06:32 AM

Good Morning,

I'm brand new here and also brand new to vba as well so please forgive me if my code is pretty poor

I have managed to cobble together a macro which will go through every worksheet in an Excel 2003 workbook and print them all to a file with a unique filename. This works perfectly for me apart from one little thing.

I want to stop it from automatically opening each file after it is saved. Currently what happens is that Windows Picture and Fax Viewer will open for each file after it is saved to the specified location. Thankfully it doesn't open up a new instance of the program for each sheet but it still looks a bit messy and I would love to be able to stop it from happening.
I'm not sure it is even possible to be honest but I thought I would register on here and get some experts views on this matter.

Please see my current code below:-

Please Login or Register  to view this content.

If any of this doesn't make sense or if you can offer suggestions which would further optimise my code then that would be fantastic.

Thanks,

Ben

**TMS** · 10-20-2010, 09:02 AM

I don't know how to control the Print and Fax Viewer action, but ...

Try selecting all the sheets first and printing them together ... at least the program will only open once. For example:

Sheets(Array("Sheet1", "Sheet2", "Sheet3")).PrintPreview

Unless you need individual files, in which case, back to square one :-(

Regards

**bhastings** · 10-20-2010, 10:26 AM

I have spent the day trawling the internet for a solution to this thinking that it was some sort of super complicated piece of code that was required...I was wrong.

All that I had to do was to print one sheet manually to set the print settings.

i.e.

Select an individual Worksheet
Go to File -> Print
Set the Print settings to how you want them in your macro (So in my case I chose Microsoft Office Document Image Writer, one copy, collate etc)
On the Save As dialog box that appears make sure that "View Document Image" is unticked.
Save the file.
Save the spreadsheet and close and reopen it.
Run the Macro
Take joy in the fact that you only wasted a day looking for a complicated solution to a simple problem rather than a week.

Ben