In this post we see how to import data from Google Sheets in Excel using VBA macros. The macro uses Excel’s web query functionality to import the Google spreadsheet as a query table. The principle is similar to what we have seen in a previous post (Add Web Query To Worksheet). The Google spreadsheet to be imported needs to be public and accessed through a modification of the original link along with the key of the spreadsheet.
In a previous post we saw how to import data from a webpage into a web query table in Excel using VBA macros. In this post, we see how to update or refresh an existing web query table when the file opens. The macro updates the COVID numbers for different countries as per the table in the worldometers website every time the file is open. That could be changed to update the number when a button is clicked, or using any other trigger.
Excel allows to easily import data from a webpage with the web query functionality. This example shows a simple routine to add a web query and import all tables from a particular website into the active worksheet.
