0

In libre-office I need to load a lot of data from one file into another file. To load the cell content, I use to code below:

=DDE("soffice";"file://path-to-my-file/my-file.xlsx";"mysheet.A2")

Problem: I need to load a lot of cells, and I can not write every single DDE command by hand (or copy-paste it)

The cell numbers from which I need to copy follow a simple rule, they are increments of ten. An example of the code I would need for my cells is given below with the cell numbers in <brackets>.

cell1: =DDE("soffice";"file://path-to-my-file/my-file.xlsx";"mysheet.A<X>")

cell2: =DDE("soffice";"file://path-to-my-file/my-file.xlsx";"mysheet.A<X+10>")


cell3: =DDE("soffice";"file://path-to-my-file/my-file.xlsx";"mysheet.A<X+20>")

I thought if I just write 3 lines of the cells and then copy the cells by dragging them down, this number would increase automatically by the appropriate amount. But this does not work, it just copies the three entries over and over again.

Is there a simple way to 'code' this, such that I do not have to write/copy all cells manually?

ABot
  • 101

1 Answers1

0

The function I was looking for is called OFFSET and exists both in EXCEL and Libre Office.

=OFFSET('file:///home/path/to/my/file/my-file.xlsx'#$my_sheet.$B$2, (ROW()-1)*3,0)
  1. The first argument is the full path to my-file.xlsx including the sheet and ending in the cell. IMPORTANT: the $ before both the row and column are absolutely mandatory.
  2. The second argument is the row selection. The *10 selects every 10th row. The -1 is necessary as in my target notebook I am relatively shifted by one row to the position of the original data.
  3. The third argument could be used for the column selection. Since I am only interested in row selection, I leave this at zero.

If this cell is dragged down, the other cells are filled with the desired values.

The syntax I'm describing here differs for Microsoft Office, LibreOffice and OpenOffice slightly. E.g. the character used for separating arguments is either ',' or ';'.

ABot
  • 101