Suppress open events when using Workbooks.Open

[dircur]

I am not a programmer but.
I have built a sub that prompts the user for a folder then opens every workbook in the folder 1 at a time to get stats on the contents of each workbook. Worked like a dam until I ran into an unexpected bug. Some of the users built on open events in their workbooks. ... UHG so you may be able to guess what happened.
Right now my routine inventories workbooks to get formula counts, cell counts, most complex formula, highest value... it does this by looping throught the sheets and the cells. If there is a way of obtaining those stats without opening the workbook I may need to rethink a lot of my work.
My hope is that there is a way to suppress the code in the target workbook I open through workbooks.open
please help.
Thanks

 

[dircur]

bump.
I haven't gotten any further. It's got me down. I did a ton of test runs on last years folder set so i was feeling pretty confident.

 

[dircur]

Success... I think
and the solution.
Do not touch anything while the routine is running?
The routine always died when hitting a xlsm so i dont know how that all relates but backing away slowly while it runs seems to have let it run.
Does that make any sense. To be clear I wasn't doing any excel work while this was going on but I dont know how else to explain it. I didn't change anything I was letting it run to see if I could identify any patterns in what it evalauted.

 

[Zack Barresse]

Hmm, strange indeed. One suggestion would be instead of calling the Application calls (i.e. EnableEvents, ScreenUpdating) intermitently throughout the code, call it once in the beginning and once at the end. I use this code..

Public Sub TOGGLEEVENTS(ByVal blnState As Boolean)
'Originally written by Zack Barresse
    With Application
        .DisplayAlerts = blnState
        .EnableEvents = blnState
        .ScreenUpdating = blnState
        If blnState Then .CutCopyMode = False
        If blnState Then .StatusBar = False
    End With
End Sub

Then in your code, it's just two lines to hit the majority of issues...

Sub YourSubRoutine()
    Call TOGGLEEVENTS(False)
    '... your code here
    '... more of your code
    Call TOGGLEEVENTS(True)
End Sub

HTH

 

[Zack Barresse]

Also, is the xlsm password protected? Generally be default, when opening an xlsm file with password protection macros are automatically disabled in that workbook.

 

[dircur]

Thanks All;<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I will try adding the code above.<o></o>
The hardest problems are the ones you think you understand.<o></o>
I have narrowed it down and the suppress auto_run or on open event may have been a bit of a red herring. In order to determine if a workbook had vba code I was using this bit of code twice. Its a bit wonky as I thought it would count only the code panes in the open workbook but as it turns out it counts the code panes in all open workbook. In order for that bit of code to work I need to turn on an excel option "Trust access to the VBA project Object model".<o></o>
When I remove those bits of code, and turn off the "trust access to th..." in the options, my code completes. <o></o>
So I think this bit was killing the routine while the target workbook was open and there by turning off the event suppression. In turn the target workbook would run its routine as it was left open. My perception is that the target workbook code is running during my routine when in fact my routine dies before the target workbook runs.
Stranger still is the fact that this seems to be linked to only xlsm files. The older xls files with code don’t appear to trigger the failure. Regardless I will abandon the use of that test. I will try to find a different way.<o></o>

Oh yeah and Toggle events. That’s nice I like that a lot, Thanks Zack.<o></o>
And thanks to Norie and Chris as well. <o></o>
<o></o>
For now I have my routine working I will look for alternative ways of determining if the workbook I am evaluating has code. Thanks again for helping me troubleshoot.<o></o>