Excel Workbook won't open until VBA code has finished running

Question

Problem: Workbook won't open until the code has finished running.

Details: I have a loop that required the user to have another file open, in order for the code to continue. This file has to be downloaded from the internet and opened. If the file isn't open a msgbox appears asking the user to open the file. Even though I download the file and open it, it wont open until all of the other code has been executed.

My Thoughts: Maybe there is something like a "Refresh", that updates the current state. I should probably add something to the existing code below that refreshes the excel workbooks and sees if something else has happened.

Code below: (The code is fine and works for what I need, the problem is that the workbook won't open until all the other code is complete):

Private Function WorkbookActive() As Boolean ' Loop until the correct workbook is open Do Until WorkbookActive = True On Error Resume Next If Workbooks("Rapport.csv") Is Nothing Then If MsgBox("[Rapport.csv] - Workbook is currently not open." & vbNewLine & "Please download and open [Rapports.csv] and press OK", vbOKCancel, "Workbook not open") = vbCancel Then Exit Do WorkbookActive = False ElseIf Not Workbooks("Rapport.csv") Is Nothing Then WorkbookActive = True End If Loop End Function

FWIK, VBA use single thread, which your loop will block other action (include open workbook from Excel). To do what you want, you may need to change your approach, ask user for the location of Rapport.csv, then open it in your code, and continue your program. — Prisoner
– Prisoner, Commented Feb 13, 2017 at 8:29
Thanks for the answer, the problem is that I have tried many approaches to this. The main problem is that when the code is running I can't open any other file. — Elias Wick
– Elias Wick, Commented Feb 13, 2017 at 8:42
Have you try to open file in your code? FYR: stackoverflow.com/a/26417509/1050927 — Prisoner
– Prisoner, Commented Feb 13, 2017 at 8:46
That doesn't work either. I've tried using excel to download and open the file, but it still won't open until all of the code is complete. — Elias Wick
– Elias Wick, Commented Feb 13, 2017 at 8:55
can you show the code you open the csv? Inside the loop or outside the loop? — Prisoner
– Prisoner, Commented Feb 13, 2017 at 9:16

A.S.H · Accepted Answer · 2017-02-13 10:36:12Z

All you need is to make your loop "indirect", using Application.Ontime. Rewrite you loop this way:

Sub IndirectLoop() On Error Resume Next Workbooks("Rapport.csv").Activate If Err.Number = 0 Then Exit Sub ' Done! If MsgBox("[Rapport.csv] - Workbook is currently not open." & vbNewLine & _ "Please download and open [Rapports.csv] and press OK", _ vbOKCancel, "Workbook not open") = vbCancel Then _ Exit Sub ' loop cancelled by user ' Pause VBA and reschedule the routine for 3 seconds later ' Control passes here from VBA to excel to open the file by the user' Application.OnTime Now + TimeSerial(0, 0, 3), "IndirectLoop" End Sub

The idea is, instead of looping within your VBA routine (which keeps Excel blocked), reschedule the routine using Application.Ontime. This technique will pass the control from VBA to Excel for some moment (say 3 seconds in the code above), permitting it to do some activity, that is open the file you're waiting for.

Collectives™ on Stack Overflow

Excel Workbook won't open until VBA code has finished running

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related