How to create XmlHttpRequest summary report

Last time I showed You how to download files using function URLDownloadToFile and XmlHttpRequest. In this article I am going to show You, what You can add into old code from previous post to create XmlHttpRequest summary report.

I showed You before only the main code. Let’s make it prettier!

Create report worksheet

First of all we need to create additional workbook for report worksheet. After that let’s name columns,which we will be filling in later. In this case there are 3: ID – not to complicate things just order number, Links – URL links to files and Status – if download went successful or not.

Set wk2 = Workbooks.Add
Set ws2 = wk2.Sheets(1)
ws2.Cells(1, 1) = "ID"
ws2.Cells(1, 2) = "Links"
ws2.Cells(1, 3) = "Status"

Add progress bar

Personally I really like to watch the progression of my tools, so I’m almost always using my progress bar.

Progression.Show

Add ID variable

Next we have to add variable, which will be collecting ID’s of links from column A.

myID = .Range("A" & i).Value

Check & count download statuses

Put into following string variable called stan the download status and count successful downloads.

If xstatus = 0 Then
    stan = "ok"
    ctr = ctr + 1
Else
    stan = "nok"
End If

Counted successes will be shown in end message box.

Gather all info into report

With every iteration save gathered data into, created at the beginning, summary report worksheet.

With ws2
    .Cells(i, 1) = myID
    .Cells(i, 2) = myURL
    .Cells(i, 3) = stan
End With

Update progress bar

In the end of the loop add this line to update the progress in the bar.

Call progress(i - 1, lastRow - 1)

Save summary report

After all links, save summary report in chosen localization and close it.

wk2.SaveAs dlpath & "\" & "Download report"
wk2.Close 0

Close progress bar window

After all iterations, 100% progress bar, macro is finishing its job, so we need to unload the progress bar.

Unload Progression

Show instant info about downloads

And last but not least, let’s show information about downloads – how many of them were successful, with the message box I mentioned above.

MsgBox "Status: " & ctr & "/" & lastRow - 1 & " downloads."

Whole code

Summing all extras from this post and adding to original code, we receive code like that below.

Option Explicit

Private Declare PtrSafe Function URLDownloadToFile 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 download_picture()

Dim wk As Workbook, wk2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet

Dim xmlhttp As New MSXML2.XMLHTTP60

Dim myURL As String, typ As String, name0 As String, name2 As String
Dim name1 As String, xstate As String, dlpath As String, myID As String
Dim pos As Integer
Dim xstatus As Long, i As Long, lastRow As Long, ctr As Long

Application.ScreenUpdating = False

Set wk = ThisWorkbook
Set ws1 = wk.Sheets(1)

Set wk2 = Workbooks.Add
Set ws2 = wk2.Sheets(1)
ws2.Cells(1, 1) = "ID"
ws2.Cells(1, 2) = "Links"
ws2.Cells(1, 3) = "Status"

Progression.Show

With ws1
    dlpath = .Range("D2").Value
    counter = 0
    
    lastRow = .Cells(Rows.Count, 2).End(xlUp).Row
    
    For i = 2 To lastRow
        myID = .Range("A" & i).Value
        myURL = .Range("B" & i).Value
        xmlhttp.Open "GET", myURL, False
        xmlhttp.send
        
        name0 = xmlhttp.getResponseHeader("Content-Disposition")
        
        If name0 <> "" Then
            pos = InStr(1, name0, "=")
            name2 = Mid(name0, pos + 1, (Len(name0) - (pos)))
        Else
            name2 = FileNameFromPath(myURL)
        End If
              
        xstatus = URLDownloadToFile(0, myURL, dlpath & "\" & name2, 0, 0)
        If xstatus = 0 Then
            stan = "ok"
            ctr = ctr + 1
        Else
            stan = "nok"
        End If
        
        With ws2
            .Cells(i, 1) = myID
            .Cells(i, 2) = myURL
            .Cells(i, 3) = stan
        End With
        
        Call progress(i - 1, lastRow - 1)
        
    Next i
    
End With

wk2.SaveAs dlpath & "\" & "Download report"
wk2.Close 0

Unload Progression
Application.ScreenUpdating = True

MsgBox "Status: " & ctr & "/" & lastRow - 1 & " downloads."

End Sub

Summing up…

So, this is how to create XmlHttpRequest summary report.
Now we got complete macro code to download files from links, having everything reported in separate Excel file.

Of course this is my vision, You can have totally different idea and approach to download files from links or to use XmlHttpRequest.