Access cellrange in stringitemlist

Creating a macro - Writing a Script - Using the API

Access cellrange in stringitemlist

Postby tmanikanda » Tue May 15, 2018 1:00 pm

Hi All,
I am new to open office and even macros. I am trying to build a dialog box with list box or combo box in it. I need to load the list box with contents from a calc spreadsheet column.

oMyCombo= oDialog1.getControl("ComboBox1").getModel()
oCellRange = oSheet_tst.getCellRangeByName("I2:I7")
arg = Array("Item1", "Item2", "Item3", "Item4", "Item5")
REM Array(oCellRange) - Not working
REM Array( "Item1", "Item2", "Item3", "Item4", "Item5" ) - Working

In the above code if i use array of strings hardcoded it is working, but if I use oCellRange inside Array, I am getting error - Incorrect property value. Please help me in this
Apache OpenOffice 4.1.3 , Windows 10
Posts: 2
Joined: Tue May 15, 2018 9:36 am

Re: Access cellrange in stringitemlist

Postby Zizi64 » Tue May 15, 2018 3:40 pm

Please upload your ODF type samle file with the embedded macro code here
Tibor Kovacs, Hungary; LO4.4.7, LO5.4.6 on Win7x64Prof.
PortableApps, winPenPack: LO3.3.0-LO6.0.2 and AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Posts: 6584
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Access cellrange in stringitemlist

Postby FJCC » Tue May 15, 2018 5:10 pm

You cannot assign the oCellRange to StringItemList because oCellRange is not an array of strings, it is an object describing a range of cells. One of its properties is a DataArray, which is an array of arrays of strings representing the cell contents. DataArray(i)(j) refers to the jth column of the ith row and you can refer to the entire ith row with DataArray(i). There is not compact way, I think, to refer to the entire jth column because those elements are in separate arrays. I haven't thought about array handling in Basic for a long time, so I may be wrong about that. If you store your values in a row, say I2:M2, this should work. (I put the combo box directly on my spreadsheet, not in a dialog)
Code: Select all   Expand viewCollapse view
  oDrawPages = ThisComponent.getDrawPages()
  oObj1 = oDrawPages.getByIndex(0)
  oObj2 = oObj1.getByIndex(0)
  oControl = oObj2.getControl()
  oSheet = ThisComponent.Sheets.getByIndex(0)
  oCellrange = oSheet.getCellRangeByName("I2:M2")
  DatArr = oCellrange.DataArray
  oControl.StringItemList = DatArr(0)
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Posts: 6455
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Access cellrange in stringitemlist

Postby Lupp » Tue May 15, 2018 7:20 pm

The indices of DataArray (as most indices on the technical level) are 0-based.

Another question to the Original Questioner: What is your understanding of a LIST?
That is not just the same as an array (1D or higher). 
You seem to want an array eventually, but another row in your post looks a if you try to treat a CellRange as a list.
The problem with this is not just that cell contents cannot be referenced as the cells themselves (like a default property), but also that neither the CellRange nor its DataArray are lists.

What TYPE of value do you actually want to assign to a property of your ComboBox?
(If this sounds silly: I never use ComboBoxes in Calc.)
On Windows 10: LibreOffice 5.4.4 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
Maybe we might! (Create a powerful UFO: United Free Office)
Lupp from München
User avatar
Posts: 1790
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Access cellrange in stringitemlist

Postby tmanikanda » Thu May 17, 2018 9:17 am

Hi All,

thanks all for your reply. Please find the sample sheet i am working on. The Item list will be a getting added. Also based on the indicator value i want them on the list.
(13.15 KiB) Downloaded 12 times
Apache OpenOffice 4.1.3 , Windows 10
Posts: 2
Joined: Tue May 15, 2018 9:36 am

Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 6 guests