openoffice calc macro for CSV saving

Question

Given: I recorded a simple macro in Openoffice to save my worksheet as a CSV file. Here it is.

sub toCSV
dim document   as object
dim dispatcher as object
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim args1(2) as new com.sun.star.beans.PropertyValue
args1(0).Name = "URL"
args1(0).Value = "file:///path/csv/filename.csv"
args1(1).Name = "FilterName"
args1(1).Value = "Text - txt - csv (StarCalc)"
args1(2).Name = "FilterOptions"
args1(2).Value = "59,34,76,1"
dispatcher.executeDispatch(document, ".uno:SaveAs", "", 0, args1())
end sub

Problem: I want to add some features to this function. 1. I need to get the current XLS filename so that I can put that at the end of my static path. So file:///path/csv/ is going to always remain the same, and filename.csv will be coming from filename.xls. 2. Well, I'll need to do some regex replacement on that filename-revision01.xls to ultimately get filename.csv.

I can do regex matching well, I'm simply looking for hints on string concatenation, how to get the current filename, and how to write a regex expression within a macro.

BTW, what is this language called!?

Answer 1

This is the solution I came up with to help in exporting a CSV (with my export options, filename adjustment, and file location) with one click.
I'm on a Mac, so the file paths will be for such an operating system. The information that helped me do this is here.

REM  *****  BASIC  *****
sub toCSV
dim document   as object
dim dispatcher as object
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
FileURL = ThisComponent.URL
oMasterScriptProviderFactory = createUnoService("com.sun.star.script.provider.MasterScriptProviderFactory")
oMasterScriptProvider = oMasterScriptProviderFactory.createScriptProvider("")
oScriptReplace = oMasterScriptProvider.getScript("vnd.sun.star.script:Tools.Regex.js?language=JavaScript&location=user")
sReturn = RegExpReplace(oScriptReplace, FileURL, "(.*)/(\w*-\w*)(-revision\d*)+\.xls", "i", "$2.csv")
dim args1(2) as new com.sun.star.beans.PropertyValue
args1(0).Name = "URL"
args1(0).Value = "file:///Users/joe/Documents/mydocuments/trunk/my%20projects/dictionary/verbsXLS/proofed/csv/" + sReturn
args1(1).Name = "FilterName"
args1(1).Value = "Text - txt - csv (StarCalc)"
args1(2).Name = "FilterOptions"
args1(2).Value = "59,34,76,1"
dispatcher.executeDispatch(document, ".uno:SaveAs", "", 0, args1())
end sub

function RegExpReplace(oScriptReplace as Object, sSource as String, sRegExp as String, sGlobUpcase as String, sReplace as String) as String
RegExpReplace = oScriptReplace.invoke(Array(sSource, sRegExp, sGlobUpcase, sReplace ), Array(), Array())
end function

Here is a bit of javascript that the above macro relies upon. This file is named ~/Library/Application\ Support/OpenOffice.org/3/user/Scripts/javascript/Tools/Regex.js and is hardcoded and referenced above.

sSource     = String(ARGUMENTS[0])
sRegExp     = String(ARGUMENTS[1])
sGlobUpcase = (ARGUMENTS[2])
sReplace    = String(ARGUMENTS[3])
myRe   = new RegExp(sRegExp, sGlobUpcase)
ret = sSource.replace(myRe, sReplace)

Finally, this post gives details on how to add a toolbar button to run your macro with one click.

Answer 2

I am very sorry, but I am not able to run your macro. I made this js-file and placed into the right folder. Afterwards I implemented your macro in OpenOffice but I always get the following error: "BASIC-Runtime Error. An exception has occured. Type: com.sun.star.script.provider.ScriptFrameworkErrorExeption Message: Incorrect format for Script URI: vnd.sun.star.script:Tools.Regex.js?language=JavaScript&location=user"

What could be the problem?