Just starting to learn/use Libre Office Calc, after many years of various forms of MS Excel.
I have 2 workbooks that I use constantly.
Data is entered in Workbook1 . This workbook has a command/push button set up in a macro.
Currently, in Excel, I have a small macro written that will transfer certain data to Workbook2 when I press this button.
I have read somewhat conflicting reports[Google] of whether Excel macros work in Libre Office Calc.
Of course, LO Calc recognizes my spreadsheets, [.xlsx/.xlsm] asks me whether I want to enable macros.
It also shows my workbook in Tools>Macros>Organize Macros.
After opening Workbook1 and choosing to enable Macros , when I press my ‘Command’ button I get an
Object not accessible The object
cannot be accessed due to insufficient
rights.
I then click OK on that msg and a new window opens with the title Workbook1.xls.m.VBAProject - LibreOffice Basic.
I get another message that states:
BASIC runtime error. ‘1’
Type:com.sun.star.uno.RuntimeException
Message: unsatisfied query for
interface of type
com.sun.xtar.sheet.XSpreadsheetDocument
and it is pointing to this line of my macro:
Set GNGInvDBase = Workbooks.Open("G:\GNGRen\Workbook2.xlsx")
which obviously has to do with MS Excel and Windows 10 so,
I changed the path to reflect the place where I have the Workbook2 stored in my Linux PC, saved the macro and closed it and Workbook1.xlsm.
When I reopen Workbook, I still get asked to enable macros, but when it opens all my formatting is gone, including the command/push button. I cannot access the macro any more to change the line back.
Can someone advise whether this EXCEL macro will ever work in LO CALC?
If the macro will not work, please advise of my options to achieve the same thing in LO Calc [templates, macros, databases, etc.] The same thing being transfering data from one workbook/sheet to another on a reoccurring basis where a a time period of data would be collected in Workbook2 from a day to day transferrance of data from Workbook1.
Is it possible to find a comparioson sheet to rework the macro into LO Calc macro?
Thank You.
This is the Excel Macro:
Private Sub CommandButton1_Click()
Dim FILE As String
Dim INVOICE As Single
Dim DATEs As Date
Dim TOTAL As Currency
Dim Labour As Currency
Dim Subtotal As Currency
Dim MatSubTotal As Currency
Dim MatList As Currency
Dim HST As Currency
Dim Rentals As Currency
Dim Tipping As Currency
Dim Permits As Currency
Dim Other As Currency
Dim Paid As String
Dim BillTo As String
Dim Workbook2 As Workbook
Worksheets("Service Invoice").Select
FILE = Range("D49")
Worksheets("Service Invoice").Select
INVOICE = Range("D6")
Worksheets("Service Invoice").Select
DATEs = Range("D4")
Worksheets("Service Invoice").Select
TOTAL = Range("D41")
Worksheets("Service Invoice").Select
Labour = Range("D34")
Worksheets("Service Invoice").Select
Subtotal = Range("D35")
Worksheets("Service Invoice").Select
MatSubTotal = Range("D32")
Worksheets("Service Invoice").Select
MatList = Range("D33")
Worksheets("Service Invoice").Select
HST = Range("D36")
Worksheets("Service Invoice").Select
Rentals = Range("D37")
Worksheets("Service Invoice").Select
Tipping = Range("D38")
Worksheets("Service Invoice").Select
Permits = Range("D39")
Worksheets("Service Invoice").Select
Other = Range("D40")
Worksheets("Service Invoice").Select
BillTo = Range("D7")
Set GNGInvDBase = Workbooks.Open("G:\GNGRen\Workbook2.xlsx")
Worksheets("Tracking").Select
RowCount = Worksheets("Tracking").Range("a1").CurrentRegion.Rows.Count
With Worksheets("Tracking").Range("a1")
.Offset(RowCount, 0) = FILE
.Offset(RowCount, 1) = INVOICE
.Offset(RowCount, 2) = DATEs
.Offset(RowCount, 3) = TOTAL
.Offset(RowCount, 4) = Labour
.Offset(RowCount, 5) = Subtotal
.Offset(RowCount, 6) = MatSubTotal
.Offset(RowCount, 7) = MatList
.Offset(RowCount, 8) = HST
.Offset(RowCount, 9) = Rentals
.Offset(RowCount, 10) = Tipping
.Offset(RowCount, 11) = Permits
.Offset(RowCount, 12) = Other
.Offset(RowCount, 13) = BillTo
End With
Workbook2.Save
End Sub