VBA IE automation - wait for the download to compl

问题:

I am trying to automate some tasks which are being done through Internet explorer, which include downloading a file and then copying it to a different directory and renaming it. I was more or less successful in finding the information on how to do this, the code is working, but it has exceptions, therefore I would be grateful if someone could help me to improve this code.

There are two things I would like to do:

1. Insert a loop, so that the script would wait for certain elements to appear and only then would proceed with execution. I have found something on this page, however, I also would like to built in a maximum wait time, like it is suggested there.
2. As the code is downloading a file, it should also wait for the download to be finished, and only then proceed. Currently I am using "wait" command, but the download times may vary and the script will stop in that case. I have also found a solution to this, by waiting till the button "Open folder" appears, but I am not sure how to implement it in my code. Here is the code that i have found: Link

Also, maybe there is another solution, not to save file in a default download location, but do a "Save as" instead and then defining the directory and file name in that way?

Thank you in advance!

Below is my source code, that I am using right now. As an example, I am using Microsoft page with sample file download.

    Option Explicit
#If VBA7 Then
    Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)

    Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
  (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, ByVal lpsz1 As String, _
  ByVal lpsz2 As String) As LongPtr

#Else
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

    Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, _
ByVal lpsz2 As String) As Long
#End If

Sub MyIEauto()

    Dim ieApp As InternetExplorer
    Dim ieDoc As Object

    Set ieApp = New InternetExplorer

    ieApp.Visible = True
    ieApp.navigate "https://docs.microsoft.com/en-us/power-bi/sample-financial-download"
    Do While ieApp.Busy: DoEvents: Loop
    Do Until ieApp.readyState = READYSTATE_COMPLETE: DoEvents: Loop

    ieApp.navigate "http://go.microsoft.com/fwlink/?LinkID=521962"
    Do While ieApp.Busy: DoEvents: Loop
    Do Until ieApp.readyState = READYSTATE_COMPLETE: DoEvents: Loop

    Dim AutomationObj As IUIAutomation
    Dim WindowElement As IUIAutomationElement
    Dim Button As IUIAutomationElement
    Dim hWnd As LongPtr

    Set AutomationObj = New CUIAutomation

    Do While ieApp.Busy Or ieApp.readyState <> 4: DoEvents: Loop
    Application.Wait (Now + TimeValue("0:00:05"))
    hWnd = ieApp.hWnd
    hWnd = FindWindowEx(hWnd, 0, "Frame Notification Bar", vbNullString)
    If hWnd = 0 Then Exit Sub

    Set WindowElement = AutomationObj.ElementFromHandle(ByVal hWnd)
    Dim iCnd As IUIAutomationCondition
    Set iCnd = AutomationObj.CreatePropertyCondition(UIA_NamePropertyId, "Save")

    Set Button = WindowElement.FindFirst(TreeScope_Subtree, iCnd)
    Dim InvokePattern As IUIAutomationInvokePattern
    Set InvokePattern = Button.GetCurrentPattern(UIA_InvokePatternId)
    InvokePattern.Invoke
    Application.Wait (Now + TimeValue("0:00:05"))

    FileCopy "C:\Users\Name\Downloads\Financial Sample.xlsx", "C:\Users\Name\Desktop\Financial Sample.xlsx"
    Name "C:\Users\Name\Desktop\Financial Sample.xlsx" As "C:\Users\Name\Desktop\Hello.xlsx"
    Application.Wait (Now + TimeValue("0:00:01"))

    Dim KillFile As String
    KillFile = "C:\Users\Name\Downloads\Financial Sample.xlsx"
    If Len(Dir$(KillFile)) > 0 Then
    SetAttr KillFile, vbNormal
     Kill KillFile
End If

End Sub

回答1:

