I want to open up a spreadsheet in calc, search for specific cells or values, and then paste them into a different spreadsheet. I am trying to automate this so, is it possible to do so entirely in the command line?
There are several ways to solve such tasks. Unfortunately, you cannot perform this action only using command line parameters - you will need to use a macro.
This is the most widespread, long-used and repeatedly tested method is this.
A macro (a set of procedures and functions) is created in a separate document, which:
- opens the source file
- collects the necessary data
- closes (if necessary) the original file
- opens (or creates) an output file
- puts in it (if necessary) the collected data
- saves and closes the output file
- closes the current file (itself)
Several explanations for each step:
- The source file name can be fixed, in which case it can be hard-coded into the macro code or written into a specific cell of this spreadsheet. It can be a variable - then you can pass the path and filename as a parameter when opening the file from the command line. There is a solution in which all the source files are located in a specific folder - in this case, the macro processes each of them and, after importing the data, deletes the processed file.
- How you find and retrieve the data you need depends on the structure of the source file and greatly affects the algorithm you create for this step. It can be data extraction from cells with fixed addresses, from named ranges. This could be a search for specific keywords on the sheet. In the case of a table (for example, a CSV file), this would be a simple loop through all rows.
- There is a possibility that the source file was already open before running the macro and you want to leave it that state after the program finishes. Function OpenDocument() from the Standard Library Tools demonstrates one way. When you close the file, you can close it without saving any accidental changes, or vice versa - add the note “Processed YYYY-MM-DD” to the file and save it with such a note.
- If you are going to accumulate data in a file, you should provide protection against data duplication in the event of an unintended re-run of the macro for already processed source files. This sometimes makes the processing algorithm very complicated. Therefore, the preferred option is when the macro creates a new file every time it is run. Final processing (merging with the cumulative table) can be done manually.
- It is at this step that checks are performed - whether the selected data is needed (for example, “QTY is greater than zero” or “whether this data was already entered when the macro was last run”)
- No comments
- If this document is the only open spreadsheet, then closing it will close the office
Now assign this macro to the Document Open event of this spreadsheet and you can open it from the command line (just make sure that the launch rights will allow you to run the macro without unnecessary questions and warnings)