Auto printing excel worksheet

[bromano]

I have an Excel (2007) worksheet that I'd like to have printed every morning automatically without having to manually open Excel and print the sheet.

Is there a way to do this?

//Bill

 

[Joe4]

Here is how I would do it.

1. Use the Macro Recorder to record yourself printing out what you want, and then add one line of code to close the file (ActiveWorkbook.Close).

2. Then, place that code in the Worksheet_Open event procedure in VBA (http://www.cpearson.com/excel/Events.aspx), which runs automatically when the file is opened.

3. Use Windows Scheduler to open this file every morning.

 

[bromano]

Thanks, Joe. Can't wait to give this a try! I'll keep you posted.
//Bill

 

[Joe4]

Great! Post back to this thread if you run into any problems that you need help with.

 

[bromano]

Hi Joe.........

Many thanks. This seems to be working. The only thing I have run into is the workbook doesn't close because it's waiting for a response to "Do you want to save the changes you made to xyz.xls ?

Can I add a do not ask somehow?


//Bill

 

[Joe4]

Yes, there is optional argument to the Close statement regarding whether or not you want to save the changes. You can just use:

ActiveWorkBook.Close False

to not save any changes (or "True" if you do want to).

It seems a little odd, because if you are just opening and printing, there shouldn't be any changes being made during that session. So I am not sure why you would be getting that prompt. In any event, you can handle that as indicated above.

 

[bromano]

This may seem like a ver stupid question... but with the macro as it's written... when I open the file it does as instructed and prints the worksheet ...then closes on me. LOL.

 

[Joe4]

Not stupid at all!
Just hold the Shift key down while you open the file and that will disable that macro from running.

 

[bromano]

Joe: seems to work ok. It may have run into a conflict with another macro in this spreadsheet... so I'm working through that.

Question...Obviously I have to save the sheet (Excel 07 version) as a macroenabled version, but I do have some users that don't have E'07. Can I save as XLS and still have the macro work or, should I create a workaround so the file autosaves as an earlier version.?

//b

 

[Joe4]

I think you may have to save it to the lower version to run it on the other computers.

Save it as an Excel 2003 files (.xls) instead. It might actually be easier to create the file/macro on the "lower" version machine. Just test it out to make sure it works on both.