This post is about how to extract Corona Virus Affected Statistics from
https://www.worldometers.info/coronavirus/ in Excel using WebQuery and create your own Graph, metrics etc. Once you receive the data from Internet through WebQuery it is up to you how you want to do the visualization of that data.

Also this article talks about – How to Refresh any WebQuery using Excel VBA so that you can simply create a Refresh button to load the latest data from the internet.

This article also talks about how to apply data filter on the Extracted data table via WebQuery using Excel VBA.

So let’s begin with !! First and foremost, Don’t panic by seeing the news/ rumors and Myths spreading around. Although you got to be careful with your basic hygiene like washing your hands at least for 20 seconds, using Hand Sanitizers, etc. Try not to travel to those places where more people are affected. You can read more about this Virus and guidelines you can read FAQs about Corona on WHO website and also your local Health Advisory offices and websites. Please take care of you and your loved ones. Stay calm stay safe.

Coming back to the article, this article will cover following topics:

1. How to Fetch Data from any website in Excel using WebQuery
2. How to Refresh or Run the WebQeury every time by Clicking on a Button
3. How to Filter a data Table by a selected value from a ListBox
4. Download FREE Excel Workbook with Corona Virus Statistics extracted from Web

Let’s start then, to create a Simple WebQuery, in your Excel WorkBook go to Data -> From Web as show in the below picture

From Web – To Fetch Data from Internet

Click on From Web and provide the Web URL, where you want to fetch the data from as shown in the below Picture

Once you click on Excel will try to connect to the URL and parse the whole HTML documents available on that URL and categorize it based on simple HTML document, Table etc.

Data Table extracted from the Website URL provided above

You can also apply different type of Transformations on the Data you receive from web in a tabular format. As shown in the below picture you will see so many – mostly self explanatory – options available to apply transformation on the data received.

For example, for the Corona Virus Statistics table, I wanted to remove the last row which is a Total Row on the website because I wanted to calculate the default Total from the Excel Table. If I do not remove the last row then the total applied on the table will be doubled.

Extracted Data Table – Transformation Logic

Once transformation is done [of course an optional step], table will be extracted in a normal Excel Table like below shown in the picture

Data Table for Graph

Every time you want to refresh this data from the website, you need to click on the refresh button highlighted below on the right hand side

Existing Queries in Excel

Excel VBA code to refresh Web data

Basically to refresh the data from a web connection can be done following way

Refreshing a specific Connection by Connection Name

Sub cbRefreshResult()
    Dim wb As Workbook
    Set wb = ThisWorkbook
    wb.Connections("Query - Table 0").Refresh
End Sub

Refreshing all the connections in a Workbook

Sub cbRefreshResults()
    Dim wb As Workbook
    Set wb = ThisWorkbook
    Dim con As WorkbookConnection
    For Each con In wb.Connections
        con.Refresh
    Next con
End Sub

While refreshing you will notice that, your program will end immediately even though the data is still being refreshed. Basically by default Refresh of the data is done in background. In case you want to display a message to the user only when actually refresh is complete, then you need to disable the background processing.

How to disable background Query running

You can disable it simply by executing this statement before executing the refresh method – con.OLEDBConnection.BackgroundQuery = False

Sub cbRefreshResults()
    Dim wb As Workbook
    Set wb = ThisWorkbook
    Dim con As WorkbookConnection

    For Each con In wb.Connections
        ' get the original value
        bBackground = con.OLEDBConnection.BackgroundQuery
        'Temporarily disable background-refresh
        con.OLEDBConnection.BackgroundQuery = False
        'Refresh this connection
        con.Refresh
        'Set background-refresh value back to original value
        con.OLEDBConnection.BackgroundQuery = bBackground
    Next con
    MsgBox "Data Is Refreshed from following site " & vbNewLine & vbNewLine & "https://www.worldometers.info/coronavirus/" & vbNewLine & " Time : " & VBA.Now
End Sub

In the above code, Data Refreshed message will be shown on the screen only when data is loaded completely.

Thank you so much for reading this article. If you like this article, feel free to share it with your friends, colleagues, everyone… Stay Safe and healthy and keep learning