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)
End Sub
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 = _
WQuery.Range(“B3?).Value
End Sub
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.
Dick, I swear I have never been happier. I was first impressed with the concept of tracking internet data through a web query – I hadn’t gotten into that before. I was carefully reading your post for that reason, because I have a few applications for that sort of thing.
But I was blown away when I got to your name definition — it gives you a variable-named-range . . . and it’s so SIMPLE!
Thanks for such a healthy daily dose. You just made my day better!
For anyone using excel, you will need to edit a text file (there is probably an addin for this some where, but hey)
all you need to do is add the URL to a blank text file and then save it with the file extention “.iqy” , then you can go to run a web query on the extrnal data menu, and run it.
More info at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;q157482
On a related note, are Add-Ins and Web Queries incompatible?
I tried making a toolbar with a textbox that would display the current temperature.
The only way I could get it to work was if I set my IsAddIn property to False.
Otherwise, since the sheet that the query is on is not visible, the query is not called, so nothing based on that query will work. Is there another way?
I know that dick has an addin that looks at a dictionary site, but I don’t know if this is a web query? – the sheets on this were hidden.
I’m going to try and write a little addin for xl97, that will allow you to run web queries with out having to pop out and manually save a text file. Should be very easy, if I can save a text file with the .iqy extension. Give me a few hours and see what I come up with.
Right all when to plan, here it is:
http://www.freewebs.com/methodsinexcel/XL%2097%20Web%20Query.zip
not an addin, just a .xls. I have it sat in my “personal workbook” always open with excel, and open it from a tool bar short cut.
Works ok for me.
can you help me to do this:
i have an excel book.
in it are sheets with web queries from some websites. these websites wants ‘user login’ & ‘password’.
so then i open this excel book, it cant update data in web queries.
how to automate this process, that an excel book at opening would connect to these sites?
i would very happy for your answer.
how can I download a stock chart directly from the Web as an image without downloading the data and making the chart in Excel?
Thanks,
Ann
right click the mouse as pick as image as, or press the “Print Screen” Button on you key board, go to paint and press ctrl+V (ther ctrl button and V at the same time)