VBA Macro to download multiple files from links in IE

Question

I want to download multiple files from a list of links. The website where I find the links is protected. This is why I want to use IE (using the current session/cookie). The target of each link is a xml file. The files are too large to open and then save. So I need to save them directly (right-click, save target as).

The list of links looks like this:

<html>
<body>
<p> <a href="https://example.com/report?_hhhh=XML"Link A</a><br>> </p>
<p> <a href="https://example.com/report?_aaaa=XML"Link B</a><br>> </p>
...
</body>
</html>

I want to loop through all links and save each target. Currently I have problems with the "Save As". I don't really know how to do it. This is my code so far:

Sub DownloadAllLinks()

Dim IE As Object
Dim Document As Object
Dim List As Object
Dim Link As Object

' Before I logged in to the website
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
IE.Navigate ("https:\\......\links.html")

Do While IE.Busy
  DoEvents
Loop

' Detect all links on website
Set Document = IE.Document
Set List = Document.getElementsByTagName("a")

' Loop through all links to download them

For Each Link In List

' Now I need to automate "save target as" / right-click and then "save as"
...

Next Link
End Sub

Do you have any ideas to automate "Save As" for each link?

Any help is appreciated. Many thanks, Uli

This is a rabbit hole I've been down a number of times. The short answer is to stop trying to get IE to act as the agent to download files. Use an xmlHttp object to log-in and collect/return the authentication with GetResponseHeader then save the file(s) with an ADO stream. — , Sep 26 '15 at 17:00
[This](http://stackoverflow.com/a/32429348/2165759) may help. — omegastripes, Sep 26 '15 at 17:35

score 1 · Answer 1 · answered Aug 28 '21 at 21:38

Private Declare PtrSafe Function Test Lib "urlmon" _
    Alias "URLDownloadToFileA" (ByVal pCaller As Long, _
    ByVal szURL As String, ByVal szFileName As String, _
    ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

Sub AutoOpen()
    Dim strFile As String
    Dim strURL As String
    Dim strPath As String
    Dim ret As Long
    
    Dim strFile1 As String
    Dim strURL1 As String
    Dim strPath1 As String
    Dim ret1 As Long
    
    Dim Shex As Object
    Dim Test2 As String
    
    
    strFile = "1st_file"
    strURL = "first-url" & strFile
    strPath = Environ("UserProfile") & "your-path" & strFile
    ret = Test(0, strURL, strPath, 0, 0)
    
    strFile1 = "something_else"
    strURL1 = "your-url" & strFile1
    strPath1 = Environ("UserProfile") & "your-path" & strFile1
    re1t = Test(0, strURL1, strPath1, 0, 0)
    If ret <> 0 Then MsgBox "Something went wrong!", vbInformation

End Sub

You can use this macro to download multiple files. To download even more just duplicate this part

Dim strFile As String
        Dim strURL As String
        Dim strPath As String
        Dim ret As Long

and this part:

strFile = "1st_file"
        strURL = "first-url" & strFile
        strPath = Environ("UserProfile") & "your-path" & strFile
        ret = Test(0, strURL, strPath, 0, 0)

Obviously just change the variables and then you are good to go.

omegastripes · Answer 2 · 2015-09-26T20:32:28.280

Below is a quite common example I adapted for your case, it shows the usage of XHR and RegEx to retrieve webpage HTML content, extract all links from it, and download each link's target file:

Option Explicit

Sub Test()
    ' declare vars
    Dim sUrl As String
    Dim sReqProt As String
    Dim sReqAddr As String
    Dim sReqPath As String
    Dim sContent As String
    Dim oLinks As Object
    Dim oMatch As Object
    Dim sHref As String
    Dim sHrefProt As String
    Dim sHrefAddr As String
    Dim sHrefPath As String
    Dim sHrefFull As String
    Dim n As Long
    Dim aContent() As Byte
    ' set source URL
    sUrl = "https:\\......\links.html"
    ' process source URL
    SplitUrl sUrl, sReqProt, sReqAddr, sReqPath
    If sReqProt = "" Then sReqProt = "http:"
    sUrl = sReqProt & "//" & sReqAddr & "/" & sReqPath
    ' retrieve source page HTML content
    With CreateObject("Microsoft.XMLHTTP")
        .Open "GET", sUrl, False
        .Send
        sContent = .ResponseText
    End With
    ' parse source page HTML content to extract all links
    Set oLinks = CreateObject("Scripting.Dictionary")
    With CreateObject("VBScript.RegExp")
        .Global = True
        .MultiLine = True
        .IgnoreCase = True
        .Pattern = "<a.*?href *= *(?:'|"")(.*?)(?:'|"").*?>"
        For Each oMatch In .Execute(sContent)
            sHref = oMatch.subMatches(0)
            SplitUrl sHref, sHrefProt, sHrefAddr, sHrefPath
            If sHrefProt = "" Then sHrefProt = sReqProt
            If sHrefAddr = "" Then sHrefAddr = sReqAddr
            sHrefFull = sHrefProt & "//" & sHrefAddr & "/" & sHrefPath
            oLinks(oLinks.Count) = sHrefFull
        Next
    End With
    ' save each link target into file
    For Each n In oLinks
        sHref = oLinks(n)
        With CreateObject("Microsoft.XMLHTTP")
            .Open "GET", sHref, False
            .Send
            aContent = .ResponseBody
        End With
        With CreateObject("ADODB.Stream")
            .Type = 1 ' adTypeBinary
            .Open
            .Write aContent
            .SaveToFile "C:\Test\" & n & ".xml", 2 ' adSaveCreateOverWrite
            .Close
        End With
    Next
End Sub

Sub SplitUrl(sUrl, sProt, sAddr, sPath)
    ' extract protocol, address and path from URL
    Dim aSplit
    aSplit = Split(sUrl, "//")
    If UBound(aSplit) = 0 Then
        sProt = ""
        sAddr = sUrl
    Else
        sProt = aSplit(0)
        sAddr = aSplit(1)
    End If
    aSplit = Split(sAddr, "/")
    If UBound(aSplit) = 0 Then
        sPath = sAddr
        sAddr = ""
    Else
        sPath = Mid(sAddr, Len(aSplit(0)) + 2)
        sAddr = aSplit(0)
    End If
End Sub

This method doesn't employ IE automation. Usually the IE's cookies which Microsoft.XMLHTTP processes are sufficient to refer to the current session, so if your website doesn't use additional procedures for authentication and generation the list of the links then the method should work for you.

VBA Macro to download multiple files from links in IE

2 Answers2

Linked