insertDocumentFromURL() vs loadComponentFromURL()..

Creating a macro - Writing a Script - Using the API

insertDocumentFromURL() vs loadComponentFromURL()..

Postby Mandrake » Wed Jun 15, 2016 6:19 pm

Hi All,

I am trying to download yahoo finance data into a calc sheet. I have found the code however it loads the data into a new file. Is there anyway to load these datas into an exsisting sheet?

Here the two options I have tried so far:
Code: Select all   Expand viewCollapse view
Sub Main
   getStockInfo( array("AAPL", "GOOG"))
End Sub

Sub getStockInfo(iCompanySymbols)
   Dim oUrl as String
   Dim oDoc as Object
   Dim oFrame as Object
   Dim oSymbols as String
   Dim oFields as String
   Dim oPropertyValue(0) as New com.sun.star.beans.PropertyValue
   
   REM oDoc = ThisComponent
   oFrame = ThisComponent.getCurrentController().getFrame()
   oSymbols = join (iCompanySymbols, "&s=")
   oFields = "sl1d1t1c1ohgv"
   oUrl = "http://download.finance.yahoo.com/d/quotes.csv" & "?s=" & oSymbols & "&f=" & oFields & "&e=.csv"
   REM example http://download.finance.yahoo.com/d/quotes.csv?s=AAPL&f=sl1d1t1c1ohgv&e=.csv
   oPropertyValue(0).Name = "FilterOptions"
   oPropertyValue(0).Name = "44" 'Comma separated tab
   oDoc = oFrame.loadComponentFromURL(oURL, "_self", 0, oPropertyValue())
End Sub

This works shall work accordingly to the manual, but it still opens a new document

Code: Select all   Expand viewCollapse view
Sub Main
   getStockInfo( array("CGA", "GOOG"))
End Sub

Sub getStockInfo(iCompanySymbols)
   Dim oUrl as String
   Dim oDoc as Object
   Dim oFrame as Object
   Dim oSymbols as String
   Dim oFields as String
   Dim oPropertyValue(0) as New com.sun.star.beans.PropertyValue
   
   REM oDoc = ThisComponent
   oFrame = ThisComponent.getCurrentController().getViewCursor()
   oSymbols = join (iCompanySymbols, "&s=")
   oFields = "sl1d1t1c1ohgv"
   oUrl = "http://download.finance.yahoo.com/d/quotes.csv" & "?s=" & oSymbols & "&f=" & oFields & "&e=.csv"
   REM example http://download.finance.yahoo.com/d/quotes.csv?s=AAPL&f=sl1d1t1c1ohgv&e=.csv
   oPropertyValue(0).Name = "FilterOptions"
   oPropertyValue(0).Name = "44" 'Comma separated tab
   oDoc = oFrame.insertDocumentFromURL(oURL, oPropertyValue())
End Sub


In this example it gives me an error with the current cursor.

Any solution?

Super thanks for helping

M
OpenOffice4 , Yosemite
Mandrake
 
Posts: 7
Joined: Sun Jun 12, 2016 2:01 am

Re: insertDocumentFromURL() vs loadComponentFromURL()..

Postby Villeroy » Wed Jun 15, 2016 8:24 pm

It is easy to link a sheet.
menu:Insert>Sheet From File...
Paste your URL in to the file picker, confirm and wait some seconds.

Code: Select all   Expand viewCollapse view
http://download.finance.yahoo.com/d/quotes.csv?s=AAPL&f=sl1d1t1c1ohgv&e=.csv

imports correctly with the following import settings:
Character set: UTF-8 or something Western
Language: English(USA) [because of the M/D/Y dates]
Column separator: Comma
Text separator: Double quote
Quoted values as text = OFF
Special numbers = ON

menu:Edit>Links... [Update] updates the link.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28676
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: insertDocumentFromURL() vs loadComponentFromURL()..

Postby Mandrake » Wed Jun 15, 2016 9:15 pm

Thanks for your reply.

I am using a mac and the file picker option does not come up. I can use insert sheet from file but I have no option to insert the link.
OpenOffice4 , Yosemite
Mandrake
 
Posts: 7
Joined: Sun Jun 12, 2016 2:01 am

Re: insertDocumentFromURL() vs loadComponentFromURL()..

Postby Villeroy » Thu Jun 16, 2016 12:11 am

Some kind of file open dialog should pop up when you insert a new sheet from some file. If the Mac dialog does not accept http: URLs you can switch to the built-in OpenOffice dialogs. The "General" section of the options dialog has a check box "use OpenOffice dialogs". These dialogs take any kind of URL you throw at it as long as it points to some kind of file somewhere.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28676
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: insertDocumentFromURL() vs loadComponentFromURL()..

Postby Mandrake » Thu Jun 16, 2016 3:17 pm

That worked! Thanks.

Anyway I would still be interested in understadning why my macro did not work and if anyone wants to suggest solutions.

M
OpenOffice4 , Yosemite
Mandrake
 
Posts: 7
Joined: Sun Jun 12, 2016 2:01 am

Re: insertDocumentFromURL() vs loadComponentFromURL()..

Postby Villeroy » Thu Jun 16, 2016 3:51 pm

It can not work because you don't use any object inspector nor documentation. The method you try to use is not for the purpose you intend.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28676
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: insertDocumentFromURL() vs loadComponentFromURL()..

Postby Mandrake » Thu Jun 16, 2016 3:58 pm

The methods I used come from this https://www.amazon.co.uk/Learn-OpenOffice-org-Spreadsheet-Macro-Programming/dp/1847190979 and this OpenOffice.org Macros Explained - Andrew Pitonyak.

