Question regarding the PrivateSub Workbook_open() macro

[Overkill32]

Hello all

I have a question regarding the macros that run automatically when a file is open. I'm currently having problems with a set of workbook with whom I need to extract data from. Each time I try to open one of the files I need, an error comes up because of 1 line in the PrivateSub Workbook_Open() that makes a reference to another file that doesnt exist anymore. Is there a way i can code my loop so it doesn't run these macros each time i ask it to open the file?

One way I thought could work was to code the file opening with an "hold shift key down" command but I don't if it's possible....

I already tried the Application.AutomationSecurity = msoAutomationSecurityForceDisable and the " On error Resume" Command and they are not working...

Thanks for the help

 

[xenou]

Well, this is tricky. You generally put macros in a workbook open event that you *want* to run when the workbook opens. So in this case you don't want them to run? Is that a blanket statement or merely a result of this line that errors out...

If the former, you probably don't want these macros in the open event - that isn't the place for macros that shouldn't run automatically on open. If the latter you could probably delete the line (no reason to have a macro that references a workbook no longer in existence). You can also handle errors so there's at least no crash:

Private Sub Workbook_Open()

On Error GoTo ErrHandler:
[I]'Macro code here[/I]

ErrHandler:
Msgbox "Error: " & Err.Description '//Comment this line out to continue silently
Resume Next

End Sub

On the whole, I guess I'd say fix the macro that's crashing - it really shouldn't be bugging out every time like that anyway.

 

[Overkill32]

I have to work around that part of the code because unfortunatly i wasn't the one who wrote it in the first place. Also, i wouldn't mind simply erasing that line of code thats linking to a worksheet that doesn't exist but i need to repeat that sequence through about 600 files....

I tried the "On error Goto" Sequence but the error ("run time error" with _Workbook failed) keeps comming up for that line of code.

I also tried using a loop with an open file command while simultaneously holding the shift key down but the loop seems to be ignoring it....

Any other suggestion i could try?

 

[xenou]

I don't understand why we can't just comment out code that doesn't work anyway. This would be simple and effective.

 

[Overkill32]

That would be nice indeed!

I found a way to work around the problem using a direct reference to the cells i needed. That way, the file doesn't load anything since it's not required to open. It's a bit more rough but it gets the job done!

Thanks for the help!