| View previous topic :: View next topic |
| Author |
Message |
sylvaticus
Power User
Joined: 15 Jun 2006
Posts: 50
|
 Posted: Wed Oct 04, 2006 7:41 am Post subject: Saving calc sheets to CSV without prompts |
 |
|
Hello.. I am very new to OO macro (altrough I have same experience in other programming environments).
I am trying to make a macro to export my sheets in CSV.
Up now I get how to export a single sheet, but I am wondering how to "move" from one sheet to the other (in VBA terminology "activate" or "select") in order to loop over the various sheets.
The second point is how to get rid of the window box "Attencion, it was saved only the current sheet"..
The macro I am employing is:
| Code: |
sub export2Csv
' define variables
dim document as object
dim dispatcher as object
' get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
If Not GlobalScope.BasicLibraries.isLibraryLoaded("Tools") then
GlobalScope.BasicLibraries.loadLibrary("Tools")
Endif
sDocUrl = ThisComponent.URL
sDocPath = DirectoryNameoutofPath(sDocUrl, "/")
'sDocFileName = FileNameoutofPath(sDocUrl, "/")
'sDocFileNameExtension = GetFileNameExtension(sDocUrl)
'sDocFileNameWithoutExtension = GetFileNameWithoutExtension(sDocUrl, "/")
filenameToSave= sDocPath+"/objects.csv"
dim args1(5) as new com.sun.star.beans.PropertyValue
args1(0).Name = "URL"
args1(0).Value = filenameToSave
args1(1).Name = "FilterName"
args1(1).Value = "Text - txt - csv (StarCalc)"
args1(2).Name = "FilterOptions"
args1(2).Value = "59,34,22"
args1(5).Name = "SelectionOnly"
args1(5).Value = true
dispatcher.executeDispatch(document, ".uno:SaveAs", "", 0, args1())
end sub
|
|
|
| Back to top |
|
 |
sylvaticus
Power User
Joined: 15 Jun 2006
Posts: 50
|
| Posted: Thu Oct 05, 2006 3:48 am Post subject: |
|
|
I ended with this macro but I still have to understand how to change the target of the saveAs action to the different sheets.. anyone can help???
| Quote: |
sub export2Csv
If Not GlobalScope.BasicLibraries.isLibraryLoaded("Tools") then
GlobalScope.BasicLibraries.loadLibrary("Tools")
Endif
myDoc = ThisComponent
myDocUrl = ThisComponent.URL
myDocPath = DirectoryNameoutofPath(myDocUrl, "/")
'sDocFileName = FileNameoutofPath(sDocUrl, "/")
'sDocFileNameExtension = GetFileNameExtension(sDocUrl)
'sDocFileNameWithoutExtension = GetFileNameWithoutExtension(sDocUrl, "/")
Dim sheetNames(), s%
Dim filterProperties(5) as new com.sun.star.beans.PropertyValue
Dim emptyFilterProperties()
' Define here the sheetnames you want to export...
sheetNames = Array ("settings", "objects", "activities", "resources")
For i = LBound(sheetNames()) To UBound(sheetNames())
' here I'd like to change the target to the various sheets....
filenameToSave= myDocPath+"/"+sheetNames(i)+".csv"
filterProperties(0).Name = "URL"
filterProperties(0).Value = filenameToSave
filterProperties(1).Name = "FilterName"
filterProperties(1).Value = "Text - txt - csv (StarCalc)"
filterProperties(2).Name = "FilterOptions"
filterProperties(2).Value = "59,34,22"
filterProperties(5).Name = "SelectionOnly"
filterProperties(5).Value = true
myDoc.storeToURL(filenameToSave,filterProperties())
Next
myDoc.storeAsURL(myDocUrl,emptyFilterProperties())
end sub
|
|
|
| Back to top |
|
|
sylvaticus
Power User
Joined: 15 Jun 2006
Posts: 50
|
| Posted: Thu Oct 05, 2006 4:05 am Post subject: I get it working ;-))) |
|
|
Finally I get it working. 
The following macro will export all sheets defined in sheetNames() as CSV files with the same name under the same directory of the original ODS file, using as field separator the ";". If you want other field separator you need to modify FilterOption.
| Code: |
sub export2Csv
If Not GlobalScope.BasicLibraries.isLibraryLoaded("Tools") then
GlobalScope.BasicLibraries.loadLibrary("Tools")
Endif
myDoc = ThisComponent
myFrame= ThisComponent.CurrentController.Frame
myDocUrl = ThisComponent.URL
myDocPath = DirectoryNameoutofPath(myDocUrl, "/")
Dim sheetNames(), s%
Dim filterProperties(5) as new com.sun.star.beans.PropertyValue
Dim emptyFilterProperties()
Dim targetCell(1) as New com.sun.star.beans.PropertyValue
oDisp = createUnoService("com.sun.star.frame.DispatchHelper")
' Define here the sheetnames you want to export...
sheetNames = Array ("settings", "objects", "activities", "resources")
For i = LBound(sheetNames()) To UBound(sheetNames())
targetCell(0).Name= "ToPoint" : targetCell(0).Value = sheetNames(i)+".$A$1"
oDisp.executeDispatch(myFrame, ".uno:GoToCell", "", 0, targetCell())
filenameToSave= myDocPath+"/"+sheetNames(i)+".csv"
filterProperties(0).Name = "URL"
filterProperties(0).Value = filenameToSave
filterProperties(1).Name = "FilterName"
filterProperties(1).Value = "Text - txt - csv (StarCalc)"
filterProperties(2).Name = "FilterOptions"
filterProperties(2).Value = "59,34,22"
filterProperties(5).Name = "SelectionOnly"
filterProperties(5).Value = true
myDoc.storeToURL(filenameToSave,filterProperties())
Next
myDoc.storeAsURL(myDocUrl,emptyFilterProperties())
end sub
|
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
