[Solved] Macro export to csv multiple times

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
konsuijotai
Posts: 3
Joined: Wed Aug 28, 2019 6:46 pm

[Solved] Macro export to csv multiple times

Post 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

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
Last edited by konsuijotai on Wed Aug 28, 2019 9:19 pm, edited 2 times in total.
Office 4.1.6
Win10
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Macro export to csv multiple times

Post by RoryOF »

A guess: try not dimensioning Dim Propval(1); leave it as Dim Propval
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Macro export to csv multiple times

Post 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

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
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
konsuijotai
Posts: 3
Joined: Wed Aug 28, 2019 6:46 pm

Re: Macro export to csv multiple times

Post 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

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
Office 4.1.6
Win10
Post Reply