Import Web Data to Excel using VBA Macros
Why Stack Overflow recommends it
Stack Overflow users cite it as the source of the web-scraping macro they copied and adapted, and then ask for help debugging it.
Recommended by 0 people across 0 different months, 2019 to 2021.
What it is
A 20-minute TeachExcel tutorial showing how to use a VBA macro to pull data from a web page into Excel without opening a browser. It uses an XMLHTTP request and an HTML document object, and walks through each line of the macro and the page elements you need to target.
What people said
Cites Luke · 2019-08 · Stack Overflow · on “Error when scraping HTML: Object variable or With block variable not set”
I copied code to get stock data from hsbc derivatives. ( [this video] ) I changed the URL (to hsbc) and that I want to find the value based on the ID, not the class name. I changed the ID name. I get "Run Time Error-91: Object variable or With block variable not set". Sub Get_Web_Data() Dim request As Object Dim response As String Dim html As New HTMLDocument Dim website As String Dim price As Variant ' Website to go to. website = " [LINK] " ' Create the object that will make the webpage request. Set request = CreateObject("MSXML2.XMLHTTP") ' Where to go and how to go there - probably don't need to change this. request.Open "GET", website, False ' Get fresh data. 'request.setRequestHeader "If-Mod
Cites Half Kill · 2020-05 · Stack Overflow · on “Web-scraping for a specific class of tag”
I am attempting to draw financial data from the Australian stock exchange (ASX). Specifically, the share registry for a particular company. The website would be something like this [LINK] (I say something because the ticker in the URL would change from firm to firm). The HTML snippet I am looking at is: <td class="ng-binding">COMPUTERSHARE INVESTOR SERVICES PTY LIMITED <br>Yarra Falls, 452 Johnston Street, ABBOTSFORD, VIC, AUSTRALIA, 3067</td> My overarching goal is to create a spreadsheet whereby I would have a list of Ticker Symbols and a macro would take that symbol, place it in the url and draw the adjacent Share Registry value into Excel. (In this case, COMPUTERSHARE INVESTOR SERVICES P
Cites guga poladashvili · 2021-07 · Stack Overflow · on “Converting weird characters and symbols into normal language in excel”
I am using the VBA code to extract information from a website into excel cells, and the numerical information is fine but I have a problem with text strings. I am mostly extracting information from Georgian websites, and the texts with the Georgian language are not properly displayed in excel, so I was wondering if there is any chance (code or something else) I could convert these symbols into proper language. Sub GetData() Dim request As Object Dim response As String Dim html As New HTMLDocument Dim website As String Dim price As Variant Dim address As Variant Dim x As Integer Dim y As Range x = 1 Do Until x = 9 Set y = Worksheets(1).Range("A21:A200"). _ Find(x, LookIn:=xlValues, lookat:=xl