You can bring data in from web through a web query, but that doesn’t allow you to see the data change over time. With a macro and a chart, you can see how that piece of data changes.
In this example, my web query will be the time. I know you don’t need a graph to see how the time changes, but it gives a nice example that’s updated frequently. Something like a stock price or the current temperature would be more interesting, but they would take too long for my level of patience. Also, I couldn’t find a decent web page from which to query the temperature.
Start by creating a web query that gets the time and put it on sheet named WebQuery:
Next, change the properties to refresh every minute:
Now we’ll need to create a class module with QueryTable Events to save the data before it’s refreshed and lost forever. In a standard module:
Public clsQueryEvent As CQtEvent
In the ThisWorkbook module
Private Sub Workbook_Open()
Set clsQueryEvent = New CQtEvent
Set clsQueryEvent.qtTime = WQuery.QueryTables(1)
In a Class module named CQtEvent:
Public WithEvents qtTime As QueryTable
Private Sub qtTime_AfterRefresh(ByVal Success As Boolean)
WData.Range(“a” & WData.Rows.Count).End(xlUp).Offset(1, 0).Value = _
Range(“B3?) is where the time ends up when I put the query in A1. Note that I have changed the code names of my two worksheets (F4 to show the properties window in the VBE) to WData and WQuery.
Next we need a defined name that will be the source for our chart, call it GraphData and set it up like this:
Create a bar chart on a worksheet name Data, but instead of using the Data Range textbox, switch over to the Series tab and create a series with your newly defined name as the Data.
After a few refreshes, the chart should look something like this
Now you know how long it takes to make a post.