Web Query real time updating

[g48dd]

Excel 2003, My company has about 200 printers on a network. Today I was handed a SS that has all the IP address and was asked to get the page count off of each printer. The Printers are made by HP and each has its own data page count HTML site. If you use Web Query you can import the data right into a SS but I don't need most of it just the total page count. So I did an import and then deleted all the info I didn't need and I am watching the page count to see if it has changed but I don't see it changing and I know the dept I am looking at prints a lot of stuff.... what am I missing? I was just thinking of building a SS that when opened goes out and updates the page count on 200 printers.... you can do this real time right? Its no different than getting stock exchange stuff and I have never done but I have seen it done.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Thanks <o></o>
Ken<o></o>

 

[energman58]

You need to get external data into the ss - it is in the Data tab on 2007. You navigate to where you want to import it from and then depending on how the site is laid out you either select the table or the page to import.

You then can use the data in your spreadsheet but it is effectively a snapshot of the data at the time - you have to refresh the query to get the latest data which you can do in VBA every 5 minutes or something - depending on how long it takes to return (as you are on an internal network it should be quite fast).

So if you know the IP address of the printers you simply need to create the links once and subsequently refresh them to scrape the data you want. As there are 200+ printers and you already have the IP addreses in a spreadsheet I think I would write some code for this but once you have established the links it is simply a case of refreshing the data and no VB is required.

 

[g48dd]

Thank you, I figured out that what I had to do was bring the External Data Tool Bar up and that has a refresh all Icon, there is a properties to this that I missed when I first set it up and in the properties box you can set it up so that it does an update when you open... that is all I really need is one update per day and I can set that up through the Windows Task mgr to open it automatically.

It took me ahwile to locate all this but I really appreciate your help, what I curious about is if I can scrap the data I don't need and the update still work, I think I will build a test page and see what happens.

Thanks
Ken