I am very new to this.
The macro seems to run...exporting the sheet to CSV naming it as '1' increasing the cell value on A2 by 1 (to 2) but then I get a basic run time error '9' on this line
Dim Propval(1) as New com.sun.star.beans.PropertyValue
I am wanting to export the same sheet to 66 seperate csv files all based on 66 different values in A2
: -
Code: Select all
Sub Repeat()
Dim i
for i = 1 to 66
Dim Document As Object
Dim Sheets As Object
Dim Sheet As Object
Document = ThisComponent 'assigns the current document to the variable document
Sheets = Document.Sheets 'get the container of all Sheets
Sheet = Sheets.getByName("EXPORT") 'get the sheet named EXPORT
Document.CurrentController.setActiveSheet(Sheet)
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 = , 34 = "
path = ThisComponent.getURL()
arr=Split(path, "00-") 'to get rid of the "fileName.ods" (I have to name my original worksheet with an initial "00-")
path=arr(0)
Sheet = Sheets.getByName("Week Number") 'get the sheet named Week number
title = Sheet.getCellByPosition(0,1) ' to take the string inside the cell A1 with which I want to nominate the exported .csv
FileName = path + title.string + ".csv" 'put file path and title togethere
FileURL = convertToURL(FileName)
Document.StoreToURL(FileURL, Propval()) 'why does this save also the orginal ODS rather than just exporting the sheet "EXPORT" as .csv?
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Nr"
args1(0).Value = 1
dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args1())
Range("A2").Value=Range("A2").Value+1
next i
End Sub