Hi all
I need saving from myself! I have a subroutine in Access which opens four Excel sheets and does extensive updating. The process takes 4 to 5 minutes. Before the process starts in earnest I check to see if Excel is open; if it is I stop the process and request that the user shuts Excel down. The problem with having Excel open is that the macro can end up picking a workbook which is open and not part of the routine, or the user continues to work on their open sheet, which effects commands such as ActiveSheet etc..
However, while the macro runs I often check my mail, which normally has Excel workbooks attached. Without fail I end up openning the workbook and crashing the subroutine. Is there a way to lock excel while I process, or make my subrouitne modal to stop me trying to run other programs
Making process modal
leo_asp
There's not really a way to do this -- it defeats the whole purpose of multitasking, which is fundamental to Windows!
However, what you can do is get rid of code that uses ActiveSheet and similar context-dependent objects. You can declare a variable of type Worksheet, set it just once for each worksheet, and not worry about it changing no matter what else is going on in that instance of Excel (except, I guess, if you close that workbook!)
ActiveSheet, ActiveWorkbook, etc. are convenient, but they're not well suited to applications like yours. In my experience, whenever you've got code that could possibly be running while other workbooks are open, you're setting yourself up for problems by using them.