Ask Your Question
0

Best way to transfer data from one workbook/sheet to another on a reoccurring basis.

asked 2019-01-24 06:00:45 +0200

labud gravatar image

updated 2019-01-24 06:01:40 +0200

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 ...
(more)
edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
0

answered 2019-01-24 16:29:18 +0200

peterwt gravatar image

In the past I have found problems when opening a xlsm Macro Enabled Workbook in LO. It may or may not work.LO attempts to convert the VBA code into LO Basic. Some VBA commands may not work. In your case you had the wrong location for your Workbook2. You modified the Macro and saved the xlsm Workbook. Did you not get an error message to the effect that you are saving in a Macro-free document and that if you continue the Macros will not be saved? If you click continue it will be saved confusingly with an xlsm extension but things like Macros and buttons will be lost. It appears that LO does not support the saving in xlsm format and this format is not listed in the file types in the save dialogue. I have found that if you save the Workbook as a LO ods Workbook it will work.

edit flag offensive delete link more
0

answered 2019-01-27 21:16:20 +0200

JohnSUN gravatar image

It seems to me that your task can be solved with the help of a slightly different code:

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("G:\GNGRen\Workbook2.xlsx"), 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()
    oDoc.Close (True)
End Sub
edit flag offensive delete link more

Comments

thanks JohnSUN I tried your code and while it does open the correct goto workbook, it does so only momentarily. I removed

oDoc.Close (True)

and the workbook would stay open but it was blank- no lines/headings/data and it does not work when you click on the command buttton on the 1st workbook, only when I run the macro Tools>Macros>Run I am not too sure I understand all the coding, but is there something else that needs to be done to for it to be able to work fom pushing the button on workbook 1?

labud gravatar imagelabud ( 2019-01-28 03:16:24 +0200 )edit

It seems to me that you are missing two points: the executed macro for the button should be explicitly specified in the properties of the button in the Events tab and you must have at least some data in the cells of column D of the "Service Invoice" sheet of the current book, otherwise the macro simply has nothing to copy

JohnSUN gravatar imageJohnSUN ( 2019-01-28 07:17:05 +0200 )edit

Yes JohnSUN I reworked the command button and that has corrected that problem. It was not pointed to anything. However, even though I have entered data into 'D' cells the workbook2 still shows all blank. I will continue to check things to make sure I have not erred elsewhere. Thanks

labud gravatar imagelabud ( 2019-01-29 04:22:42 +0200 )edit

JohnSUN I changed a line in your code:

nNewRowNum = oCursor.RangeAddress.EndRow + 2

to

nNewRowNum = oCursor.RangeAddress.EndRow + 1

and now the second worksheet is opening With headings and lines as it should, but, alas It is still not transferring the data, even though I have put data in the cells of column D. Would you have any ideas how to solve this?
Thanks

labud gravatar imagelabud ( 2019-02-07 01:28:11 +0200 )edit

This was a wrong change. So the macro will start recording not from the first empty row, but from the last filled row - it will overwrite the previous data.

It is impossible to understand without sample files what is your problem. Modify your original question and add files with data and macro. Mark with color the cells whose data should be copied to G:\GNGRen

JohnSUN gravatar imageJohnSUN ( 2019-02-07 09:49:54 +0200 )edit
0

answered 2019-01-25 04:50:22 +0200

labud gravatar image

Thank You peterwt for your help.

Did you not get an error message to the effect that you are saving in a Macro-free document and that if you continue the Macros will not be saved? If you click continue it will be saved confusingly with an xlsm extension but things like Macros and buttons will be lost.

Yes I did get that message, but felt that I needed to save as .xlsm. Obviously, wrong choice. I dumped that copy and opened another and after making required changes in the macro [re:proper location where I was sending the data to] I saved Workbook1 as a .ods, reopened the workbook and all the formatting was back. I did a test run and clicked on the 'Transfer" button and Workbook2 opened and a line for the data in Workbook1 was there, except for the numbers. So, text only no numbers. Something else in the coding needs changing?

I tried it a second time and nothing happened when i keyed the 'Transfer' button, but I went to Tools>Macros>Run Macro and it did run the macro but only text appeared again. Having got these results, I am hopeful this is integratable into LO calc.

Opinions and advice, always needed. Is there another place [forum] where a discussion about these matters and solutions may be had without overstepping the rules of this site? Thank you

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-01-24 06:00:45 +0200

Seen: 166 times

Last updated: Jan 27