Progress indicator while creating Word document (1 Viewer)

Kronix · Jan 12, 2018

I have some VBA code that opens a Word document (using Word.Application object), replaces some bookmarks, and then saves it as a RTF file. This process takes 5-15 seconds after the corresponding button is clicked.

How do I make some kind of animation appear after the button is clicked to show that the system hasn't frozen? It doesn't have to be a progress bar, especially since the work isn't a countable loop but a set number of bookmark inserts and I don't know how much of the time is spent on opening and closing the file/object vs inserting bookmarks. It could just be an animating mouse cursor, or some text in the status bar at the bottom of the Access window with the words "Working..." and the periods after "Working" cycling from 1 to 3.

What options do I have? Is it possible to create a process thread for an animation that starts when the Word object is opened and ends when it quits and is set to Nothing?

isladogs · Jan 12, 2018

You could indeed use a progress bar
OR status bar text
OR textbox / label visible on the form
OR change cursor to hourglass
OR a combination of those

Kronix · Jan 12, 2018

How would I make it animate while it is opening Word and the document, since those are time consuming tasks that take up a single line of VBA code, since I can't create VBA code to animate "inside" of those parts?

isladogs · Jan 12, 2018

You'll be pleased to know its very simple ...

If you want a progress bar there are several examples in the Code Repository & Sample Databases areas ... including one of mine ...

Otherwise, add one or more of the following item pairs both before and after your code

NOTE: lblInfo is a label on your form that is normally hidden.
When I use this method, I use a large font (24pt?) in a bright colour e.g. Magenta so it stands out

Code:

DoCmd.Hourglass True
StatusBar "Updating Word document ...."

Me.lblInfo.Visible = True
Me.lblInfo.Caption = "Updating Word document ...."

[I][COLOR="DarkRed"]'Your code goes here[/COLOR][/I]

DoCmd.Hourglass False
StatusBar ""

Me.lblInfo.Visible = False
Me.lblInfo.Caption = ""

If you want to use the StatusBar code, put this function in a standard module

Code:

Public Sub StatusBar(Optional msg As Variant)

On Error GoTo ErrHandler
Dim temp As Variant

' if the Msg variable is omitted or is empty, return the control of the status bar to Access

    If Not IsMissing(msg) Then
        If msg <> "" Then
            temp = SysCmd(acSysCmdSetStatus, msg)
        Else
            temp = SysCmd(acSysCmdClearStatus)
        End If
    Else
        temp = SysCmd(acSysCmdClearStatus)
    End If
    
ExitHandler:
    Exit Sub
    
ErrHandler:
    MsgBox "Error " & Err.Number & " in StatusBar procedure : " & Err.Description
    Resume ExitHandler
End Sub

Accessanitor · Jan 14, 2018

Using DoEvents also helps if the form doesn't respond while the process is running.

Kronix · Jan 21, 2018

Thanks, I'll have to look into this later. Are these the only options available to me for multithreading in VBA?

isladogs · Jan 21, 2018

You asked about 3 different solutions - text on screen, status bar text & progress bar.
I gave you code for the first two of those & suggested where to look for progress bars

What else would you like - a warning siren?

The_Doc_Man · Jan 22, 2018

Are these the only options available to me for multithreading in VBA?

Technically speaking, there are NO options for multithreading in VBA because it is interpretive and the interpreter is single-threaded. AT BEST you can simulate threaded behavior a little bit. NOTE: There is a "hint" of multi-threading in that the DBEngine (these days, ACE) can perform some cleanup steps in parallel with Access - but there is no true parallel thread operation in the Pascal sense of Fork/Join.

You can use a timer event that does things that RESEMBLE animation via a parallel thread - but you are really just using a timed event interrupt/trap to drive the effect. In essence, your timer is stealing ticks to do its work. Be aware that a poorly written timer can bollux up your system in a heartbeat. And ALSO be aware that because timers are events, and Access is "picky" about event handling, the timer cannot interrupt another event (and another event cannot interrupt a timer.)

The comment about DoEvents is another case of RESEMBLING parallelism. While Access is NOT multi-threaded to any significant degree, Windows IS. DoEvents essentially tells Windows that you are voluntarily giving up the CPU resource briefly to allow other events in other threads to occur. NOTE: On a multi-CPU system, DoEvents is less effective since there is usually another free processor anyway. If you have as many as 4 CPUs, you will almost certainly always have a free CPU slot for process execution.

The "other threads" for the case being discussed would include Word, which is running as a separate task/process in memory even though VBA and a Word Application Object from Access may be calling the shots. In general, Application Objects use Windows threading where possible since they have their own separate utility (Word, Excel, Outlook, etc.) that can act independently for a brief time given sufficient direction.

Progress indicator while creating Word document (1 Viewer)

Kronix

Registered User.

isladogs

MVP / VIP

Kronix

Registered User.

isladogs

MVP / VIP

Accessanitor

Registered User.

Kronix

Registered User.

isladogs

MVP / VIP

The_Doc_Man

Immoderate Moderator

Similar threads

Users who are viewing this thread