Access cellrange in stringitemlist
-
- Posts: 21
- Joined: Tue May 15, 2018 9:36 am
Access cellrange in stringitemlist
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
oMyCombo.StringItemList=(arg)
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
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
oMyCombo.StringItemList=(arg)
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
Re: Access cellrange in stringitemlist
Please upload your ODF type samle file with the embedded macro code here
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
Re: Access cellrange in stringitemlist
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
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)
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Access cellrange in stringitemlist
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.)
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 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
-
- Posts: 21
- Joined: Tue May 15, 2018 9:36 am
Re: Access cellrange in stringitemlist
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.
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.
- Attachments
-
- Item_list.ods
- (13.15 KiB) Downloaded 299 times
Apache OpenOffice 4.1.3 , Windows 10
-
- Posts: 21
- Joined: Tue May 15, 2018 9:36 am
Re: Access cellrange in stringitemlist
Hi All
I am using for loop and additem to add items into the list box, but it is becoming slow when the row count increase, also i am still finding a way to filter the entries
For vi=0 To 100
vitem = oCellRange.DataArray(vi)(0)
If vitem <> "" Then
olistbox.AddItem(oCellRange.DataArray(vi)(0),vi)
End If
Next vi
I am not able to use match and index functions. Getting error
oItemRng = oSheet_Prod.getCellRangeByName("B2:B1000")
arg1 = Array(oProdRng)
oItemIdRng = oSheet_Prod.getCellRangeByName("A2:A1000")
arg2 = Array(oProdIdRng)
vtmp1 = svc.callFunction("MATCH",vproduct_name,arg1,0)
vproduct_id = svc.callFunction("INDEX",arg2,vtmp1)
Can someone help me in this
I am using for loop and additem to add items into the list box, but it is becoming slow when the row count increase, also i am still finding a way to filter the entries
For vi=0 To 100
vitem = oCellRange.DataArray(vi)(0)
If vitem <> "" Then
olistbox.AddItem(oCellRange.DataArray(vi)(0),vi)
End If
Next vi
I am not able to use match and index functions. Getting error
oItemRng = oSheet_Prod.getCellRangeByName("B2:B1000")
arg1 = Array(oProdRng)
oItemIdRng = oSheet_Prod.getCellRangeByName("A2:A1000")
arg2 = Array(oProdIdRng)
vtmp1 = svc.callFunction("MATCH",vproduct_name,arg1,0)
vproduct_id = svc.callFunction("INDEX",arg2,vtmp1)
Can someone help me in this
Apache OpenOffice 4.1.3 , Windows 10
Re: Access cellrange in stringitemlist
How related the colorized variables to this task? Where you assign a value to the variable oProdRng? Please upload the full code of the Sub (or Function) with all of the dependencies. Better to upload a full sample file (again)
Code: Select all
[color=#FF0000]oItemRng[/color] = oSheet_Prod.getCellRangeByName("B2:B1000")
arg1 = Array([color=#FF0000]oProdRng[/color])
oItemIdRng = oSheet_Prod.getCellRangeByName("A2:A1000")
arg2 = Array(oProdIdRng)
vtmp1 = svc.callFunction("MATCH",vproduct_name,arg1,0)
vproduct_id = svc.callFunction("INDEX",arg2,vtmp1)
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
-
- Posts: 21
- Joined: Tue May 15, 2018 9:36 am
Re: Access cellrange in stringitemlist
Hi Zizi64
oItemRng and oProdRng are same its my mistake while copying the code. I have attached a sample file in my previous post on May 17th. Also the for loop i have used is becoming slower when the count is more(ex. 1000)
oItemRng and oProdRng are same its my mistake while copying the code. I have attached a sample file in my previous post on May 17th. Also the for loop i have used is becoming slower when the count is more(ex. 1000)
Apache OpenOffice 4.1.3 , Windows 10
-
- Posts: 145
- Joined: Mon Jun 13, 2016 10:50 am
Re: Access cellrange in stringitemlist
Hi,
You can call the TRANSPOSE() function to get the item array. Something like this:
Regards
You can call the TRANSPOSE() function to get the item array. Something like this:
Code: Select all
oMyCombo= oDialog1.getControl("ComboBox1")
oCellRange = oSheet_tst.getCellRangeByName("A2:C8")
s = createUnoService("com.sun.star.sheet.FunctionAccess")
data = s.callFunction("TRANSPOSE", array(oCellRange))
oDialog1.getControl("ComboBox1").addItems(data(0), 0)
oMyCombo.DropDownLineCount = ubound(data(0)) +1
- Attachments
-
- tmanikanda.ods
- (14.92 KiB) Downloaded 248 times
AOOo 4.1.2 on Win7 | LibreOffice on various Linux systems
-
- Posts: 21
- Joined: Tue May 15, 2018 9:36 am
Re: Access cellrange in stringitemlist
Hi All
Thanks for your replies. I tried lambert suggestion. Its working for getting the whole column into the listbox, But i need to get the items only which is not yet active, can i do this? Also i am in need of new requirement. Want to select more than one item and update them, For ex. select both item 3 and 4 from list box and make them active. Need help on this
Thanks for your replies. I tried lambert suggestion. Its working for getting the whole column into the listbox, But i need to get the items only which is not yet active, can i do this? Also i am in need of new requirement. Want to select more than one item and update them, For ex. select both item 3 and 4 from list box and make them active. Need help on this
- Attachments
-
- tmanikanda.ods
- (14.47 KiB) Downloaded 220 times
Apache OpenOffice 4.1.3 , Windows 10
Re: Access cellrange in stringitemlist
@hubert lambert:
1.works taking .DataArray as the "default property" of a com.sun.star.sheet.CellRange object. I hadn't expected that - and I would still prefer to writeThe name dataArray replacing data there because CellRange objects also have an array property .Data which (for historical reasons?) only contains numeric data NAN for strings and blank cells.
2.
Since you seem to be familiar with the array-properties of CellRanges and with their specific way of using indices, a question OT in this thread: Do you know a way to assign such a special array to a properly dimensioned ordinary array with one statement (without any loops)?
The reverse case is Cellrange.SetDataArray() with works with 2D-arrays dimensioned in Basic.
To correctly get the current price for a selected item, and to push it to the respective cell, you can generate an array giving an indirection to the original indices depending on the listbox indices for the selected item.
Sorry. Just found you wanted to allow selection of INactive items only. Simply adapt the few respective lines and addwhere already the new price is pushed.
=== Edit ===
Demo replaced.
See crude demo attached.
1.
Code: Select all
data = s.callFunction("TRANSPOSE", array(oCellRange))
Code: Select all
dataArray = s.callFunction("TRANSPOSE", array(oCellRange.DataArray))
2.
Since you seem to be familiar with the array-properties of CellRanges and with their specific way of using indices, a question OT in this thread: Do you know a way to assign such a special array to a properly dimensioned ordinary array with one statement (without any loops)?
The reverse case is Cellrange.SetDataArray() with works with 2D-arrays dimensioned in Basic.
Having the one-column-arrays for item, isActive, and price you can scan isActive for active entries and create a compacted version of 'items'.tmanikanda wrote:But i need to get the items only which is not yet active, can i do this?
To correctly get the current price for a selected item, and to push it to the respective cell, you can generate an array giving an indirection to the original indices depending on the listbox indices for the selected item.
Sorry. Just found you wanted to allow selection of INactive items only. Simply adapt the few respective lines and add
Code: Select all
oCellRange.getCellByPosition(1, indirection(itempos)).String = "Y"
=== Edit ===
Demo replaced.
See crude demo attached.
- Attachments
-
- tmanikandaReworkedInactive.ods
- (15.34 KiB) Downloaded 229 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
-
- Posts: 21
- Joined: Tue May 15, 2018 9:36 am
Re: Access cellrange in stringitemlist
Hi Lupp
The code you gave worked. Thanks. Just want to understand that your adding the items which are inactive to a array and using that array in additems function. Am i right.
One more thing i am looking for is to select multiple items in the listbox. getselecteditem is working where i am selecting only one item from the listbox, but getselecteditems is not working where i am selecting multiple items. Any idea on this
The code you gave worked. Thanks. Just want to understand that your adding the items which are inactive to a array and using that array in additems function. Am i right.
One more thing i am looking for is to select multiple items in the listbox. getselecteditem is working where i am selecting only one item from the listbox, but getselecteditems is not working where i am selecting multiple items. Any idea on this
Apache OpenOffice 4.1.3 , Windows 10
Re: Access cellrange in stringitemlist
viewtopic.php?f=20&t=94115getselecteditem is working where i am selecting only one item from the listbox, but getselecteditems is not working where i am selecting multiple items. Any idea on this
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
-
- Posts: 21
- Joined: Tue May 15, 2018 9:36 am
Re: Access cellrange in stringitemlist
Hi Zizi64
I am getting error 'Property or Method not found: Selectedvalues' in your sheet Listbox.ods when selecting any values in listbox_2
I am getting error 'Property or Method not found: Selectedvalues' in your sheet Listbox.ods when selecting any values in listbox_2
Apache OpenOffice 4.1.3 , Windows 10
Re: Access cellrange in stringitemlist
It works fine in my LibreOffice 4.4.7 version, but - I just tested it now - I am getting same error message in my Apache OpenOffice winPenPack portable version.I am getting error 'Property or Method not found: Selectedvalues' in your sheet Listbox.ods when selecting any values in listbox_2
Apache OpenOffice 4.1.3 , Windows 10
Usually I use LibreOffice.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
-
- Posts: 21
- Joined: Tue May 15, 2018 9:36 am
Re: Access cellrange in stringitemlist
i searched few links and still couldn't find how to select multiple items from listbox in openoffice. any help
Apache OpenOffice 4.1.3 , Windows 10