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.