XMLHttp vs WinHttp – speed comparison

Time is money and money is time. Everyone wants to have better and faster macros. So do You. In today’s article I am going to do speed comparison between XMLHttp and WinHttp methods.

XMLHttp vs WinHttp - speed comparison fast and furious

First of all, where the idea to do the speed comparison came from?

Some time ago I created a tool, which is downloading the files from URL’s. And there I used WinHttp method. Recently I was told, that it is working so slow, that it took days to download all needed files.

The download speed was really slow. At first I thought, that is all about the files size, but decided to read something about it.

I found the post, where is said, that XMLHttp method is much faster, so I checked that immediately.

Test code

First of all, You need to set the reference for early binding to XML, in my case this is version 6, and to WinHTTP Services, version 5.1.

XMLHttp vs WinHttp - speed comparison references

To do the speed comparison I used code from my old article about downloading from URL. For the first attempt I used MSXML2.XMLHTTP60 and for the second WinHttp.WinHttpRequest. Also, I prepared 10 hyperlinks with files around 1Mb each.

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
Dim ws1 As Worksheet
Dim counter As Long

Dim xmlhttp As New MSXML2.XMLHTTP60
Dim xmlhttp As New WinHttp.WinHttpRequest

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

time_ctn = Timer

Application.ScreenUpdating = False

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

With ws1
    dlpath = .Range("E2").Value
    counter = 0
    
    lastRow = .Cells(Rows.Count, 2).End(xlUp).Row
    
    For i = 2 To lastRow
    
        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 = Replace(Mid(name0, pos + 1, (Len(name0) - _
                (pos))), """", "")
        Else
            name2 = FileNameFromPath(myURL)
        End If
              
        xstatus = URLDownloadToFile(0, myURL, dlpath & "\" & _
                name2, 0, 0)
        
        Debug.Print xstatus
        
    Next i
    
End With

Debug.Print Format(Timer - time_ctn, "0.00")

Application.ScreenUpdating = True

End Sub

Function FileNameFromPath(strFullPath As String) As String

    FileNameFromPath = Right(strFullPath, Len(strFullPath) _
        - InStrRev(strFullPath, "/"))

End Function

Results

I read about the difference, but did not believe it that way.

MSXML2.XMLHTTP60 0.16s
WinHttp.WinHttpRequest 6.24s

XML method went almost 40 times faster!

It was immediate versus 6 seconds of waiting. It was amazing discovery.

So why would I use WinHttp at all?

It may seem that XML is much better than Win method.

It is true, but not always…

Disadvantages of XMLHttp method

1.Sensitive for re-directions

You may think it is weird point – why would I want to put the link for my code, which is not direct link? I may agree with that, but if You receive that from someone else, different things can happen. XMLHttp will not download anything at best case scenario, at bad it will cause an error.

2.Http with/WITHOUT secure

I know that it is almost the same as point above, but wanted to mention it separately.

In most cases I saw the same link structure. There was no redirection, but I received package of links, which in the end appears to have no s in the end of http. Web browser is automatically hiding this beginning of link, so You can’t really see the difference, but XML method does.

It fails on it.

3.No server status

Following above points You can’t get the server status or its answer for your request, like in WinHttp. This was really helpful in one of my cases.

Comparison summary

Now You know who won the XMLHttp vs WinHttp speed comparison. Remember, that the fastest doesn’t always mean the best. Everything depends on the situation. Keep that in mid. Use it wisely.

Author: Tomasz Płociński

I'm very advanced in VBA, Excel, also easily linking VBA with other Office applications (e.g. PowerPoint) and external applications (e.g. SAP). I take part also in RPA processes (WebQuery, DataCache, IBM Access Client Solutions) where I can also use my SQL basic skillset. I'm trying now to widen my knowledge into TypeScript/JavaScript direction.

6 thoughts on “XMLHttp vs WinHttp – speed comparison”

  1. Thanks a lot for awesome tutorials. Can you provide me with a link to test the code?

  2. When trying winhttp, I encountered an error (The requested header was not found) at this line
    name0 = xmlhttp.getResponseHeader(“Content-Disposition”)

  3. But when using (Dim xmlhttp As New MSXML2.XMLHTTP60) instead, the code works fine. Any idea?

    1. Don’t really know what is the issue, because usually when this property was not filled, it returns “” (blank), not causing error. Can You provide me the link You tried? I’m curious what is the matter.

  4. I simply used the link of the image in the article “https://simpleexcelvba.com/wp-content/uploads/2020/03/speedJ.jpg”. Never mind about that. I have put “On Error Resume Next” before the line which causes the error and then “On Error GoTo 0” after it.

Leave a Reply

Your email address will not be published. Required fields are marked *