Sending HTTP Requests to Access Web Resources Using Excel

Today, I will share code that sends <span>HTTP</span> requests through <span>Excel</span> to retrieve web resources.

Function BytesToBString(Body, Cset)
    On Error Resume Next
    '"GB2312"
    '"GBK"
    '"UTF-8"
    ' Excel byte encoding conversion

    Dim Objstream
    Set Objstream = CreateObject("adodb.stream")
    Objstream.Type = 1
    Objstream.Mode = 3
    Objstream.Open
    Objstream.Write Body
    Objstream.Position = 0
    Objstream.Type = 2
    Objstream.Charset = Cset
    BytesToBString = Objstream.ReadText
    Objstream.Close
    Set Objstream = Nothing
End Function

Function HttpGet(Target As Range) As String
    ' HTTP GET
    Dim Client As New WebClient
    Dim Url As String
    Url = Target.Value

    Dim web_Request As New WebRequest

    web_Request.Resource = Url
    web_Request.Format = WebFormat.Json
    web_Request.Method = WebMethod.HttpGet

    Dim Response As New WebResponse
    Set Response = Client.Execute(web_Request)

    Dim ResponseText As String
    ResponseText = BytesToBString(Response.Body, "UTF-8")

    HttpGet = ResponseText
End Function

<span>HttpGet</span> depends on the VBA-Web[1] library. You need to first copy <span>VBA-Web</span> into the Excel file, and then copy the above code into the file. It will be ready to use.

For example, to retrieve the content of the Baidu homepage:

=HttpGet("https://www.baidu.com")

References

[1]

VBA-Web: https://github.com/VBA-tools/VBA-Web

Related posts

  1. How Should Interface Testing Engineers Learn HTTP/HTTPS Protocols? (Part 19)
  2. Introduction to Go Project Development (Part 21): HTTP Service Authentication & Authorization
  3. The Past and Present of the HTTP Protocol
  4. Detailed Explanation of Basic HTTP Protocol
  5. Understanding HTTP Host Header Attacks
  6. Salary Increase Technology | Lesson 9 of Performance Testing from 0 to 1 – HTTP Response Statistics
  7. Local Cloud-Native Practices for Rust Applications: Colima + K8s + Multi-Architecture CI/CD Deployment Process
  8. Using HttpClient in C# to Send POST and GET Requests to Call APIs
  9. Understanding Blepharospasm and Apraxia of Eyelid Opening in PSP Patients
  10. Introduction to C++ STL: From Array to Vector, Experience the Advantages of Template Programming
  11. Guide to Using the LiteLLM Library for Integrating Multiple AI Models in Python
  12. Tutorial on Deploying MQTT on Windows 10
  13. What Is the http://router.ctc Telecom Router?
  14. HTTP Programming in Go: Web Server and Client
  15. HttpOnly Defense Against XSS
  16. HTTP Smuggling: Understanding the Attack Techniques and Detection Methods
  17. Frontend HTTP Requests: A Practical Guide to Accurately Determining Data Reception Completion
  18. Critical Vulnerability in HTTP/1.1 Poses Malicious Takeover Risk for Millions of Websites
  19. Douyin Command Tutorial: Learn 3 Ways to Open Douyin Commands and Usage Rules in 1 Minute

Leave a Comment