Workbooks.open fails when run from macro

Excel 2019 Windows 10

Have a large wb that takes maybe 6 seconds to unpack itself. Been running it from a macro for years under excel 2007 without much issue

Now, it randomly (but MOST of the time) simply fails to complete loading and hangs Excel when run that way (not responding, has to be force closed)

When the same file is run from the Excel file manager, no problem. opens with no errors

called with :
Workbooks.Open filespec

simple call is used, no vars, no objects.

Tried a bunch of things, all seem to fail. Funny thing is, it sometimes works, then the next day will fail again with the same code.

The workbook.open event is not firing when it fails, as I have a 'speak' line in there that would tell me it had.

So pretty sure the workbook itself is failing to complete load when called from a macro in personal.xls

But no problem loaded normally

Hard to go through all the ideas posted here, may have to hire help

No one has a clue ?

Originally Posted by jdop

Hard to go through all the ideas posted here, . . .

Keep up the sarcasm, and you can hope for double the responses.

Originally Posted by jdop

. . .No one has a clue ?

You haven't provided any details the rest of us can check. If your workbook is too sensitive to share, try putting Application.EnableEvents = False just before the Workbooks.Open filespec. Does that allow the workbook to open without Excel hanging? If so, the problem would seem to be Excel needing to run the newly opened workbook's Workbook_Open while the preexisting workbook's macro hasn't completed. FWIW, I've had problems with macros (not event handlers) making changes to cells which cause event handler to run.

All your points are well taken, but I am an experienced programmer, and the info I gave is actually all there is to it.

Yes, the target WB is macro enabled.

I tried the suggestion Application.EnableEvents = False

Doing this prevents Excel from hanging, but the workbook_open event doesn't fire. Application.EnableEvents = True set after the Open line is too late for that as well.

So , we know then its the workbook_open even that fails , which kind of gets us back to square one.

Now, in all this experimenting , I can now open the workbook repeatedly (this after closing Excel completely) without error.

Don't know why this is, but most likely in a day, it will fail to open again . Same file. Yeah, I know its a head scratcher.

1) Workbook always opens when done from Excel file picker
2) Workbook sometimes opens when done from macro .
3) Workbook hangs Excel (not responding) all other times.

Originally Posted by jdop

. . . I tried the suggestion Application.EnableEvents = False Doing this prevents Excel from hanging, but the workbook_open
event doesn't fire. . . .

If this other workbook would commonly be opened by other workbooks, put most of the code from that other workbook's Workbook_Open event handler into
a macro (X) in a general module with that macro NOT marked Private. Have that other workbook's Workbook_Open event handler call X in that workbook,
and call X AS THE LAST STATEMENT in the macro opening that other workbook. If that doesn't work, maybe use Application.OnTime to launch to run X after
the macro which opened the other macro has completed. If that also doesn't work, I'm stumped.

Code cant be moved outside of workbook. All the calling macro does is look for the last highest version number to run.

No, there's no shift key or anything like it causing this .

I didn't even reboot, I put the computer in sleep mode overnight

Just tried running the same workbook which worked 3 or 4 times in a row last night (using the macro) and it FAILS AGAIN, as expected.

Very very odd. Excel has some strange ways, this is yet another one of them

Fortunately, its easy to work around, but would be up the creek if it wasnt

Not likely to be a help here. Usually nothing VBA related will show in the log. I'll look anyhow

Originally Posted by jdop

Code cant be moved outside of workbook. . . .

Not what I suggested. Instead of

ThisWorkbook code module

Please Login or Register  to view this content.

use

ThisWorkbook code module

Please Login or Register  to view this content.

General code module IN SAME WORKBOOK

Please Login or Register  to view this content.

When opened directly, Workbook_Open calls StartUpMacro. When opened by another macro in another workbook with events disabled, that other macro could call the newly opened workbook's StartUpMacro procedure.

I already run the opening code via a timed delay, located in the open event. so an a

Trying your method, I get a compile error in the calling code, if the target workbook is not already open.

In trying the last suggestion, I removed the timed delay, and went with the main init code in the workbook_open event .

Now the file is loading BUT, its throwing an error 438 at the workbook.open line. "Object doesnt support property or method"

Never gets to the workbook_open event in the newly opened wb.

Not sure what to make of this as the only object is the newly opened file, and the file throws no error when opened alone .

All I am passing to the workbook.open method is a simple filespec, no other parms.

an ON ERROR RESUME NEXT does not prevent the load from stopping

dunno, been doing some intense debugging trying to narrow down the exact cause, but the built in error handler pretty much stinks for this .

cant even access the vba properties of the workbook until the calling macro completes or is ENDED . no way to have VBA tell me what line is offending. set BREAK ON ALL ERRORS , but still breaks only on the workbook.open line , not in the presumed problem area

Originally Posted by jdop

. . . Now the file is loading BUT, its throwing an error 438 at the workbook.open line. "Object doesnt support property or method" . . .

Workbook.Open doesn't exist. Do you mean Workbooks.Open?

Sadly it all works properly except for the 438 error when run from a macro.

This is a non trivial project, with a lot going on, so I suspect its some object thats not in proper focus when the wb is called this way.

I tried tracking down the offending line using audio, since VBA is like those puzzle games , where it defies you at ever chance. God forbid it would throw an error at the line it doesn't like

Anyone know how to simulate the FILE/OPEN that you do with the keyboard, since the Application.FileDialog can't hack it.

I have a feeling that MIGHT work past this .

Originally Posted by jdop

Anyone know how to simulate the FILE/OPEN that you do with the keyboard, . . .

See Application.SendKeys.

Im well aware of sendkeys, and best practice is to avoid that if possible

must be a windows hook into the file open routine

Originally Posted by jdop

Im well aware of sendkeys, and best practice is to avoid that if possible

We're well into looking for any & all alternatives at this point, no? In general it may be a good idea to avoid SendKeys, but you may have reached the point at which you have to consider using it, no?

must be a windows hook into the file open routine

Assuming you want to open the other workbook in the SAME Excel instance, and you're adamantly opposed to using SendKeys, you may have to consider using a VBScript file to call GetObject to get a reference to an active Excel session, then with xlapp set to the resulting Excel application object,

xlapp.Workbooks.Open your_pathname_here

where your_pathname_here would be either hard-coded or read from commandline arguments. Call that VBScript file using VBA's Shell statement, which runs other processes asynchronously.

Originally Posted by rorya

It has an Execute method to actually open the file.

Where do you see an Execute for Application.FileDialog ??

No need to use sendkeys, easy enough to open the File dialog , which as expected works without crashing Excel.

No easy way to insert the filename in the filespec box (sendkeys does not work in this object), so have to click on it manually

The whole problem caused by Excel weirdness and focus issues. Getting errors where VBA cant find name ranges (when opened via macro) , then magically it can .

I got big file sometimes take long time to open . Rarely also crash does on open . Also with Worbooks.Open sometimes crashes

Funny is, if workbook is closed and I use Appication.Run to start a macro in it,
it opens quicker then also and so far never crashed..