External VB script used for Excel now for Calc

[PBlan]

I have a vb script that I run as a daily scheduled task (win XP) for microsoft excel.
The script copies the content of some cells to another document (template that adds layout) and saves it in another folder
I would now like to use the script on another pc with calc ... that does not seem to be so easy
any help would be greatly appreciated.
the script:

Code: Select all

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\A.xls")
Set objWorksheet = objWorkbook.Worksheets("book1")
Set objRange = objWorksheet.Range("A1:A1500")
objRange.Copy


Set objExcel2 = CreateObject("Excel.Application")
Set objWorkbook2 = objExcel2.Workbooks.Open("C:\B.xls")
Set objWorksheet2 = objWorkbook2.Worksheets("Data")
objWorksheet2.Range("A18").Activate
objWorksheet2.Paste


Set objRange = objWorksheet.Range("B1:B1500")
objRange.Copy

Set objWorksheet2 = objWorkbook2.Worksheets("Data")
objWorksheet2.Range("N18").Activate
objWorksheet2.Paste

Set objRange = objWorksheet.Range("A1:B1500")
objRange.Clear

objWorkbook.Save
objWorkbook2.Saveas("C:\C.xls")

[FJCC]

Code: Select all

Sub Main
fileName1 = convertToURL("C:\Users\username\Documents\A.ods")
Doc1 = StarDesktop.loadComponentFromURL(fileName1, "_blank", 0, Array())
Sheet1 = Doc1.Sheets.getByName("Sheet1")
cellrange1 = Sheet1.getCellrangeByName("A1:A1500")
Data1 = cellrange1.DataArray

fileName2 = convertToURL("C:\Users\username\Documents\B.ods")
Doc2 = StarDesktop.loadComponentFromURL(fileName2, "_blank", 0, Array())
Sheet2 = Doc2.Sheets.getByName("Sheet1")
cellrange2 = Sheet2.getCellrangeByName("A18:A1517")
cellrange2.DataArray = Data1

cellrange3 = Sheet1.getCellrangeByName("B1:B1500")
Data3 = cellrange3.DataArray

cellrange4 = Sheet2.getCellrangeByName("N18:N1517")
cellrange4.DataArray = Data3

cellrange1.clearContents(31)
cellrange3.clearContents(31)
Doc1.store()
NewfileName = convertToURL("C:\Users\username\Documents\c.ods")
Doc2.storeAsURL(NewfileName, Array())

End Sub

[PBlan]

thanks
The last line (Doc2.storeAsURL(NewfileName, Array())) is however generating an error "cannot use parenteses when calling a sub"
As it is the last line that generates the error I expected to see the data copied & the C.ods created but that is not happeng either ...yet

[FJCC]

The macro works for me. How exactly are you running it? I've never encountered that error before and in OOBasic you do use parentheses when calling a sub, so I'm puzzled.

[PBlan]

I pasted the script in notepad & then saved it with a .vbs extension
to test I now just double clicked on the script.vbs

[FJCC]

I wrote the macro as OOBasic code, so running the script in VB is very unlikely to work. There is a web site that gives tips on writing VB code that deals with OpenOffice. I think it would be fairly easy to translate the code I posted to VB, but I've hardly ever used that language.

[PBlan]

I looked at the website but writing the code from scratch to run the script in VB is too difficult for me, I would need some examples of code that do something similar, unfortunately I have not been able to find any.
Is there a part of the forum that I could re-direct this post to?
thanks for your help

[Villeroy]

http://www.kalitech.fr/clients/doc/VB_APIOOo_en.html

Code: Select all

  Set oSM = CreateObject("com.sun.star.ServiceManager")
...