473,378 Members | 1,543 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,378 software developers and data experts.

Excel range to Array to Listbox

Sub ExcelToListBox()
Dim xRange As Object
Dim ary
Dim xValue As String

xRange = oXL.Range("A1:A9") 'has letters A-H
ary = xRange.value
xValue = ary(3, 1) 'xValue = C
Me.ListBoxPerson.Items.AddRange(ary)

This AddRange causes error message:
'An unhandled exception of type
'System.Reflection.AmbiguousMatchException' occurred in
microsoft.visualbasic.dll
'Additional information: No accessible overloaded
'ObjectCollection.AddRange' can be called without a narrowing
conversion.

End Sub


Questions:
Is there a difference between these two:
Dim ary
Dim ary as System.Array
What is the syntax for the AddRange? I've tried various combinations.

Thanks,
George
Nov 20 '05 #1
3 8864
No replies yet, so I'll explain this newbie question another way:

How do you transfer data from an "Excel range" array to a listbox?

I've used this code with "normal" arrays:
dim Ary1() as String
[slow transfer of Excel data in "For-Next" code into Ary1]
xValue = Ary1(2) 'xValue = C
me.listbox1.AddRange(Ary1) 'this works

To transfer data (faster) from an Excel range:
dim Ary2 'not normal way to declare an array
dim xRange as Object
xRange = oXL.Range("A1:A9") 'has letters A-H
Ary2 = xRange.value 'this works
xValue = Ary2(3, 1) 'xValue = C
'not normal way to access array values
me.listbox1.AddRange(Ary2) 'error message (see my first post)

It's obvious that Ary2 is a Different kind of object than Ary1.
Have you seen any explanations for this?

Can this kind of array (from Excel range) be used in Listbox.AddRange?
If not, any suggestions to do it other ways?

Thanks for your help on either question,
George
te*****@hotmail.com (George) wrote in message news:<c1**************************@posting.google. com>...
Sub ExcelToListBox()
Dim xRange As Object
Dim ary
Dim xValue As String

xRange = oXL.Range("A1:A9") 'has letters A-H
ary = xRange.value
xValue = ary(3, 1) 'xValue = C
Me.ListBoxPerson.Items.AddRange(ary)

This AddRange causes error message:
'An unhandled exception of type
'System.Reflection.AmbiguousMatchException' occurred in
microsoft.visualbasic.dll
'Additional information: No accessible overloaded
'ObjectCollection.AddRange' can be called without a narrowing
conversion.

End Sub


Questions:
Is there a difference between these two:
Dim ary
Dim ary as System.Array
What is the syntax for the AddRange? I've tried various combinations.

Thanks,
George

Nov 20 '05 #2
This seems to be the problem:
When Ary2 = xRange.value, the rank (number of dimensions) of Ary2 is
set to 2.
But "listbox1.AddRange" evidently allows only single-dimensional
arrays.?

If that is true, is there a way to copy a specific dimension of a
multi-dimensional array to a single-dimensional array?
I tried Array.Copy without success.

I'm not getting very much help here; is this a dumb question or what?
Would ArrayList or something else work better?

Thanks,
George
te*****@hotmail.com (George) wrote in message news:<c1**************************@posting.google. com>...
No replies yet, so I'll explain this newbie question another way:

How do you transfer data from an "Excel range" array to a listbox?

I've used this code with "normal" arrays:
dim Ary1() as String
[slow transfer of Excel data in "For-Next" code into Ary1]
xValue = Ary1(2) 'xValue = C
me.listbox1.AddRange(Ary1) 'this works

To transfer data (faster) from an Excel range:
dim Ary2 'not normal way to declare an array
dim xRange as Object
xRange = oXL.Range("A1:A9") 'has letters A-H
Ary2 = xRange.value 'this works
xValue = Ary2(3, 1) 'xValue = C
'not normal way to access array values
me.listbox1.AddRange(Ary2) 'error message (see my first post)

It's obvious that Ary2 is a Different kind of object than Ary1.
Have you seen any explanations for this?

Can this kind of array (from Excel range) be used in Listbox.AddRange?
If not, any suggestions to do it other ways?

Thanks for your help on either question,
George
te*****@hotmail.com (George) wrote in message news:<c1**************************@posting.google. com>...
Sub ExcelToListBox()
Dim xRange As Object
Dim ary
Dim xValue As String

xRange = oXL.Range("A1:A9") 'has letters A-H
ary = xRange.value
xValue = ary(3, 1) 'xValue = C
Me.ListBoxPerson.Items.AddRange(ary)

This AddRange causes error message:
'An unhandled exception of type
'System.Reflection.AmbiguousMatchException' occurred in
microsoft.visualbasic.dll
'Additional information: No accessible overloaded
'ObjectCollection.AddRange' can be called without a narrowing
conversion.

End Sub
>


Questions:
Is there a difference between these two:
Dim ary
Dim ary as System.Array
What is the syntax for the AddRange? I've tried various combinations.

Thanks,
George

Nov 20 '05 #3
My "final answer", in moving data from Excel range to Listbox items:

Solution 1, needs one array, OK for small range.
1a. Excel to array

Dim Ary1() as string
'one-dimensional array acceptable to Listbox.Items.AddRange
For...Next, transfer values from Excel to Ary1

