Multi-session / asynchronous web request with VBA and XMLHttpRequest object – Part 2

Standard

In PART 1, we talked about how to download files from the internet using XMLHTTP object. In this chapter, we will talk about how to achieve asynchronous and multi-session file download.

First we need to create a class called Httphelper:

Option Explicit
Private m_exportpath As String
Private m_httprequest As MSXML2.XMLHTTP60

Sub request(ByVal url As String, ByVal exportpath As String)
m_exportpath = exportpath
Set m_httprequest = New MSXML2.XMLHTTP60
m_httprequest.OnReadyStateChange = Me
m_httprequest.Open "GET", url, True
m_httprequest.send ""

End Sub

Sub OnReadyStateChange()

   If m_httprequest.readyState = 4 Then
      If m_httprequest.Status = 200 Then
         save_binary m_httprequest.responseBody
     End If
   End If

End Sub

Sub save_binary(ByRef b() As Byte)

Open m_exportpath For Binary As #1
  Put #1, , b
Close #1

End Sub

After creating the class, you have to export the class to text .cls file and edit the .cls file in text editor:

Screen Shot 2016-10-09 at 4.20.10 PM.png

Add the following code:

Attribute OnReadyStateChange.VB_UserMemId = 0

under

Sub OnReadyStateChange()

Screen Shot 2016-10-09 at 4.23.19 PM.png

Next, you need to import the .cls file into your VBA project, and finally we can use the class like this:

Sub main()
Set http_collection = New Collection
Dim http As Httphelper
For i = 1 To 9
    Set http = new_http
    http.request "http://ichart.finance.yahoo.com/table.csv?s=000" & i & ".hk", "C:\TEMP\" & i & ".csv"
Next i
End Sub

Function new_http() As Httphelper
    Set new_http = New Httphelper
End Function

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s