Apache OpenOffice Community Forum

Hello All

As you will find out, I am not that computer savvy, but I don't want that to stop me from enabling/creating a feature to help out with an issue I come across all day every day in my profession.

I am attempting to create a 1 key-stroke program/method/Macro/AHK (whatever gets the job done) where I pull information from my company's web based reports into OOo. The web based information would hopefully be pulled into my current OOo spreadsheet, on which I have already created various formula's to calculate the information I need, which eventually I will need to add back into the same web page from which the initial data was pulled.

For example: I open my company web page report that I need to review. It has the living area and lot size information for 20 different properties which need to be compared to a single property whose information is also provided on that same web page. The web site from which I am pulling the data is NOT in spread sheet form, but instead all information is in specified fields, so simply highlighting, cutting and pasting into OOo is not an option (if it is, please let me know!).

I would like to "automatically" pull the data for these 21 properties into my spreadsheet, which calculates the difference in living area or lot size between the "subject" property and the 20 other similar properties, converting the differences in size to a dollar adjustment. This dollar adjustment is the data I would then like to export from OOo back into the company's web based report. I am not too concerned with exporting the new calculations back into the report as I am getting the raw data from the report into OOo in the first place, though both would be nice.

Obviously I have set my OOo set up to perform any calculations necessary once the data has been entered into the spreadsheet, but currently, I need to manually cut and paste this information one piece of data at a time, meaning I have to cut and paste 42 different numbers to my spreadsheet. Once OOo does my calculations, I have to cut and paste all this information back into the report. This is a massive waste of time, performed over and over again all day. I would rather spend my time thinking than getting carpel tunnel.

I think I have described the issue and don't want to write too much here. I know this would help me greatly, and could help the company on a much wider scale, if I were able to present to management this idea in a fully functioning form as opposed to a vague idea using technology I know little about.

Any and all help is appreciated.

- njm800

OO.o can communicate with external sources; however, their needs be an agreed upon form of communication between OO.o and your web page. Your situation really depends upon how your web page was programmed--such as if data is contained in an HTML table.

Does the help documentation in OO.o help you? The specific section I am referring to is the Calc help section where the search term would be "references;URL in cells"

You get to this help section by
- Opening OO.o
- Press F1 (or Help | OpenOffice Help)
- Make sure you choose the OpenOffice Calc section
- type "references;URL in cells" into the search term box (without the double quotes)
- press Enter
- this should provide you with steps on how to pull into Calc data from an external web page table

Notice, OO.o allows you to automatically update this table every X seconds. Your calculations upon the data coming from the HTML table into Calc would be performed within Calc (maybe within a different sheet).

You are going to get the calculated data back into the web page will be a manually. However, this will cut down on how quickly you will get carpel tunnel .

-----

As a side note, all the functionality you are looking in your situation is able to be done using XForms and/or XPath. OpenOffice does work with XForms and XPath. However, your web page administrator will need to work with you on this solution so OpenOffice and the web server are agreeing on the form of communication between the two. This is a little more involved.

Yes, the slow part might (indeed) be getting your data calculated by OO.o back into your web page.

OO.o is able to grab external data and work with it, but your web application might not be able to work with the standards-compliant OO.o data. That is something you will need to research.

However, their may be hope if your web program can work with the standards-compliant OO.o data. If this is the case, then things might work very well for you and very quickly all the way around. See if your web program can work with XML. OO.o files are really just compressed XML data.

Their are lots of sites on the internet that show you how to do this. Below is one site that uses Python (which might be what your web server is already using) to extract the data from OO.o files.

http://www.linuxjournal.com/article/9347