Mysterious Hanging upon opening Workbook

CPGDeveloper

Board Regular
Joined
Oct 8, 2008
Messages
160
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.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,179
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Oct 8, 2008
Messages
160
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

Well-known Member
Joined
May 25, 2013
Messages
894
Perhaps someone changed to the option to request to update links?
The opposite of what you have just done?
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
479
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
At the risk of someone resetting it and your code breaking again, you might want to force the options in your code...


Specifically this suggestion:

VBA Code:
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.)
 
Solution

CPGDeveloper

Board Regular
Joined
Oct 8, 2008
Messages
160
That's a great suggestion Jon. Thanks...I didn't realize there was a way to do that.
 

Forum statistics

Threads
1,136,640
Messages
5,676,942
Members
419,660
Latest member
Fred Cailloux

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top