[Solved] Update multiple files without opening them

[MKZ Industries]

Hello to all,

I am using LibreOffice 24.2.5.2 on a Windows 11 and I would like to know if I can update many files without opening them. Let me explain:

I have the following file structure to manage the products I make and sell from my small workshop:

• A CALC sheet with some common data: cost of materials, their weights, production times, etc.

• A CALC sheet for each type of product to calculate the price, weight, etc. depending on which materials I use, the size (the same product can have more than 100 size combinations), etc. These spreadsheets have external links to the previous spreadsheet to read the data they need for the calculations.

• A CALC sheet that reads from all the product sheets the final price and lists them all together.

The problem is that I currently have about 150 different products (and counting) and every time I change a piece of data (say the price of a material has changed), I have to open all of them, update them (either manually or automatically) and save them so that all the prices are recalculated, and in turn these appear updated when I open the third CALC sheet of the price list.

Is there any way to do this automatically without having to open each and every spreadsheet?

Thank you very much for your help and best regards,

[MrProgrammer]

Is there any way to do this automatically without having to open each and every spreadsheet?

Not unless you write a macro. Either you open the spreadsheets or a macro opens them. Create a new topic in Macros and UNO API if you want to explore that idea. You can also ask a Moderator to move this topic to that forum. Expect to spend a week or so writing your first macro, more if you're not already a programmer. You will find links in the macro forum to help people learn about macros. This is not going to be an easy first macro project. A recorded macro cannot open and close files.

A Calc sheet with some common data …
A Calc sheet for each type of product …
A Calc sheet that reads from all the product sheets …

We generally use the term spreadsheet to refer to a Calc file, that is, an ODS file like Common.ods. The term sheet refers to one of the many subdivisions that a Calc spreadsheet can contain, where Sheet1.A1 references cell A1 in one of the subdivisions. The sheet subdivisions are managed through the menus Edit → Sheet, Insert → Sheet, and Format → Sheet.

If the items you mention are all sheets, they can all be in one spreadsheet file. Then the need to open multiple files disappears. Or are you using the term Calc sheet to mean spreadsheet? Inter-sheet links in a spreadsheet provide more features and are faster than external links. Calc must open the target spreadsheet for an external link in the background before it can access the linked data. When a cell in a sheet is updated, inter-sheet links in that spreadsheet immediately use the new value. When a cell in an external spreadsheet is updated, the file must be saved before the new value is available via an external link.

If you want to keep each type of product in a separate spreadsheet file, you can use the file manager of your operating system to open big groups of them at once. You'd have to experiment how practical it is on your computer to have Calc open hundreds of spreadsheets at once. I don't use Windows but on MacOS I can select dozens of files in Finder and use File → Open to open all of them, then use File → Save All after Calc has refreshed the links.

You are more likely to get further assistance in this topic if you attach some data demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the spreadsheet itself). The Calc forum is not the correct one to ask about macros. You risk having your topic locked if you ask about writing macros in the Calc forum.

… products I make and sell from my small workshop:

Since this data is for a business you will want to ensure that you have adequate backups. Read section 6. Saving your data and making backups in Ten concepts that every Calc user should know.

[Villeroy]

Hello to all,

I am using LibreOffice 24.2.5.2 on a Windows 11 and I would like to know if I can update many files without opening them.

This is impossible to do. All spreadsheet data need to be in memory when you edit them.

I have the following file structure to manage the products I make and sell from my small workshop:

• A CALC sheet with some common data: cost of materials, their weights, production times, etc.

• A CALC sheet for each type of product to calculate the price, weight, etc. depending on which materials I use, the size (the same product can have more than 100 size combinations), etc. These spreadsheets have external links to the previous spreadsheet to read the data they need for the calculations.

• A CALC sheet that reads from all the product sheets the final price and lists them all together.

What you describe is a database. Calc is not a database application.

[robleyd]

Cross posted on AskLO where suggestions have been offered.

If you cross post, as a courtesy please let us know that you have done so, otherwise it leads to several discussions and a waste of time because several identical answers may be posted by different users.

[MKZ Industries]

We generally use the term spreadsheet to refer to a Calc file, that is, an ODS file like Common.ods. The term sheet refers to one of the many subdivisions that a Calc spreadsheet can contain, where Sheet1.

Noted! When I wrote sheet I meant spreadsheet, at this moment they are all different files.

If the items you mention are all sheets, they can all be in one spreadsheet file.

Putting all the spreadsheets together in a single document would not be manageable (they are more than 150, each product spreadsheet can have hundreds of lines and they all have from column A to DZ occupied), but I could put them together by categories so that I don't have to open so many of them.

File → Open to open all of them, then use File → Save All after Calc has refreshed the links.

This is a good tip, thanks! Combined with the previous idea could save me a lot of time.

Thank you very much for your answer, it has been very enlightening and has given me some ideas, although not to do what I wanted to do, but to save some time.

[MKZ Industries]

What you describe is a database. Calc is not a database application.

Yes, it was clear to me that my system was not the best, but I have never worked with databases and at the beginning doing it in CALC was the easiest way for me.

Also because the calculations I do in the product spreadsheets are quite complex and I didn’t know if that can be done in a database (I have columns from A to DZ, because in addition to the calculations, many of the columns are configured to be exported to the database of my online shop).

Thanks anyway, I will explore also the database solution.

[MKZ Industries]

Cross posted on AskLO where suggestions have been offered.

If you cross post, as a courtesy please let us know that you have done so, otherwise it leads to several discussions and a waste of time because several identical answers may be posted by different users.

Noted! I wasn’t sure which forum was the right one to ask so I’ve put it in both.

If it happens again I will let everybody know.

[Villeroy]

With a spreadsheet, it is easy to get quick results. However, it is close to impossible to get correct results in the long run. As long as you enter more and more data, you will always need to insert new cells, adjust formula ranges, be careful when sorting, check formulas for correct references.
With a database, you are close to application development. You need to invest brain and effort once. Then you can use the application for many years without changing anything. Entered data are stored automatically row by row without loading all the data at once.

Also because the calculations I do in the product spreadsheets are quite complex

If "calculations" means lookup formulas, these will be obsolete when using a database. A database is a validation and lookup engine. Commercial calculations are very easy to do and more reliable with a database. If you really need spreadsheet calculations, everything stored in a database is accessible in Calc.