When you want to retrieve HTML or JSON from a website without launching a browser, you can use HTTP communication objects such as MSXML2.XMLHTTP.
In this article, I will introduce the procedure to send a GET request to a sample URL (https://morinokabu.com/), retrieve the response body, and check it in a message box.
目次
Sample Code
Sub FetchWebData()
Dim httpReq As Object ' Object for HTTP request
Dim respBody As String ' Received response body
Set httpReq = CreateObject("MSXML2.XMLHTTP")
With httpReq
.Open "GET", "https://morinokabu.com/", False ' Synchronous communication
.Send ' Send request
' Process only if status code is 200 (OK)
If .Status = 200 Then
respBody = .ResponseText
MsgBox respBody, vbInformation, "Fetched HTML"
Else
MsgBox "HTTP Status Code: " & .Status, vbExclamation, "Error"
End If
End With
Set httpReq = Nothing
End Sub
Key Points
| Item | Explanation |
| MSXML2.XMLHTTP | A lightweight HTTP client capable of executing GET and POST requests. |
| Open (3rd Argument) | Specifying False enables synchronous communication, meaning code execution stops until the response is received. Change this to True for asynchronous communication. |
| Status Property | Retrieves the HTTP status code. A value of 200 indicates that the response was returned successfully. |
Application Ideas
| Goal | Implementation Example |
| Fetching JSON APIs | If the response is JSON, you can use a library like VBA-JSON to parse it and store it in a Dictionary object. |
| POST Requests | Use .Open "POST", url, False, set headers with .setRequestHeader, and then send data using .Send "param1=value1". |
| Bypassing SSL Verification | In a development environment, you can use MSXML2.ServerXMLHTTP60 and specify .setOption 2, 13056 to allow communication with self-signed certificates. |
Important Notes
- Synchronous Communication (
False): Processing stops until a response is received. If you are communicating with a slow server or downloading large data, consider using asynchronous communication (True). - Site Policies: If a site prohibits scraping or if you are making a large number of access requests, please check the terms of service and be considerate (e.g., add intervals between requests).
Summary
By using MSXML2.XMLHTTP, you can directly fetch web data from Excel VBA without going through a browser.
This technique can be applied not only to fetching HTML but also to querying API servers and downloading files. It is a powerful tool for business automation.
