Hi Forum Members
I have developed an Excel 2003 vba programme to calculate the values of commissions payable to a number of different organisations.
Following a series of calculations initiated via a userform, a bespoke statement is then generated in Word the whole process taking approximately 7 seconds.
Because of the timescale I have introduced a progress bar on the form where the length of a label is increased in length at predefined points in the code with a status label text changing as the programme progresses.
All works fine until the word application is initiated where a delay of approx 2+ Secs occurs. Using the timeGetTime API I've indicated the timings in the adapted code snippet below.
So to my question: How can I detect when the Word application is loaded using a Do loop statement in order to update the progress bar during this delay.
'Declared at module level.
Public Declare Function timeGetTime Lib "winmm.dll" () As Long
dim lngStart as long
Dim wdApp As Object
lngStart = timeGetTime
If blArchive Then
Call Update_ProgressBar(5, "Opening Word") '24ms elapsed
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
'Debug.Print "Post-GetObject: " & Finish '25ms elapsed <<===
Set wdApp = CreateObject("Word.Application")
'Debug.Print "Post-Create oWord: " & Finish '2183ms elapsed <<===
'Populate the statement document.
'Debug.Print "Post-Write Doc: " & Finish '4554ms
Set wdApp = Nothing
Application.Cursor = xlDefault
On Error GoTo 0
Finish = timeGetTime() - lngStart