0

My Excel file blocks other Excels files from opening.

I have the below code

Public Sub Workbook_Open() Application.OnTime Now + TimeValue("00:00:03"), "CommonWorkBook.prepareSheets" End Sub

In PrepareSheets Macro, I take data from a txt file every 60 seconds.

The code is as follows

Do While 1 = 1 ' Do Something pause 60 Loop

After opening my .xlsm file, I am unable to open other .xlsm files.

If I close my .xlsm file, other files gets opened.

Improve this question
3
  • What is the code for pause? Commented Dec 22, 2015 at 12:15
  • Rather than fixing your (currently) incomplete code you might want to look at the following working solution: stackoverflow.com/questions/17924542/… Commented Dec 22, 2015 at 12:38
  • Public Function pause(Optional iSeconds As Integer = 20) Dim dtEnd As Date dtEnd = Now + TimeSerial(0, 0, iSeconds) Do While dtEnd > Now: DoEvents: Loop End Function Commented Dec 22, 2015 at 13:15

1 Answer 1

Reset to default
0

The problem is that when Pause is running it is still running. If you look in the task manager while pause 60 is being executed then you will see that it is using up almost all CPU from one of your cores:

enter image description here

Even if you could fix it -- do you really want to hog so much of your system's resources?

VBA is old technology -- only minor changes since the late 1990s. It is single-threaded. Only one single-threaded instance of the interpreter for each instance of the Excel application. The running code is what is preventing another macro-enabled workbook from opening.

There really is no easy way to do multithreading in Excel (but see this question). You should look instead for a solution involving Application.OnTime. Don't pause for 60 seconds -- schedule for 60 seconds in the future. See this (as suggested by @Ralph) for more details.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for the suggestion. I will try to implement the same

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.