If the goal is to download a file from a website (such as Financial Sample.xlsx from https://docs.microsoft.com/en-us/power-bi/sample-financial-download — and the page doesn't actually need to be displayed — then there's another way that you might find less problematic.

As you may have already discovered, programmatically waiting for pages to load, clicking buttons, etc, can become a headache. This compounds with unforeseen/unpredictable factors such as network latency, source changes, etc.

The following method should work with any file URL (and any file type), even if the page doesn't contain an actual link (like many video-sharing sites).

Sub downloadFile(url As String, filePath As String)
'Download file located at [url]; save to path/filename [filePath]

    Dim WinHttpReq As Object, attempts As Integer, oStream
    attempts = 3 'in case of error, try up to 3 times
    On Error GoTo TryAgain
TryAgain:
    attempts = attempts - 1
    Err.Clear
    If attempts > 0 Then
        Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
        WinHttpReq.Open "GET", url, False
        WinHttpReq.send

        If WinHttpReq.Status = 200 Then
            Set oStream = CreateObject("ADODB.Stream")
            oStream.Open
            oStream.Type = 1
            oStream.Write WinHttpReq.responseBody
            oStream.SaveToFile filePath, 1 ' 1 = no overwrite, 2 = overwrite
            oStream.Close
            Debug.Print "Saved [" & url & "] to [" & filePath & "]"
        End If
    Else
        Debug.Print "Error downloading [" & url & "]"
    End If

End Sub

With your example we could use it like:

downloadFile "http://go.microsoft.com/fwlink/?LinkID=521962", _
    "C:\Users\Name\Desktop\Financial Sample.xlsx"

The file will be saved to the specified destination.

---

Possible security warning (and preventing it)

With this method you might have a security warning pop-up (depending on your settings and Windows version)...

This can be easily dealt with in a number of ways: (#3 or #4 being my preference)

1. Click Yes manually.

2. Click Yes by programmatically "finding" the window like your code sample.

3. Enable option "Access Data Sources Across Domains" in Windows Internet Options:

   • Hit the Windows key, type `Internet Options', and hit Enter

   • Click the Security tab.

   • Under Internet, click Custom Level…

   • Under Miscellaneous, choose Access data sources across domains.

4. Use the file's Direct URL instead of an indirect link (like Microsoft's fwlink URL's).

   In the case of your example, the direct link is:

   http://download.microsoft.com/download/1/4/E/14EDED28-6C58-4055-A65C-23B4DA81C4DE/Financial%20Sample.xlsx

...so you would download the file (without the warning) like:

downloadFile "http://download.microsoft.com/download/1/4/E/14EDED28-6C58-4055-A65C-23B4DA81C4DE/Financial%20Sample.xlsx", _
    "C:\Users\Name\Desktop\Financial Sample.xlsx"

I use this method without issues, any time scraping includes files such as documents, videos, MP3's, PDF's, etc.

Every "downloadable file" (and most "viewable files") have actual filename (including the file extension) hiding somewhere, some more obvious than others.

In the case of your link, since I knew the target was an Excel File (and only one file), using Firefox I:

1. opened the source URL from your code,

2. opened the developer logging console:

   • Firefox: Ctrl+Shift+J

   • Internet Explorer: F12 then Ctrl+2)

3. clicked the "" download link in the browser and then cancelled the download link. The "actual" download URL then appears in the Logging screen, to copy & paste to the example above.

The method will obviously vary depending on the site and your particular task, but there are various ways to grab a "hidden" filename. Another common one (for downloading a bunch of videos, etc., from a single page would be a simple web-scrape.) Some sites that try to be sneakier will insert extra characters or escape the strings.

(See if you can figure out the pattern on YouTube or Tumblr; a little trickier but they're in there! A good place to start on most sites is View Page Source and Ctrl+F to search for the file extension you're expecting, ie., MP4.)

That last part might make this method of grabbing files from a URL more complicated than it actually is — Most sites don't try very hard to hide the names of files that you can already download/view manually!

---

More about saving Data Streams from URLs:

• Open Method (ADO Stream)

• Stream Object Properties, Methods, and Events

• Status Property (XMLHTTP)

• SaveToFile Method

回答2:

You could use the GetFileSizeEx function or FSO GetFile and File.Size, and run a loop with a short Wait for 1 or 2 seconds until the file size stops changing? This should mean that the download has finished.

{EDIT} Here is a function to use a late-bound FileSystemObject to get the filesize:

Function GetFilesize(FileName As String) As Long
    GetFilesize = -1 'Default value, for if file does not exist
    On Error GoTo ExitFunc

    Dim oFSO As Object, oFile As Object
    Set oFSO = CreateObject("Scripting.FileSystemObject")

    If oFSO.FileExists(GetFilesize) Then
        Set oFile = oFSO.GetFile(GetFilesize)
        GetFilesize = oFile.Size
    End If

    Set oFile = Nothing
    Set oFSO = Nothing
ExitFunc:
End Function

回答3:

So, after spending some additional time I was able to solve my problem in the way I was expecting, and I am posting the solution below. I thank everyone for the suggestions, and I hope that all of the suggested solutions will be a great find for others in the future :)

So what the code does, it is going to a website, pressing on the download link, then pressing "Save" button, and the download is starting. Then Script is waiting for the "Open folder" button to appear, which means that the download has finished. After downloading the file, script copies the file to desktop, renames it and then deleted the original from the Downloads folder.

  Option Explicit
#If VBA7 Then
Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)

Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" _


 (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, ByVal lpsz1 As String, _
  ByVal lpsz2 As String) As LongPtr

#Else
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, _
ByVal lpsz2 As String) As Long
#End If

Sub MyIEauto()

Dim ieApp As InternetExplorer
Dim ieDoc As Object
Const DebugMode As Boolean = False

Set ieApp = New InternetExplorer

ieApp.Visible = True
ieApp.navigate "https://docs.microsoft.com/en-us/power-bi/sample-financial-download"
Do While ieApp.Busy: DoEvents: Loop
Do Until ieApp.readyState = READYSTATE_COMPLETE: DoEvents: Loop

ieApp.navigate "http://go.microsoft.com/fwlink/?LinkID=521962"
Do While ieApp.Busy: DoEvents: Loop
Do Until ieApp.readyState = READYSTATE_COMPLETE: DoEvents: Loop

Dim AutomationObj As IUIAutomation
Dim WindowElement As IUIAutomationElement
Dim Button As IUIAutomationElement
Dim hWnd As LongPtr

Set AutomationObj = New CUIAutomation

Do While ieApp.Busy Or ieApp.readyState <> 4: DoEvents: Loop
Application.Wait (Now + TimeValue("0:00:05"))
hWnd = ieApp.hWnd
hWnd = FindWindowEx(hWnd, 0, "Frame Notification Bar", vbNullString)
If hWnd = 0 Then Exit Sub

Set WindowElement = AutomationObj.ElementFromHandle(ByVal hWnd)
Dim iCnd As IUIAutomationCondition
Set iCnd = AutomationObj.CreatePropertyCondition(UIA_NamePropertyId, "Save")

Set Button = WindowElement.FindFirst(TreeScope_Subtree, iCnd)
Dim InvokePattern As IUIAutomationInvokePattern
Set InvokePattern = Button.GetCurrentPattern(UIA_InvokePatternId)
InvokePattern.Invoke

Do
Set iCnd = AutomationObj.CreatePropertyCondition(UIA_NamePropertyId, "Open folder")
Set Button = WindowElement.FindFirst(TreeScope_Subtree, iCnd)
    Sleep 200
    If DebugMode Then Debug.Print Format(Now, "hh:mm:ss"); "Open folder"
    DoEvents
Loop While Button Is Nothing


  FileCopy "C:\Users\" & Environ("UserName") & "\Downloads\Financial Sample.xlsx", "C:\Users\" & Environ("UserName") & "\Desktop\Financial Sample.xlsx"
Name "C:\Users\" & Environ("UserName") & "\Desktop\Financial Sample.xlsx" As "C:\Users\" & Environ("UserName") & "\Desktop\Hello.xlsx"
Application.Wait (Now + TimeValue("0:00:01"))

Dim KillFile As String
KillFile = "C:\Users\" & Environ("UserName") & "\Downloads\Financial Sample.xlsx"
If Len(Dir$(KillFile)) > 0 Then
SetAttr KillFile, vbNormal
 Kill KillFile
End If

End Sub

Additionally, if someone will be searching how to loop the code until an element appears, here is the code below. It loops the lines four times and then displays a message.

intCounter = 0

Do Until IsObject(objIE.document.getElementById("btnLogIn")) = True Or intCounter > 3
DoEvents
Application.Wait (Now + TimeValue("0:00:01"))
intCounter = intCounter + 1
If intCounter = 4 Then
MsgBox "Time out."
End If
Loop