[Solved] Macro with Multiple actions

[JOMA]

Hi,

I would like to build a macro for LibreOffice Calc with multiple actions:

1 Open "file1.dbf" file (located in C:/path1/file1.dbf).
2 Open "file2.xlsx" file (located in C:/path2/file2.xlsx).
3 Delete first row in "file1.dbf" file.
4 Then copy first row from "file2.xlsx" file and insert it into first row in "file1.dbf" file.
5 Save "file1.dbf" file as "file1_.xlsx" file (Excel 2007 - 365) in C:/path3/file1_.xlsx.

I have done the first two steps 1, 2 (Open two files: activos.dbf and Enc_activos_.xlsx):

----Code----

REM Open two files (file1.dbf and file2.xlsx)
Sub Main
REM open file1.dbf
FileName = "C:\path1\file1.dbf"
FileName = convertToURL(FileName)
Dim Empty() 'An (empty) array of PropertyValues
TestDoc = StarDesktop.loadComponentFromURL(FileName, "_blank", 0, Empty())
REM open file2
FileName = "C:\path2\file2.xlsx"
FileName = convertToURL(FileName)
Dim Empty2() 'An (empty) array of PropertyValues
TestDoc = StarDesktop.loadComponentFromURL(FileName, "_blank", 0, Empty())
End Sub

----Code----

Any idea about how to acomplish 3,4 and 5?

Thanks in advance.

Greetings.
JOMA

[JeJe]

Have you tried the macro recorder and searching for similar threads? For 3:

viewtopic.php?t=67858

[JOMA]

Have you tried the macro recorder and searching for similar threads? For 3:

viewtopic.php?t=67858

Hi,

Thank you for your answer, I have tried macro recorder but It doesn't work for these actions.

I,ll continue searching.

Greetings.
JOMA.

[JOMA]

Have you tried the macro recorder and searching for similar threads? For 3:

viewtopic.php?t=67858

Thank you for the tread, that thread can explain me how to do step 3 ( 3 Delete first row in "activos.dbf" file. ), but how can I do that the thread code works over the opened file sheet ("activos.dbf") ?

Thanks in advance.
Greetings.
JOMA

[JeJe]

viewtopic.php?t=107307

*

Recorded macros. Selecting the row didn't record - so I recording selecting cells along the row.
(However the link I provided gives a better answer)

Code: Select all

sub Main
selectcells
deletecells
end sub
sub deletecells

rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
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 = "Flags"
args1(0).Value = "A"

dispatcher.executeDispatch(document, ".uno:Delete", "", 0, args1())


end sub

sub selectcells
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
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 = "ToPoint"
args1(0).Value = "$A$1:$K$1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())


end sub

[Villeroy]

The macro recorder operates on a single file at a time.

When you use Calc to replace the first row of a dbf file, you replace the column labels, right?
Connect a Base document to your dbf file(s) and create queries with the wanted alias names.
File>New>Database...
Connect to existing database of type "dBase"
Specify the directory where your dBase files are stored (dBase is a database in a directory).
[X] Register database
Save the database document.
Create a query in design view, choose a table, double-click column names and write the alias names below the column names.
Save the query and database.
Close the database.
Now you can access the dBase file with renamed column labels in Calc and in Writer documents.
Calc: View>Data sources, drag the query into a Calc document,, which creates a linked copy to the dBase data with renamed column labels.

[JOMA]

The macro recorder operates on a single file at a time.

When you use Calc to replace the first row of a dbf file, you replace the column labels, right?
Connect a Base document to your dbf file(s) and create queries with the wanted alias names.
File>New>Database...
Connect to existing database of type "dBase"
Specify the directory where your dBase files are stored (dBase is a database in a directory).
[X] Register database
Save the database document.
Create a query in design view, choose a table, double-click column names and write the alias names below the column names.
Save the query and database.
Close the database.
Now you can access the dBase file with renamed column labels in Calc and in Writer documents.
Calc: View>Data sources, drag the query into a Calc document,, which creates a linked copy to the dBase data with renamed column labels.

Thank you for your answer but I´m not allowed to use Base.

Greetings.
JOMA

[JOMA]

viewtopic.php?t=107307

*

Recorded macros. Selecting the row didn't record - so I recording selecting cells along the row.
(However the link I provided gives a better answer)

Code: Select all

sub Main
selectcells
deletecells
end sub
sub deletecells

rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
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 = "Flags"
args1(0).Value = "A"

dispatcher.executeDispatch(document, ".uno:Delete", "", 0, args1())


end sub

sub selectcells
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
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 = "ToPoint"
args1(0).Value = "$A$1:$K$1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())


end sub

Thank you for the code and thread, I´ll give a look to them.

Greetings.
JOMA

[JOMA]

Hi to everybody

I got the answer:

*********************************************************************
Code:

REM ***** BASIC *****
' Open "file1.dbf" and "file2.xlsx"
' Copy first row from "file2.xlsx" to first row of "file1.dbf"
' Save "file1.dbf" as "file1_.xlsx" in folder / Excel/
' Close open files
Sub file1
Dim oDoc1 As Object
Dim oDoc2 As Object
Dim oSheet1 As Object
Dim oSheet2 As Object
Dim oCellRange1 As Object
Dim oCellRange2 As Object

' Open files
oDoc1 = StarDesktop.loadComponentFromURL(ConvertToUrl("C:\path1\file1.dbf"), "_blank", 0, Array())
oDoc2 = StarDesktop.loadComponentFromURL(ConvertToUrl("C:\path2\file2.xlsx"), "_blank", 0, Array())

' Define sheet name for both files
oSheet1 = oDoc1.Sheets.getByName("Hoja1")
oSheet2 = oDoc2.Sheets.getByName("Hoja1")

' Get the range
oCellRange1 = oSheet1.getCellRangeByPosition(0, 0, oSheet1.Columns.Count - 1, 0)
oCellRange2 = oSheet2.getCellRangeByPosition(0, 0, oSheet2.Columns.Count - 1, 0)

' opy first row from "file2.xlsx" to first row of "file1.dbf"
oCellRange1.setDataArray(oCellRange2.getDataArray())

' Save "file1.dbf" as "file1_.xlsx" in folder / Excel/
Dim sURL As String
sURL = ConvertToURL("C:\path3\Excel\file1_.xlsx")
Dim aFilterData(0) As New com.sun.star.beans.PropertyValue
aFilterData(0).Name = "FilterName"
aFilterData(0).Value = "Calc MS Excel 2007 XML"
oDoc1.storeToURL(sURL, aFilterData())

' Close open files

oDoc1.dispose
oDoc2.dispose

End Sub

*********************************************************************

Thank you to everybody.

Greetings.
JOMA