vba 用于 CSV 保存的 openoffice calc 宏
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/1319136/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
openoffice calc macro for CSV saving
提问by ojreadmore
Given: I recorded a simple macro in Openoffice to save my worksheet as a CSV file. Here it is.
鉴于:我在 Openoffice 中录制了一个简单的宏,将我的工作表保存为 CSV 文件。这里是。
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.csvwill be coming from filename.xls. 2. Well, I'll need to do some regex replacement on that filename-revision01.xlsto ultimately get filename.csv.
问题:我想为这个函数添加一些特性。1. 我需要获取当前的 XLS 文件名,以便将其放在静态路径的末尾。所以 file:///path/csv/ 将始终保持不变,而filename.csv将来自filename.xls。2. 好吧,我需要对该filename-revision01.xls进行一些正则表达式替换,以最终获得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!?
顺便说一句,这种语言叫什么!?
采纳答案by ojreadmore
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.
这是我想出的解决方案,用于帮助一键导出 CSV(带有我的导出选项、文件名调整和文件位置)。
我在 Mac 上,所以文件路径将用于这样的操作系统。帮助我做到这一点的信息在这里。
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", ".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.
这是上述宏所依赖的一些 javascript。此文件名为 ~/Library/Application\ Support/OpenOffice.org/3/user/Scripts/javascript/Tools/Regex.js 并且已硬编码并在上面引用。
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 postgives details on how to add a toolbar button to run your macro with one click.
最后,这篇文章详细介绍了如何添加工具栏按钮以一键运行宏。