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.