1b. Array to Listbox
Listbox.Items.AddRange(Ary1)
------------------------------------------

Solution 2, needs two arrays, better for large ranges.
2a. Excel to array

Dim Ary2 as System.Array
Dim xRange as Object
xRange = oXL.Range("A1:A930")
Ary2 = xRange.value 'Ary2 now has rank of 2, base 1

2b. Transfer data from two-dimensional to one-dimensional array.
Could not find a command to do this.
Redim cannot change the array's rank.
The For...Next here goes much faster than the one in step 1a.

Dim Ary3(UBound(Ary2, 1) - 1) as String
'Ary2 uses base 1, Ary3 uses base 0.
'Need to decrement by one for normal array with base zero
Dim n2 as Integer
Dim n3 as Integer
For n2 = 1 to UBound(Ary2,1)
'read items from Ary2 starting at 1
n3 = n2 -1
Ary3(n3) = Ary2(n2, 1)
'transfer to Ary3 starting at zero
Next n2

2c. Array to Listbox
Listbox.Items.AddRange(Ary3)
---------------

I could not find these steps explained in Microsoft help or in other posts here.

George

te*****@hotmail.com (George) wrote in message news:<c1**************************@posting.google. com>...
This seems to be the problem:
When Ary2 = xRange.value, the rank (number of dimensions) of Ary2 is
set to 2.
But "listbox1.AddRange" evidently allows only single-dimensional
arrays.?

If that is true, is there a way to copy a specific dimension of a
multi-dimensional array to a single-dimensional array?
I tried Array.Copy without success.

I'm not getting very much help here; is this a dumb question or what?
Would ArrayList or something else work better?

Thanks,
George
te*****@hotmail.com (George) wrote in message news:<c1**************************@posting.google. com>...
No replies yet, so I'll explain this newbie question another way:

How do you transfer data from an "Excel range" array to a listbox?

I've used this code with "normal" arrays:
dim Ary1() as String
[slow transfer of Excel data in "For-Next" code into Ary1]
xValue = Ary1(2) 'xValue = C
me.listbox1.AddRange(Ary1) 'this works

To transfer data (faster) from an Excel range:
dim Ary2 'not normal way to declare an array
dim xRange as Object
xRange = oXL.Range("A1:A9") 'has letters A-H
Ary2 = xRange.value 'this works
xValue = Ary2(3, 1) 'xValue = C
'not normal way to access array values
me.listbox1.AddRange(Ary2) 'error message (see my first post)

It's obvious that Ary2 is a Different kind of object than Ary1.
Have you seen any explanations for this?

Can this kind of array (from Excel range) be used in Listbox.AddRange?
If not, any suggestions to do it other ways?

Thanks for your help on either question,
George
te*****@hotmail.com (George) wrote in message news:<c1**************************@posting.google. com>...
Sub ExcelToListBox()
Dim xRange As Object
Dim ary
Dim xValue As String

xRange = oXL.Range("A1:A9") 'has letters A-H
ary = xRange.value
xValue = ary(3, 1) 'xValue = C
Me.ListBoxPerson.Items.AddRange(ary)

This AddRange causes error message:
'An unhandled exception of type
'System.Reflection.AmbiguousMatchException' occurred in
microsoft.visualbasic.dll
'Additional information: No accessible overloaded
'ObjectCollection.AddRange' can be called without a narrowing
conversion.

End Sub
>>

Questions:
Is there a difference between these two:
Dim ary
Dim ary as System.Array
What is the syntax for the AddRange? I've tried various combinations.

Thanks,
George

Nov 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: mytfein | last post by:
Hi Everyone, Background: Another department intends to ftp a .txt file from the mainframe, for me to process. The objective is to write a vb script that would be scheduled to run daily to...
1
by: Greg | last post by:
I tried following the code below from the microsoft article Q306022 to copy an array of data to excel in a single call. I only have a single dimension in my array. The problem I have is that...
5
by: Lee | last post by:
Hi All, My problem is trying to figure out how to get the numbers from a range of cells in Excel into an array (double) in a VB.NET program, do something with the array, and then send it back to...
2
by: George | last post by:
Is there a fast way to transfer an Excel range to an array? Example: Excel range is E2:E300 Dim person() as string Thanks, George
2
by: Doug Glancy | last post by:
Dim temp(Me.TimeIncrementListBox.Items.Count) As String Dim i As Int32 .... For i = 0 To UBound(temp) - 1 temp(i) = CType(Me.TimeIncrementListBox.Items(i), String) Next...
18
by: Frank M. Walter | last post by:
Hello, I have made an small AddIn with udf for excel 2003. I use vs2003. The point of view is the function __T() I call it in excel sheet writing =__T() I am not able to set a value to a...
2
by: dan NY | last post by:
I'm a struggling new VB Applications user that has what may be a simple question, but I've been struggling with it. I'm using a response to a message box YesNo question to cause one of two...
3
by: toffee | last post by:
Hi all, I got a pre-formatted spreadsheet. would it be possible using js to copy the data from a table on the current webpage, open the spreadsheet and paste the content ? if so, anyone got any...
2
by: gellis72 | last post by:
I'm working on a program that imports a bunch of data from a folder full of Excel files and compiles it into an Access DB. The Excel files have a varying number of rows and columns that need to be...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.