Mysterious Hanging upon opening Workbook

[CPGDeveloper]

Hello~

This is driving me insane. Any help would be greatly appreciated.

I have an MS Access Application that runs a relatively complex process of extracting data from certain Excel workbooks, performing several calculations, and then populating the results of the calculations in another workbook. It loops through 20 times to do this for 20 sets of data.

Often the code works perfectly -- from beginning to end without issue. Sometimes, it will hang on a particular line...no error, it just hangs -- line of code is this:

Set dwk = dxl.Workbooks.Open(cfile)

dwk being defined as an Excel workbook, and dxl as an instance of excel that is already open. cfile is the full path and name of the workbook.

What is so mysterious is how inconsistent this is -- sometimes it works, sometimes it doesn't. I'm working on this on a Saturday -- there is nobody in these workbooks or using these workbooks.

I tried to close and re-open that instance of excel before I execute this code, thinking that might somehow prevent this, but it did not seem to make a difference.

I also thought that some of the workbooks have links within them, and a pop-up appears every time when one opens those workbooks about updating links -- and that perhaps this was causing issues. But if that was an issue, I don't understand why it wouldn't happen every time.

I've searched this forum and others for folks experiencing similar issues -- there are a few posts here or there, but nothing seems to account for this. I'm at a loss. Again, any help would be greatly appreciated. Thanks.

 

[Micron]

If you're not opening visible, I would try doing so and when it hangs try switching screens to that workbook to check if there is a hold due to an input prompt or message box. Your description sounds like code is paused, like when an application or form is modal and waiting for a response or to be closed. Would also consider outputting the workbook path so that you can read which one it's hanging on - assuming you're halting the code. If not then in this output you could write the system time and path on open, run the process loop and then write the time again as another value. You could then see the start and end time of the loop for each file.

Without seeing your code, cannot say if an instance of Excel is open and causing an issue (maybe look at Task Manager) or if you've turned off warnings and an unexpected error is causing a hang-up. Even then it's not likely that such an issue would be overly evident. As for why this would be random, who knows? Perhaps someone or some other process affects a file so that Excel prompts for recalculation or updating links next time it opens. At least if you catalogue the events and times you'll know which file(s) it is.

 

[CPGDeveloper]

Hi Micron,

Thanks for the tips -- I believe I figured out what's going on -- if this is helpful for anyone else who is having this issue:

This process is opening and populating approximately 20 different workbooks, and I realize almost all of them have links to outside workbooks. So if I were to open any of them manually, the 'Update Links' message appears, and the user must choose whether or not to update the links.

Disabling this message on all the workbooks in question seems to have solved the problem. And the process runs considerably faster. I have been able to run the process 5 times in a row without issue, so fingers crossed that it is solved.

I do not understand why previously I was able to run the process successfully, and sometimes I wasn't. That's very mysterious to me, but regardless, this seems to have fixed the issue.

Thanks Again.

 

[welshgasman]

Perhaps someone changed to the option to request to update links?
The opposite of what you have just done?

 

[JonXL]

At the risk of someone resetting it and your code breaking again, you might want to force the options in your code...

How to suppress Update Links warning?

I'm trying to write a script that opens many Excel files. I keep getting the prompt: This workbook contains links to other data sources. I want to keep this message from appearing, so that my sc...

stackoverflow.com

Specifically this suggestion:

Application.AskToUpdateLinks = False
Application.DisplayAlerts = False
Application.Workbooks.Open Filename:="C:\Book1withLinkToBook2.xlsx"
Application.DisplayAlerts = True
Application.AskToUpdateLinks = True

(Note: I didn't try this.)

 

[CPGDeveloper]

That's a great suggestion Jon. Thanks...I didn't realize there was a way to do that.