Page 1 of 1

[Solved] Macro export to csv multiple times

PostPosted: Wed Aug 28, 2019 6:57 pm
by konsuijotai
Hi can someone advise where I am going wrong with the macro below.
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   Expand viewCollapse view
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

Re: Macro export to csv multiple times

PostPosted: Wed Aug 28, 2019 7:32 pm
by RoryOF
A guess: try not dimensioning Dim Propval(1); leave it as Dim Propval

Re: Macro export to csv multiple times

PostPosted: Wed Aug 28, 2019 9:02 pm
by FJCC
I think the code below does what you want. I pulled all of the Dim statements and variable assignments that do not change out of the for loop. Also, since you had the variable title for cell A2, I used that to increment the value of A2.
Code: Select all   Expand viewCollapse view
Sub Repeat()
Dim i
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

for i = 1 to 66

  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 ? FJCC- this only exports the csv.

  title.Value = title.Value + 1   

next i
   
End Sub

Re: Macro export to csv multiple times

PostPosted: Wed Aug 28, 2019 9:19 pm
by konsuijotai
Thank for help guys


This worked perfectly! :D :bravo:

FJCC wrote:I think the code below does what you want. I pulled all of the Dim statements and variable assignments that do not change out of the for loop. Also, since you had the variable title for cell A2, I used that to increment the value of A2.
Code: Select all   Expand viewCollapse view
Sub Repeat()
Dim i
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

for i = 1 to 66

  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 ? FJCC- this only exports the csv.

  title.Value = title.Value + 1   

next i
   
End Sub