MrExcel Publishing
Your One Stop for Excel Tips & Solutions

opening a workbook with a macro without starting up the macro's of the new workbook


Posted by Luke on August 13, 2001 6:17 AM

I have tried to use all the tools I can find within the help files in excel97 and within a Book on VBA for excel2000 but somehow I cannot open a workbook from within an excel97 VBA macro without also activating the macro's in that workbook (workbook_open() sheet_activate() and such macro's within 'this workbook'). Even more, Excel does not even ask if macro's should be opened. I would like to do this so that I can update an older workbook laced with macro's with a newer version, or actually, import most or all of the worksheets out of another workbook on disk without being bothered with the macro's. Does anyone have the answer for me. The extension editable = true/false within the open command does not want to do the job. Best would be a command like "open('***') _ nomacro's" or "_deactivate macro's"

So, I should like to either open a workbook without activating its macro's or import worksheets from an unopened workbook.

If anyone can help I'd be gratefull


Posted by Russell Hauf on August 13, 2001 10:03 AM

Try EnableEvents. Something like this:

Application.EnableEvents = False
Workbooks.Open "C:\OPENTEST.XLS"
Application.EnableEvents = True

Hope this helps,

Russell

Posted by Luke on August 16, 2001 2:43 PM

Thanks,

Sometimes the answer you're looking for is so close at hand and so simple. I have used the command EnableEvents for other purposes but never thought of using it in this context. It worked like a charm, and for a number of other problems, for which I created catch loops, as well. I think I have been able to speed up the program by 20 or 30 % by illiminating the catch loops.