In the previous post we have seen how to connect to a web API in Excel using VBA macros. In this post we see how to read the API JSON response in Excel and retrieve the data of interest. In this macro example, the data is the list of universities for a given country. The web API is public and does not require any API key to be used. There are different ways to read a JSON file in Excel. We will use a simple method of string manipulation to get the name of each university for a given country.
Sunday, May 29, 2022
Thursday, May 26, 2022
Connect To API Excel VBA Macro (Part 1)
In this post we learn how to get data from a web API in Excel using VBA macros. As an example, we are getting data form a public API with information about universities around the world. This API does not require any API key to be used, and consists of a simple URL that accepts a single parameter to specify the country of interest. The Excel VBA macro code to send an API call and retrieve information or data from the API consists of two steps. In this post we will cover the first step, which consists of sending an HTTP request to the API URL, in a similar way we have done to scrape web data in a previous post (Scrape Web Data in Excel Part 1). In the next post we will see how to read the API response returned as JSON format, and retrieve the list of universities for a given country.
Tuesday, May 3, 2022
Scrape Web Data Excel VBA Macro (Part 2)
In the previous post we have seen how to send an HTTP request to get web data as text (Part 1). In this post we see how to process the response into the body of a html document to target the data of interest (Part 2). We need to add the Microsoft HTML Object Library to the references in order to manipulate the response as a html document. Then we need to target and get the elements with the information we are after. In this particular example, that’s the job title, company, location, etc. Despite the macro is entirely written with Excel VBA, basic HTML/web programming skills are required to understand the whole process.
Tuesday, April 26, 2022
Scrape Web Data Excel VBA Macro (Part 1)
In this post we learn how to send a HTTP request to scrape and import web data into Excel using VBA macros. As an example, we are getting data form a jobs portal, which contains public data that anyone can see on the browser with no login needed. The process consists of two steps. In this post, we will see the first step, which consists of sending a HTTP request to get the web page contents as text. In the next post we will see how to process the HTTP response into the body of a HTML document in order to target the data of interest.
Popular Posts
-
In a previous post , we have seen how to send an email from Excel (via Outlook) using VBA macros. We can add as many recipients as needed se...
-
We can integrate Excel with other Microsoft Office applications using VBA. In this post we are going to see how to send an email from Excel ...
-
In this macro example, we compare two worksheets with data from two different versions of the same data set. Thus, we can see if there are a...
-
Excel offers filter options to easily narrow down the data. This macro example activates the filter option in VBA and copies the filtered da...
-
In this post we learn how to upload or export data from Excel to Google Sheets using VBA macros. The macro sends a HTTP request to POST the ...
-
In this post we see how to compare two Excel workbooks with VBA macros. The macro checks if the number of worksheets matches, then compares ...
-
In a previous post we saw how to Compare Two Worksheets in Excel using VBA macros. In this post we see how to compare two columns with data...
-
This macro runs a clock timer in Excel. The user can start/stop the time, and the clock shows minutes and seconds. It is a simple example ...
-
This macro example separates sheets into new workbooks, keeping the original workbook. Each new workbook takes the name of the sheet and con...
-
In this post we see how to delete rows based on given criteria in Excel using VBA macros. The macro loops through each row with content in t...