other events whilst saving...

[sykes]

Hi Folks

Does anyone know if it's possible to use "ActiveWorkbook.Save" then have a loop running until the workbook's finished saving?
I'm trying to design a "Saving indicator" - a bit like a progress bar but with dots appearing in a line.
The code below works well. It's called from a userform (userform4).
On the userform are 3 labels.
One ("mess") contains the caption - either "Please wait - saving workbook", or "Workbook saved."
The other 2 are laid one on top of the other. Both have the same background colour as the userform. The bottom one contains a row of dots. As the loop progresses it moves the top one ("waiting") to the right and slowly reveals the line of dots.
When the loop's been through 20 iterations it re-sets everything and starts again.
At the moment it does this five times (the outer loop) then stops.

Sub clrout()
Dim i As Integer, i2 As Integer
With UserForm4
    .mess.Caption = "Please wait - saving workbook"
    .mess.Width = 174
    .Label35.Left = .mess.Left + .mess.Width
    .waiting.Left = .Label35.Left
End With
wb_saving = True
i = 0
i2 = 0
Do Until i2 = 5
    Do Until i = 20
      Start = Timer
        Do Until Timer > Start + 0.15
        Loop
         UserForm4.waiting.Left = UserForm4.waiting.Left + 5
       i = i + 1
       DoEvents
    Loop
    UserForm4.waiting.Left = UserForm4.Label35.Left
    i = 0
    i2 = i2 + 1
    DoEvents
Loop
UserForm4.mess.Caption = "Workbook saved"
End Sub

What I'm trying to achieve is to start this loop when I start saving the workbook (it's taking about 10 seconds to save at the moment, but this will increase as it gets bigger) and exit it when the save has finished.
I've already declared a boolean variable at the top of a module, ready to act as a "switch" so that the loop can keep testing to see if the switch has changed, and therefore exit, but I can't see how I can run the loop as the workbook saves.

Any ideas?

 

[sykes]

............I've been digging around the WIN32 help file, and found this entry:

Asynchronous call

A function that allows the next instruction in the process to be executed as soon as the call is made, without waiting for the function to return. Most OLE functions and interface methods are synchronous; that is, the function must return before the next instruction is executed. OLE defines six asynchronous methods, five within the IAdviseSink interface - OnDataChange, OnViewChange, OnRename, OnSave, and OnClose - and one within the IAdviseSink2 interface - OnLinkSourceChange.

I looked up "IAdviseSink::OnSave", and got this:

IAdviseSink::OnSave

Notifies all registered advisory sinks that the object has been saved.

Void OnSave();

Comments

Object handlers and link objects normally implement IAdviseSink::OnSave to receive notifications of when an object is saved to disk, either to its original storage (through a Save operation) or to new storage (through a Save As operation). Object Handlers and link objects register to be notified when an object is saved for the purpose of updating their caches, but then only if the advise flag passed during registration specifies ADVFCACHE_ONSAVE. Object handlers and link objects forward these notifications to their containers.

Looking at it, this would do the trick, but I havn't the foggiest where to put it, or how to invoke it.

Any help mucho appreciato.

 

[Legacy 98055]

Hi Sykes. Is the application's main window visible whilst you are saving?

 

[sykes]

Hi Tom

Well, the full answer is that I'm trying to make it look like a stand-alone application as much as possible, whilst still using Excel / VBA.
It's all controlled through userforms, so right at the beginning I use application.hide with the workbook_open event, then at the end when the user quits, I use application.quit.
The answer to your question is, therefore, no - only a userform is visible, and this is the one I'm trying to run my "Saving indicator" on.
If you have an idea which might work, I could of course make the application visible again for the duration of the save, but this would to a certain extent defeat the object (pardon the pun!)

Either way, I'm interested in anything you have to offer, thanks.

 

[Legacy 98055]

My conclusion is you cannot do this with code being hosted by the app that is currently in a "hung" state while saving. API or not, you will need to do this from another process. Another instance of Excel, for example.

 

[sykes]

Tom

I had a feeling this might be the case.
I don't give up easily, but now that someone who "knows" has confirmed my doubts I'll give it up, and not waste any more time. (Shame, because my "Saving indicator" looked quite smart, I thought!)

One of the main reasons for wanting this was to inform the user that there was going to be a delay, and not to start wildly hitting keys, trying to re-start the project! I was using message boxes, but I had to input quite a bit of data myself last week, and the constant raft of message boxes appearing, with the associated "Ding!" drove me mad!
I'm starting work on a system of messaging with labels on the userform instead, changing the message before and after the save etc etc. which is simpler, and probably just as effective.........

As always, thanks for your input.

All the best