[Solved] Macro export to csv multiple times

Creating a macro - Writing a Script - Using the API

[Solved] Macro export to csv multiple times

Postby konsuijotai » Wed Aug 28, 2019 6:57 pm

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
Last edited by konsuijotai on Wed Aug 28, 2019 9:19 pm, edited 2 times in total.
Office 4.1.6
Win10
konsuijotai
 
Posts: 3
Joined: Wed Aug 28, 2019 6:46 pm

Re: Macro export to csv multiple times

Postby RoryOF » Wed Aug 28, 2019 7:32 pm

A guess: try not dimensioning Dim Propval(1); leave it as Dim Propval
Apache OpenOffice 4.1.7 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29778
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Macro export to csv multiple times

Postby FJCC » Wed Aug 28, 2019 9:02 pm

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
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7360
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Macro export to csv multiple times

Postby konsuijotai » Wed Aug 28, 2019 9:19 pm

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
Office 4.1.6
Win10
konsuijotai
 
Posts: 3
Joined: Wed Aug 28, 2019 6:46 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 4 guests