how to wait until download bar appear without application wait in VBA Excel

Question

how can I exclude the Line Application.Wait (Now + TimeValue("0:00:20")) and let excel automatically detect the download bar as the waiting time is not Fixed.

and how can I Press on this Image directly without looping all imgs element.getAttribute("src") = "/xmlpserver/theme/al_excel.gif

Please ignore if you found a lot of declaration as is put them for testing purposes.


Public Declare Function FindWindowEx Lib "user32.dll" Alias "FindWindowExA" ( _
    ByVal hwndParent As Long, _
    ByVal hwndChildAfter As Long, _
    ByVal lpszClass As String, _
    ByVal lpszWindow As String) As Long


Sub Test()

    Const cURL = "https://mywebsite"
    
    Const cUsername = "xxxxxx"    'REPLACE XXXX WITH YOUR USER NAME
    Const cPassword = "xxxxxxxxxxx"    'REPLACE YYYY WITH YOUR PASSWORD
    
    Dim ie As InternetExplorerMedium
    Dim doc As htmlDocument
    Dim LoginForm As HTMLFormElement
    Dim CIFs As MSHTML.HTMLWindow2
    Dim CIFBOX As HTMLInputElement
    Dim UserNameInputBox As HTMLInputElement
    Dim PasswordInputBox As HTMLInputElement
    Dim SignInButton As HTMLInputButtonElement
    Dim element As Object
    Dim htmlDoc As htmlDocument
    Dim HTMLelement As IHTMLElement
    Dim ieEvent
    Set ie = New InternetExplorerMedium
    With ie
        ie.Visible = True
        ie.navigate cURL
    
        Do While ie.readyState <> READYSTATE_COMPLETE Or ie.Busy: DoEvents: Loop

        Set doc = ie.document
    
        On Error Resume Next
        doc.getElementsByName("overridelink").Item.Click
        Application.Wait (Now + TimeValue("0:00:03"))

    
        Set LoginForm = doc.forms(0)
   
    
        Set UserNameInputBox = LoginForm.elements("j_username")
        UserNameInputBox.Value = cUsername
    
        'Get the password textbox and populate it

        Set PasswordInputBox = LoginForm.elements("j_Password")
        PasswordInputBox.Value = cPassword
    
        'Get the form input button and click it
    
        Set SignInButton = LoginForm.elements("btn_login")
        SignInButton.Click
            
        'Wait for the new page to load
    
        Do While ie.readyState <> READYSTATE_COMPLETE Or ie.Busy: DoEvents: Loop
   
        ie.navigate "https:/mywebsite2"
   
        Do While ie.readyState <> READYSTATE_COMPLETE Or ie.Busy: DoEvents: Loop
        Dim img As MSHTML.HTMLImg
        Set doc = ie.document
        Set CIFs = doc.frames(0)
     
        CIFs.document.getElementById("_paramsCIF_NO").Value = Range("A3")
  
        Set AllInputs = CIFs.document.getElementsByTagName("img")
        For Each element In AllInputs
            If element.getAttribute("src") = "/xmlpserver/theme/al_excel.gif" Then
                element.Click
                Exit For
            End If
        Next element
        Application.Wait (Now + TimeValue("0:00:20"))

        AutoSave

        MsgBox ("Screen Update Done")

    End With
    
End Sub

Public Function AutoSave() As Boolean
On Error GoTo handler
    Dim sysAuto As New UIAutomationClient.CUIAutomation
    Dim ieWindow As UIAutomationClient.IUIAutomationElement
    Dim cond As IUIAutomationCondition
    Set cond = sysAuto.CreateAndCondition(sysAuto.CreatePropertyCondition(UIA_NamePropertyId, "Notification"), _
                                      sysAuto.CreatePropertyCondition(UIA_PropertyIds.UIA_ControlTypePropertyId, UIA_ToolBarControlTypeId))
    Set ieWindow = sysAuto.GetRootElement.FindFirst(TreeScope_Descendants, cond)

    Dim tField As UIAutomationClient.IUIAutomationElement
    Dim tFieldCond As IUIAutomationCondition
    Set tFieldCond = sysAuto.CreatePropertyCondition(UIA_ControlTypePropertyId, UIA_ControlTypeIds.UIA_SplitButtonControlTypeId)
    Set tField = ieWindow.FindFirst(TreeScope_Descendants, tFieldCond)

    Dim invPattern As UIAutomationClient.IUIAutomationInvokePattern
    Set invPattern = tField.GetCurrentPattern(UIA_InvokePatternId)
    invPattern.Invoke
    AutoSave = True

Exit Function
handler:

End Function

