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.
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.
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."
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
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.