I have a task to copy data from many Exel workbooks, identical in format, and each one has a WORKBOOK_OPEN procedure. Each workbook will be opened in a loop using VBA in my working Excel file, but can I open each workbook without running the code in WORKBOOK_OPEN?
The end result is to combine the #1 worksheet of many workbooks into one worksheet. If I can do this without opening each workbook to do the copying, I would like to learn how this can be done. I am currently opening each workbook in a certain directory and copying and pasting the data into one worksheet.
You can use the 'EnableEvents' property to prevent the Workbooks_Open event from triggering when you open the workbook. You want to re-enable events after the workbook has opened by setting the property to True. Here's an example.
strPath = ThisWorkbook.Path
strFileName = strPath & "\" & "ONOpenTest.xls"
Application.EnableEvents = False
Application.EnableEvents = True
As shades hinted, you can do this without opening the workbooks and bypass the on-open events. The way I know of to do this is by using ADO to retrieve the worksheet data into a Dataset. ADO can be tricky to set up. Opening each workbook will probably be easier for you and can work well.