Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Question Unanswered: Can I Bypass WORKBOOK_OPEN from VBA

    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?

    Thanks.
    Jerry

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Is there a reason to specifically open the workbooks? If not, you needn't concern yourself with it.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    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.
    Jerry

  4. #4
    Join Date
    Feb 2004
    Posts
    533
    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.
    Code:
    Sub OpenWBTest()
      strPath = ThisWorkbook.Path
      strFileName = strPath & "\" & "ONOpenTest.xls"
      
      Application.EnableEvents = False
       Workbooks.Open (strFileName)
      Application.EnableEvents = True
    
    End Sub
    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.
    ~

    Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •