Macro help

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
lagrabow
Posts: 3
Joined: Thu Feb 07, 2019 2:37 am

Macro help

Post by lagrabow »

I am posting in the beginners section because I am a beginner in Calc, although the Calc I am referring to is Libre Office 6.1 Calc. I have been told I can ask for help here even though this is OO. I hope this is true, my apologies if it is not. Also, and please don't hold it against me, I am coming from MS Excel.

I have an workbook Invoice template from which all invoices are created. [GNGInvoice]
On this template I have a "Command_1_Click" button and when I finish filling in all the invoice fields I mouse click this button [Transfer]
At this point a macro I created kicks in to transfer data from certain fields to fields in a second workbook. [GNGInvDBase]
I Save the second workbook [GNGInvDBase] and return to the first workbook [GNGInvoice] to "Save As" which saves the Invoice and clears the template for the next invoice.
All of this was created in MS Excel and works very well.

I have often wanted to switch to another spreadsheet program and am trying to learn LO Calc.
Other than outward appearance they have a lot of similarities and I read that some Excel macros work in LO Calc.
My VBA macro does partially work in LO Calc, but not completely.
I was given this bit of code [by JohnSUN at https://ask.libreoffice.org] for LO Calc

Code: Select all

Sub CommandButton1_Click()
Dim oDoc As Object
Dim iSheet As Object,
Dim oSheet As Object,
Dim oCursor As Object,
Dim iData As Variant,
Dim oData As Variant,
Dim aRowNums As Variant,
Dim nNewRowNum As Long,
Dim i As Integer

GlobalScope.BasicLibraries.LoadLibrary("Tools") 

Rem Rows with data in column D instead Range("D49"),Range("D6"),Range("D4"), etc    aRowNums = Array(49, 6, 4, 41, 34, 35, 32, 33, 36, 37, 38, 39, 40, 7)
iSheet = ThisComponent.getSheets().getByName("Service Invoice")

Rem Get all data from column D
 iData = iSheet.getCellRangeByName("D1:D49").getDataArray()

Rem Take only the data from the specified rows
ReDim oData(0 To UBound(aRowNums))
For i = 0 To UBound(aRowNums)
oData(i) = iData(aRowNums(i)-1)(0)
Next i

Rem Output workbook (spreadsheet)
oDoc = OpenDocument(ConvertToURL("/home/DataStore/GNGRenoDebian/Macro/GNGInvDBase.xlsx"), Array())
oSheet = oDoc.getSheets().getByName("Tracking")

Rem Number of first empty row 
oCursor = oSheet.createCursor()
oCursor.GotoEndOfUsedArea(True)
nNewRowNum = oCursor.RangeAddress.EndRow + 1

Rem Paste collected data
oSheet.getCellRangeByName("A" & nNewRowNum & ":N" & nNewRowNum).setDataArray(Array(oData))

Rem Store result and close output workbook
oDoc.store()
Rem oDoc.Close (True)    this statement closes GNGInvDBase sheet instantaneously

Rem

This macro also partially works [better than the VBA].
I fill out my invoice and click Transfer button.
This macro opens my second workbook, but does not transfer the data from the invoice.
I don't know enough about LO Basic to see what might be wrong, so if anyone can have a look at it, I'd appreciate it.
I have tried to reach JohnSUN through ask.libreoffice.org but have not been able to yet.

I also want to ask if where I might find some good tutorials on Basic for LO Calc.
Thank You.
Open Office 3.1 on Debian 'Buster'
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: macro help

Post by Zizi64 »

Welcome to the forum!
This is a common Forum for Apache OpenOffice, LibreOffice and other derivatives (Neo Office, Collabora Office, ...).
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: macro help

Post by Zizi64 »

I don't know enough about LO Basic to see what might be wrong,
The "LO Basic" (the StarBasic) is a very simple programming language. There are a few of built-in Basic functions only.
But: you can/must call the thousands of the API functions from the Basic. You must study the API functions.
(API: Application Programming Interface of the opensource office suite.)

For example: When you call the "getCellRangeByName" function, then you call an API function. And you can call it from all of supported programming languages (not only from the Basic).


I suggest you: Install and use one of available object inspection tools for examine the programming objects. You can display the properties, methods, interfaces (and more) of the objects by usage the Xray Tool, or the MRI.
Find them in this Forum.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: macro help

Post by Zizi64 »

Code: Select all

oDoc = OpenDocument(ConvertToURL("/home/DataStore/GNGRenoDebian/Macro/GNGInvDBase.xlsx")
Do not store your important documents in a foreign (half-standardized) file format for the AOO, and LO. Always work in the native, International Standard ODF fileformat - mainly, particularly when you want work with macros.
At end of the work you can convert a copy ("Save as" or "Export) of your documents into the foreign file formats.


(The macros - what you want to embed into the file - will not be stored into the foreign file formats. And they will not work in the Excel.)
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: macro help

Post by UnklDonald418 »

I also want to ask if where I might find some good tutorials on Basic for LO Calc.
Andrew Pitonyak's book "OpenOffice.org Macros Explained" can be downloaded from
http://www.pitonyak.org/oo.php
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
lagrabow
Posts: 3
Joined: Thu Feb 07, 2019 2:37 am

Re: Macro help

Post by lagrabow »

Thank you UnklDonald418 and Zizi64 for replying.
I have cleaned up my
foreign (half-standardized) file format
and changed files to .ods.
Also, I had originally had some Totals at the bottom of the workbook that rec'd the data. Well below those totals I found some of the missing data. I removed the totals section to another sheet and most of the data was transferred correctly, except for one cell [A7]. All the other data to be transferred is in Column D.
So, please, if someone could point out to me where, in the macro, the cell A7 from GNGInvLO.ods gets copied to the 2nd workbook [GNGInvDBaseLO.ods]? Thank you
I am showing copies of the invoice, data sheet and macro.
https://ask.libreoffice.org/upfiles/154 ... 866136.png
https://ask.libreoffice.org/upfiles/154 ... 732994.png

Code: Select all

**Sub CommandButton1_Click()

Dim oDoc As Object, iSheet As Object, oSheet As Object, oCursor As Object
Dim iData As Variant,oData As Variant,aRowNums As Variant
Dim nNewRowNum As Long, i As Integer 


    GlobalScope.BasicLibraries.LoadLibrary("Tools")

Rem Rows with data in column D instead Range("D49"),Range("D6"),Range("D4"), etc
    aRowNums = Array(49, 6, 4, 41, 34, 35, 32, 33, 36, 37, 38, 39, 40, 7)

    iSheet = ThisComponent.getSheets().getByName("Service Invoice")

Rem Get all data from column D
    iData = iSheet.getCellRangeByName("D1:D49").getDataArray()

Rem Take only the data from the specified rows
    ReDim oData(0 To UBound(aRowNums))
    For i = 0 To UBound(aRowNums)
        oData(i) = iData(aRowNums(i)-1)(0)
    Next i

Rem Output workbook (spreadsheet)
    oDoc = OpenDocument(ConvertToURL("/home/DataStore/GNGRenoDebian/Macro/GNGInvDBase.ods"), Array())
    oSheet = oDoc.getSheets().getByName("Tracking")

Rem Number of first empty row
    oCursor = oSheet.createCursor()
    oCursor.GotoEndOfUsedArea(True)
    nNewRowNum = oCursor.RangeAddress.EndRow + 2

Rem Paste collected data
    oSheet.getCellRangeByName("A" & nNewRowNum & ":N" & nNewRowNum).setDataArray(Array(oData))

Rem Store result and close output workbook
    oDoc.store()
Rem oDoc.Close (True)  this closes sheet instantaneously

End Sub**
Open Office 3.1 on Debian 'Buster'
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Macro help

Post by Zizi64 »

Please upload real .odf samples here instead of the pictures. We can not try your macro on the pictures...
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
lagrabow
Posts: 3
Joined: Thu Feb 07, 2019 2:37 am

Re: Macro help

Post by lagrabow »

Zizi64
Here are the worksheets.
Everything is getting transferred, except cell A7.
Invoice template, where data is gathered.
LRBInvDBaseLO.ods
(14.8 KiB) Downloaded 181 times
Data is transferred via macro to this sheet.
LRBInvoiceLO.ods
(36.57 KiB) Downloaded 204 times
Macro

Code: Select all

**Sub CommandButton1_Click()

Dim oDoc As Object, iSheet As Object, oSheet As Object, oCursor As Object
Dim iData As Variant,oData As Variant,aRowNums As Variant
Dim nNewRowNum As Long, i As Integer 


    GlobalScope.BasicLibraries.LoadLibrary("Tools")

Rem Rows with data in column D instead Range("D49"),Range("D6"),Range("D4"), etc
    aRowNums = Array(49, 6, 4, 41, 34, 35, 32, 33, 36, 37, 38, 39, 40, 7)

    iSheet = ThisComponent.getSheets().getByName("Service Invoice")

Rem Get all data from column D
    iData = iSheet.getCellRangeByName("D1:D49").getDataArray()

Rem Take only the data from the specified rows
    ReDim oData(0 To UBound(aRowNums))
    For i = 0 To UBound(aRowNums)
        oData(i) = iData(aRowNums(i)-1)(0)
    Next i

Rem Output workbook (spreadsheet)
    oDoc = OpenDocument(ConvertToURL("/home/DataStore/GNGRenoDebian/Macro/GNGInvDBase.ods"), Array())
    oSheet = oDoc.getSheets().getByName("Tracking")

Rem Number of first empty row
    oCursor = oSheet.createCursor()
    oCursor.GotoEndOfUsedArea(True)
    nNewRowNum = oCursor.RangeAddress.EndRow + 2

Rem Paste collected data
    oSheet.getCellRangeByName("A" & nNewRowNum & ":N" & nNewRowNum).setDataArray(Array(oData))

Rem Store result and close output workbook
    oDoc.store()
Rem oDoc.Close (True)  this closes sheet instantaneously

End Sub**
Open Office 3.1 on Debian 'Buster'
Post Reply