[Solved] Make a 'log' from external data source

[iainfs]

Hi,

I'm using OOo 3.2 on Windows 7 x64.

I am trying to create a spreadsheet to monitor my bandwidth usage.

There is a configuration page on my router settings, which includes a 'data used' counter in an HTML table. I have imported this table into Calc and set it to automatically update every 10 minutes. This works great. I have defined some functions which pull out the relevant bits of data from the imported table.

What I'd like to do now is set it up so that every time the table updates (or, if that is not possible, every 10 minutes), some of these parameters (plus a time-stamp) are pulled out and inserted into the next available blank row on another sheet.

Many thanks for your help!
iainfs

[Charlie Young]

Sounds like you want a refresh listener, which is something I have used before (as it happens).

In the code below, replace "URL for Log Data" with the URL you have specified, which should be found under Edit > Links. You need to code the routines to do the updating, and call them from where I have specified "UpdateStuff." Feel free to ask for clarification, of course. If you get this to work, you might want to call LinkListener on File Open. If you do this, under Tools > Options > Calc > General, you will want to have "Update links when opening" set to Never, because LinkListener (which you can call anything you want), takes care of the initial refresh.

Code: Select all

Global e
Global logLink As Object

Sub LinkListener
	Dim Doc as Object
	dim x as Object
	dim a as Object
	dim oEvent as New com.sun.star.lang.EventObject
		
	Doc=ThisComponent
	x=Doc.AreaLinks.CreateEnumeration
	e = createUnoListener("Refreshed_","com.sun.star.util.XRefreshListener")
	Do While x.hasMoreElements()
		a=x.NextElement()
		
		If a.URL = "URL for Log Data" Then  ' Substitute complete URL here
			logLink = a
		End If
	Loop
	logLink.addRefreshListener(e)
	loglink.Refresh
		
End Sub

Sub Refreshed_disposing(oEvent)
	
End Sub

Sub Refreshed_refreshed(oEvent)
	UpdateStuff  'Do Update Routines
End Sub

[iainfs]

Charlie, thanks for your reply. I had feared it would need coding I'm new to all of that I'm afraid.

I think that all I need to do is every 10 minutes, copy the numerical contents of a few cells to a new row on a different sheet. Is there some sort of guide or tutorial I could read to learn enough to get by for this (hopefully straightforward) task?

Cheers,

[Charlie Young]

Charlie, thanks for your reply. I had feared it would need coding I'm new to all of that I'm afraid.

I think that all I need to do is every 10 minutes, copy the numerical contents of a few cells to a new row on a different sheet. Is there some sort of guide or tutorial I could read to learn enough to get by for this (hopefully straightforward) task?

Cheers,

If you could attach a copy of the file (suitably edited, if desired), I and others here would be happy to look at it, and see what advice we could give.

For attaching files, see

http://user.services.openoffice.org/en/ ... =74&t=8289

[iainfs]

Thanks for your continued support. The spreadsheet is a little larger than 128 KB (it's about 350) so I've uploaded it to mediafire.

http://www.mediafire.com/?mgyntzfrmrz

Now, the imported HTML table is actually linked to a local file, so I have included a static 'snapshot' of what a single update might look like. This is on the second sheet, called Data. In my local copy, this is set to update every 10 minutes.

The figures I need to grab from it are the Data Transferred numbers and their units, and these are isolated in the cells I9:J10. When the HTML table is updated, these cells are automatically recalculated, which is great.

What I need to do next sounds fairly straightforward, but I don't know how to do it.

Whenever the HTML table is updated (or, alternatively, on a fixed schedule every 10 minutes), I want to add a fresh row of data to the bottom of the table on the first sheet, containing a time-stamp (just hour/minute will do) and the corresponding values we grabbed from the HTML table. The rest of the columns should then fill themselves in automatically. I have the impression that this will need some kind of script, but that's where I get lost!

Any help would be greatly appreciated

[Charlie Young]

Well, I can't see how it could be done without a bit of coding, so I did the dirty work. Note first of all that the size of the file was caused by the formulas on the Bandwidth sheet being copied down to row 5000, and this should not be necessary now - so I have attached it here. The assumptions I have made are that the relevant sheets are called "Bandwidth" and "Data," that the formulas in columns F through K are in place in rows 5 through the last row with data, and that the data to be copied from the Data sheet to Bandwidth are as currently found in Data.I9:J10, so just be sure when you restore the router link that that is still true.

Note also that I modified the formulas a bit, as it should no longer be necessary to check if the time in the new row is identical to the previous one.

If all is working as intended, when you press the Start button on the Bandwidth sheet, the formulas, data, and the current time will be immediately copied to the next empty row, and this will happen every 10 minutes until the Stop button is pressed.

I'm sure you will report back on your experience with this one way or another.

[iainfs]

Charlie,

You are a genius! Thanks very much indeed, it works perfectly. I am indebted to you!

Thanks for your help.

[Charlie Young]

You're welcome. A little undeserved flattery doesn't hurt.

I'm not entirely 100% satisfied with it (I never am), but I won't post anything unless I've got something profound. One concern is if you want to change the update interval (trivial, but just to let you know). Another is what happens when you run out of rows (I haven't done the calculation, but that should take a while), or if you want to change the layout (that should be easy as well).

With external links, there is always the potential problem of the data format changing. That seems unlikely in this case unless they change the router, but I suppose that could happen.

[iainfs]

I had a look through the code. I can just about follow how it all works (I can see where to change the interval) but I wouldn't have stood a chance of actually writing it! Running out of rows shouldn't be an issue, I only want to monitor it for a couple of months or so.

Regarding the external source changing, the ISP updated the router firmware (externally) a few weeks ago and the format of that page didn't change. I reckon that should be OK -- they won't update it for a while yet! Actually, I just fixed a small bug (mine, not yours), regarding how the data is isolated from the table: I had wrongly assumed that the number of significant figures would always be the same, but apparently not. Easily fixed though, with

Code: Select all

=TRIM(RIGHT(E9;SEARCH("/";E9;1)-1))

which lets us extract the 377.52 from "38.46 / 377.52", where before I would've missed off the leading 3