Page 1 of 1
[Solved] OOo Basic Macro to open a csv file
Posted: Mon Feb 01, 2010 5:46 am
by gigern
I am migrating from MS Office to OO. To do that I will re-write my macros in the OO native language. I thought I could simply use the macro learn functions. It works for many tasks but not for opening a file. The code returned is preceded by REM and if I remove that I find there are no parameters recorded. I suspect there are at least 5 or so parameters needed.
I have spent most of today to look through the forum and have not found a solution.
My next step will be to open a CSV file from the internet. It works manually but I would like to incorporate it into a macro which automatically extracts relevant data and stores it in a calc sheet.
Hope someone can give me hints. Thanks
Nick
Re: OO Basic Macro to open a csv file
Posted: Mon Feb 01, 2010 7:40 am
by FJCC
The macro recorder is not a good way to learn how to write macros. It is very limited in what it can do, as you have seen, and it uses a different method of controlling the OOo program than is typically used in human-written macros. Here is some code that opens a comma delimited file with a hard coded name.
Code: Select all
Sub CSVImport
Dim Propval(1) as New com.sun.star.beans.PropertyValue
Propval(0).Name = "FilterName"
Propval(0).Value = "Text - txt - csv (StarCalc)"
Propval(1).Name = "FilterOptions"
Propval(1).Value ="44,34,0,1,1" 'ASCII 44 = comma. See Developer's Guide Page 637
FileName = "C:\Documents and Settings\username\Desktop\Archivo.txt" '
FileURL = convertToURL(FIleName)
oCSV = StarDesktop.loadComponentFromURL(FileURL, "_blank", 0, Propval())
End Sub
A good place to learn about writing macros is Andrew Pitonyak's
English Macro Document.
Re: OO Basic Macro to open a csv file
Posted: Mon Feb 01, 2010 12:06 pm
by gigern
Thanks, that works! My remaining problem is that excel will open web pages which are not csv. When I try that with the macro the page opens in the writer instead. There is not likely a way around that, because if I try to open such a page with calc directly the program hangs. Yet if I open the page with Writer it opens it in as a table (the site is
http://newsvote.bbc.co.uk/1/shared/fds/ ... efault.stm). In excel I can open it using a macro and then retrieve the values I am interested in to update my excel sheet.
Never the less I am impressed with the progress OO has made and would not be surprised if it would become the default standard in the not too distant future!