If no other form exist, you can try a loop: `Do while LoginForm is Nothing` `on error resume next` `Set LoginForm = doc.forms(0)` `On Error GoTo 0` `Loop`. It should exit the loop in the moment when `LoginForm` object has been created... — FaneDuru, Jul 11 '23 at 13:14
I think it would be good to also insert another line: `DoEvents`... — FaneDuru, Jul 11 '23 at 13:37
OK, what does it mean? Do you receive an error? Does the code stop? Doesn't it go out from the loop? Or **what**? — FaneDuru, Jul 11 '23 at 17:08
Dear,, I'm barely read the VBA..if you don't mind.please re-write the code and let me know how this code detect the download bar automatically. — Basem Zaki, Jul 11 '23 at 17:09
What to "re-write"? The code in my first comment creates a loop inside of it tries setting the object. It stays in the loop as match the object is nothing and exit it when the object is set. But, how do you test it? What made you say "It doesn't work"? — FaneDuru, Jul 11 '23 at 17:47
I think you didn't understand what I mean. The above code is automat IE and go through the website and download xlsx on the line element.Click I need to detect the download bar and exclude the application wait line — Basem Zaki, Jul 11 '23 at 20:19
Would this be helpful? https://stackoverflow.com/questions/48560702/vba-ie-automation-wait-for-the-download-to-complete — DecimalTurn, Jul 11 '23 at 23:52
I think it is using the same concept of Application.Wait but in different way, Is any another way to loop till it detect download bar> — Basem Zaki, Jul 12 '23 at 06:03
What you try doing looks obvious. Did you understand what I tried suggesting? If so, I asked for clarifications about "It doesn't work" and you were not able to answer them. Now, does the existing code, **as it is** work? Why do you want replacing `Application.Wait (Now + TimeValue("0:00:20"))` line of code? I thought that waiting for 20 seconds, not exactly how much time is needed for the necessary form to show up, I suggested a classic loop waiting until the form is visible. Usually, inside the loop a line as `Application.Wait (Now + TimeValue("0:00:01"))`, to wait one second per loop. — FaneDuru, Jul 12 '23 at 08:29
But, since you hate this line, I only suggested `DoEvents` until the object it is found, set and the loop exits. I hope you know that you cannot write code in comments **on multiple lines**... I mean, each code formatted slices should be placed on separate lines. This loop must replace only the code line you hate. Now, maybe you are in a better mood today and explain how you use it and what was happening to determine you writing "It doesn't work", which is completely useless in such discussions... — FaneDuru, Jul 12 '23 at 08:33
as I said I'm new in VBA and Barely understand its syntax, Of Couse i couldn't understand what exactly you mean, So I asked you to implement you idea in an answer to know how to integrate or merge this Loop into the code that is working fine as it is. — Basem Zaki, Jul 12 '23 at 08:50
If you say that "please re-write the code" means placing an answer, I must confess that I couldn't understand that... I will copy the above code in an answer and explain how to use it (as I thought I explained in my above comment). Anyhow, I will post such an answer in a minute. — FaneDuru, Jul 13 '23 at 07:05

FaneDuru · Accepted Answer · 2023-07-13T10:31:42.647

Please, replace the next two code lines:

   Application.Wait (Now + TimeValue("0:00:20"))
   Set LoginForm = doc.forms(0)

which tells to VBA to wait 20 seconds until trying to set the LoginForm object, with the next piece of code. It will set the object as soon as possible and exit the loop:

Do while LoginForm is Nothing 
   On Error resume next 
   Set LoginForm = doc.forms(0) 
   On Error GoTo 0 
   DoEvents
Loop

The above solution avoids waiting a specific set period of time before setting LoginForm. According to the internet connection quality, it may take more or less time...

But, if this does not set the object before it is shown, because it is not possible... So, in terms of making the code faster, it may do that only for a few seconds.

Usually, it also includes Application.Wait (Now + TimeValue("0:00:01")), to only loop once per minute, instead of looping thousand of times... Not answering my question regarding the reason of eliminating ``Application.Wait`, I did not proceed as usually and completely eliminated it...

Edited:

For the AutoSave part, please try replacing:

  Application.Wait (Now + TimeValue("0:00:20"))
  Autosave

with:

  Do while AutoSave = False
    On Error Resume Next
     Application.Wait (Now + TimeValue("0:00:01")) 'try it from minute to minute
     AutoSave
    On Error GoTo 0
  Loop

It will exit the loop when AutoSave function will return True.

If you really hate Application.Wait, comment that line and let CPU to be loaded trying continuously to call the respective function...

Please check the Line '''element.Click''' and the line that call function '''AutoSave''' the element.click is downloading a xlsx and Autosave function is clicking save as on downloading bar when appear how can i implement this answer to avoid '''Application.Wait (Now + TimeValue("0:00:20"))''' that comes after '''element.Click''' — Basem Zaki, Jul 13 '23 at 09:58
Then, place `Do while AutoSave is false', in fact, I will edit the answer... — FaneDuru, Jul 13 '23 at 10:18

how to wait until download bar appear without application wait in VBA Excel

1 Answers1