insertDocumentFromURL() vs loadComponentFromURL()..

Creating a macro - Writing a Script - Using the API
Post Reply
Mandrake
Posts: 7
Joined: Sun Jun 12, 2016 2:01 am

insertDocumentFromURL() vs loadComponentFromURL()..

Post by Mandrake »

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

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

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
User avatar
Villeroy
Volunteer
Posts: 30885
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: insertDocumentFromURL() vs loadComponentFromURL()..

Post by Villeroy »

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

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Mandrake
Posts: 7
Joined: Sun Jun 12, 2016 2:01 am

Re: insertDocumentFromURL() vs loadComponentFromURL()..

Post by Mandrake »

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
User avatar
Villeroy
Volunteer
Posts: 30885
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: insertDocumentFromURL() vs loadComponentFromURL()..

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Mandrake
Posts: 7
Joined: Sun Jun 12, 2016 2:01 am

Re: insertDocumentFromURL() vs loadComponentFromURL()..

Post by Mandrake »

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
User avatar
Villeroy
Volunteer
Posts: 30885
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: insertDocumentFromURL() vs loadComponentFromURL()..

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Mandrake
Posts: 7
Joined: Sun Jun 12, 2016 2:01 am

Re: insertDocumentFromURL() vs loadComponentFromURL()..

Post by Mandrake »

The methods I used come from this https://www.amazon.co.uk/Learn-OpenOffi ... 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
User avatar
Villeroy
Volunteer
Posts: 30885
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: insertDocumentFromURL() vs loadComponentFromURL()..

Post by Villeroy »

Code: Select all

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.
viewtopic.php?f=20&t=82242&p=380711&hil ... 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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
ThierryT
Posts: 11
Joined: Fri May 13, 2016 8:52 pm

Re: insertDocumentFromURL() vs loadComponentFromURL()..

Post by ThierryT »

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
chrismjh
Posts: 7
Joined: Tue Jun 07, 2016 10:46 pm

Re: insertDocumentFromURL() vs loadComponentFromURL()..

Post by chrismjh »

Villeroy wrote:

Code: Select all

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.
viewtopic.php?f=20&t=82242&p=380711&hil ... 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

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

Dim Doc As Object
 
Doc = ThisComponent
 
Doc.Sheets.insertNewByName("OtherSheet", 2)
OpenOffice 3.1 on CentOS 7
User avatar
Villeroy
Volunteer
Posts: 30885
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: insertDocumentFromURL() vs loadComponentFromURL()..

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 30885
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: insertDocumentFromURL() vs loadComponentFromURL()..

Post by Villeroy »

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.
UTF-8, comma separated, double-quoted US-dates and point decimals. <br />DO NOT treat quoted numerals as text.<br />DO interprete &quot;special numbers&quot;
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).
Correct csv import with all blue numbers for a German [de-DE] locale setting.
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

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

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply