can you read and make changes to a libreoffice workbook without opening it?

[sebisterta]

Hello,

I want to do a project at my workplace. Right now I have a data entry worksheet in libreoffice calc that helps me write some notations faster and I would like to create a second worksheet that is linked to this one and work like a database.

I will call the worksheet I already have File1 and the one that I want to create File2.

File1 is a data entry form that helps me generate notations for different scenarios , I managed to accomplish this with formulae and a macro that deletes everything once i am done with a request. File1 would work like a front end interface while File2 would be a list shared with multiple people ( it should be located on a shared folder on the local network, I don’t have that yet so at first it will be on my computer, I hope I can ask for my superiors to create one after I can prove that this can be done, if it can be done ).

The list in File2 would contain 2 columns, one with a case number and one with an order number. In File1 I introduce the case number manualy and the order number is extracted by a formula from another cell ( basically I copy paste an entire email in a cell and extract the information I need with formulas by searching beetween two values in the email). So in File1 I have a case number in cell A1 , an order number in cell B1 ( if the order number in the email is not correct I introduce it manualy in cell B2 ). In File2 I would have the case numbers in column A and order number in column B.

I was wondering if I can add a button in File1 that will check if the order number already exist in the list from File2 ( it will use the value from B2 if the B2 cell is not empty - the order number was incorrect and I had to introduce it manualy - , if the cell B2 is empty then use the value from B1 - this value is outputed by a formula ).

• If the order number does not exist in the File2 then show a message box saying that everything is fine, create a new entry in the File2 with the case number in column A and order number in column B ( the case number and order number always come in pairs ).
• If the order number exists in File2 then show a message box saying that the request is a duplicate, copy the coresponding case number from File2 in File1.A2.

The use will never open File2, only File1 so I need a macro attached to a button that can do the steps above. I think I can write the algorithm myself but I don’t know how to set up the connection beetween the two worksheets so that you can read what is inside Sheet2 and add new information to it without needing to open it ( because there will be multiple people working that need access to Sheet2 at the same time and I don’t want anyone to be able to edit it manualy and mess up the layout ).

I am sorry for the long text, I just wanted to be as specific as possible, maybe I added to many details. I don’t expect a complete solution, I just need to know how to make the connection beetween Sheet1 and Sheet2 and only have Sheet1 open, if that is even possible. If you know an alternative solution that could work would be much appreciated.

[keme]

It is most likely doable with Calc. Shared/concurrent access is supposed to work. Needs a bit of setting up. I haven’t used it myself, only read about it.

Data integrity mechanisms in Calc are rudimentary and break easily. An accidental copy/paste may be all it takes. User discipline is required. Setting up a proper database is perhaps a daunting task, but will be less prone to error.

[ajlittoz]

Considering the uniqueness constraints you impose on order number, I’d recommend to create a database for your “File2”. Base, the data base interface component, has a nice GUI to define the database tables and also to design queries.

Your “File1” is a query screen for the database. I don’t see clearly how it links to an email but there are several possible ways to handle this link.

Base is nice to model (demonstration) what you want to achieve. Making it a shared multi-user service is a bit more complicated: I’d use a form in a browser as the entry/query device and this needs setting up a web server.

[keme]

I am sorry for the long text, …

Don’t fear the punctuation!

I confess: I edited your question to split into paragraphs for legibility.

Rephrasing a few of the longest sentences may also help us to better understand your goals and needs. An attached file would be useful to see everything in context (remember this is a public place ; remove/mangle confidential data, and insert dummy data if required).

[newbie-02]

simple as your sample vlookup (vertical lookup) might be your friend,

something like ‘=vlookup(A1;“range_in_File2”;2;“match_indicator_0/1”)’ (look for the right syntax and replace “range…” and “match…” meaningful) in File1 will give you a value (from second column) picked from the row where the value in the first column matches A1,

see a sample with missing starting “=” and closing “)” in lookup-function-between-2-files/

keyboard shortcuts are mostly faster than click-buttons,

this approach is ‘fully dynamic’ (advantage over base), have your input fields, the number extracted from your email and that looked up by vlookup beneath, use conditional formatting for quick match check, fire a macro for a new record in File2 if needed,

care for access rights, network performance etc., it might be a ‘one file’ or ‘one sheet with input area’ construction is simpler, more stable and faster,

have fun …