CODEWITHSUNDEEP
vbaexcel
dwirony
dwirony

Reputation: 5450

How to pull processing % from opening workbook for a status bar update?

So I have a few large macros that open/close large workbooks, and unfortunately these workbooks take a little time to open/load/calculate. When I open them manually, I can see the opening percentage on the initial screen:

enter image description here

And the calculation % on the status bar of the file here:

Processing picture

What I'm wondering is how I can grab these percentages and relay it to my main workbook's status bar to something of the effect of:

Application.DisplayStatusBar = True
Application.StatusBar = "Opening production file... " & Workbooks("Book1").LoadingPercentage & " of the way complete..."

I'm a big fan of updating my users through status bars, so when the status bar just says Opening production file... and hangs for 20 seconds, they might grow concerned that the program froze. I could just have a counter that increments every second, but that would be inconsistent and wouldn't necessarily prove that the program hasn't froze/crashed.

Is there a way that I can grab this number from an opening workbook? Or is there any kind of clever workaround? I skimmed through the properties of Workbooks("Book1"). and didn't see anything I could use.

Upvotes: 0

Views: 792

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

You can't.

This instruction runs synchronously:

Set wb = Workbooks.Open(path)

The instruction after that will only run after the file has finished loading and the wb object reference is set.

And as far as I know there's no way to open a workbook asynchronously in Excel. If there was, then the API could have exposed events such as OnOpenProgress or similar, which you could have handled, say, like this:

Private WithEvents app As Excel.Application

'...

Private Sub app_OnOpenProgress(ByVal path As String, ByVal percentComplete As Double, ByRef Cancel As Boolean)
    Application.StatusBar = "Opening " & path & "... (" & Format(percentComplete, "0.0%") & ")"
End Sub

But that's not something that's currently possible AFAIK.

The only way I can see this done, is by implementing your own Workbooks.Open method that returns a Excel.Workbook object. Needless to say the cost/benefits ratio is astronomical (and not in favor of the benefits).

The best solution is to suck it up and do everything possible to minimize workbook file size, mostly by storing data in a database, not in Excel workbooks.

Upvotes: 2

Related Questions