1

Following this question, I want to know how I can specify a column/row of a specific sheet including a categorical variable, in Open/LibreOffice BASIC script/macro and get the unique list (i.e., set) of the values.

For example, consider the below dataset:

        A,   B,      C
1. gender, age, height
2.    man,  34,    180
3.  woman,  23,    155
4.    man,  54,    165
5.  other,  37,    172
6.  woman,  19,    169

Now I want to specify the column tag A or header gender in Sheet1 and get the unique values stored in an array woman, man, other. It should be noted that

  • empty cells must be ignored
  • the length of the column is not known

I would appreciate if you could help me know how I can write a BASIC function which gets the column tag or header and the sheet name and returns the set of the categorical values as mentioned above. Thanks for your support in advance.

Foad S. Farimani
  • 12,396
  • 15
  • 78
  • 193
  • 1
    Sets are built into Python; are you sure that Basic is your choice? As for finding the last row, see if any of these answers help - https://ask.libreoffice.org/en/question/145593/select-last-cell-in-column-with-a-macro/. – Jim K Mar 04 '20 at 18:13
  • I have actually already decided to move on to Python. However, I can't find the Python executable as I have asked [here](https://ask.libreoffice.org/en/question/232282/). – Foad S. Farimani Mar 04 '20 at 18:15
  • @JimK BTW If I figure out the reading the column/row into an array, then converting the array into a set shouldn't be impossible. for example [here](https://stackoverflow.com/questions/3017852/vba-get-unique-values-from-array). – Foad S. Farimani Mar 04 '20 at 18:18
  • 1
    I have successfully tested python-uno macros on macOS in the past, so it should be possible. However, I only own systems with windows or linux. Hopefully, someone on the askLO site has macOS and can answer your question. – Jim K Mar 04 '20 at 18:25
  • @JimK yeah the Python scripts run. but the location of the executable is not known! ¯\\_(ツ)_/¯ – Foad S. Farimani Mar 04 '20 at 18:31
  • Try `python -v` in a terminal, or run from code: https://stackoverflow.com/questions/749711/how-to-get-the-python-exe-location-programmatically. – Jim K Mar 04 '20 at 18:33
  • @JimK Well, Fining my Python executable is no problem. The issue as I have explained [here](https://ask.libreoffice.org/en/question/232282/) Is that I can't find the Python executable embedded into the LO. So it seems the `/Applications/LibreOffice.app/Contents/MacOS/soffice` is the Python executable which doesn't make sense! – Foad S. Farimani Mar 04 '20 at 18:38
  • @JimK I asked the same question with python [here](https://stackoverflow.com/q/60541655/4999991) – Foad S. Farimani Mar 05 '20 at 09:12

0 Answers0