Web Query real time updating

g48dd

Board Regular
Joined
Jan 12, 2009
Messages
101
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-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
Thanks <o:p></o:p>
Ken<o:p></o:p>
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

energman58

Well-known Member
Joined
Oct 25, 2010
Messages
553
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

Board Regular
Joined
Jan 12, 2009
Messages
101
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,032
Messages
5,856,939
Members
431,841
Latest member
jaybeem

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top