The problem with their example is that the macros open a new doc instead of inserting a new sheet into an existing doc.
OpenOffice4 , Yosemite
Mandrake
 
Posts: 7
Joined: Sun Jun 12, 2016 2:01 am

Re: insertDocumentFromURL() vs loadComponentFromURL()..

Postby Villeroy » Thu Jun 16, 2016 4:10 pm

Code: Select all   Expand viewCollapse view
Sub Main()
GlobalScope.BasicLibraries.loadLibrary("MRILib")
obj = ThisComponent.CurrentController.getFrame()
mri obj

There is no method insertDocumentFromURL for a Writer frame or spreadsheet frame.

I have never used insertDocumentFromURL.
A quick search on this forum indicates that it is a text cursor method to insert the contents of a text document.
https://forum.openoffice.org/en/forum/v ... RL#p380711

Writing macros with Open/LibreOffice is pointless. Too much effort for sparse results while nobody is willing to pay for this oversophisticated bullshit.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28676
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: insertDocumentFromURL() vs loadComponentFromURL()..

Postby ThierryT » Sun Jun 19, 2016 12:04 pm

The insertDocumentFromUrl method is based on the Text Cursor system : https://wiki.openoffice.org/wiki/Docume ... Text_Files
not the Frame.
LibreOffice 5.3.2.1 x 64 and AOO 4.1.3 Windows x64
ThierryT
 
Posts: 11
Joined: Fri May 13, 2016 8:52 pm

Re: insertDocumentFromURL() vs loadComponentFromURL()..

Postby chrismjh » Mon Jun 20, 2016 4:41 pm

Villeroy wrote:
Code: Select all   Expand viewCollapse view
Sub Main()
GlobalScope.BasicLibraries.loadLibrary("MRILib")
obj = ThisComponent.CurrentController.getFrame()
mri obj

There is no method insertDocumentFromURL for a Writer frame or spreadsheet frame.

I have never used insertDocumentFromURL.
A quick search on this forum indicates that it is a text cursor method to insert the contents of a text document.
https://forum.openoffice.org/en/forum/v ... RL#p380711

Writing macros with Open/LibreOffice is pointless. Too much effort for sparse results while nobody is willing to pay for this oversophisticated bullshit.


Oh cmon the whole point of open source is to help other people..if you like helping other people then you'll like open source. Lots of businesses are going to open office so you could probably get a job working in open office nowadays.

Here is how to create a sheet without creating a new file from the documentation..its seems to be where the other guy is stuck..

Code: Select all   Expand viewCollapse view
Dim Doc As Object
Dim Sheet As Object

Doc = ThisComponent

If Doc.Sheets.hasByName("MySheet") Then
   Sheet = Doc.Sheets.getByName("MySheet")
Else
   Sheet = Doc.createInstance("com.sun.star.sheet.Spreadsheet")
   Doc.Sheets.insertByName("MySheet", Sheet)
End If


You need to call Doc = ThisComponent after your script calls loadComponentFromURL

Then you will get the sheet and you can add a new page to the sheet,,,i don't know why they call it a sheet within a sheet, thought it would be a frame...still learning

A better way
Code: Select all   Expand viewCollapse view
Dim Doc As Object

Doc = ThisComponent

Doc.Sheets.insertNewByName("OtherSheet", 2)
OpenOffice 3.1 on CentOS 7
chrismjh
 
Posts: 7
Joined: Tue Jun 07, 2016 10:46 pm

Re: insertDocumentFromURL() vs loadComponentFromURL()..

Postby Villeroy » Mon Jun 20, 2016 5:06 pm

Now, that was really helpful :bravo:
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28676
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: insertDocumentFromURL() vs loadComponentFromURL()..

Postby Villeroy » Mon Jun 20, 2016 7:06 pm

In fact, it is as simple as this if you know how to work systematically.
First of all, I open the file directly to find out the correct import options.
yahoo_csv_options.png
UTF-8, comma separated, double-quoted US-dates and point decimals.
DO NOT treat quoted numerals as text.
DO interprete "special numbers"

To prove that all values imported correctly, I hit Ctrl+F8 (View>Highlight Values).
Yahoo_ImportSheet.png
Correct csv import with all blue numbers for a German [de-DE] locale setting.

Then I run one of my helper macros which gives me the full FilterOptions string of this import.
Code: Select all   Expand viewCollapse view
Sub showFilterOptions
Dim args(),i%
   args() = thisComponent.getArgs
   for i = 0 to uBound(Args())
      if args(i).Name = "FilterOptions" then inputbox "","",args(i).value
   next
End Sub

Which shows 44,34,76,1,,1033,false,true in an input box from where I can copy the string.

Then I put together a few lines of most stupid Basic code and fire up MRI to find the right method to link the new sheet:
Code: Select all   Expand viewCollapse view
Sub Main
Const cSheet = "AAPL_Link"
Const cURL = "http://download.finance.yahoo.com/d/quotes.csv?s=AAPL&f=sl1d1t1c1ohgv&e=.csv"
Const cFO = "44,34,76,1,,1033,false,true"
Const cFN = "Text - txt - csv (StarCalc)"
xsh = ThisComponent.getSheets()
if not xsh.hasByName(cSheet) then xsh.InsertNewByName(cSheet, 0)
sh = xsh.getByName(cSheet)
'globalscope.basiclibraries.loadlibrary("MRILib")
'mri sh
REM com.sun.star.sheet.XSheetLinkable
sh.link(cUrl, cSheet, cFN, cFO, com.sun.star.sheet.SheetLinkMode.VALUE) 
End Sub
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28676
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 7 guests