Getting an array from Excel

  • Thread starter Thread starter Lee
  • Start date Start date
L

Lee

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 the spreadsheet. I AM able to open an exiting
Excel spreadsheet and do this with a single cell but not
an array. In addition, I can send an array from the
VB.NET program to a range of cells in Excel but I can't
for the life of me figure out how to get the numbers from
a range of cells in Excel into an array in the VB.NET
program. I'm sure it would be easy to use a loop and
bring in one cell at a time but my range of cells is very
large and it would be very time consuming to do it that
way. I'd like to learn how to work with arrays.

Here is a small example of the code I tried that seems
like it should work but it doesn't:

Dim x(9) as Double
'Read in the values of a range of cells
x = xlSheet.Range(xlSheet.Cells(50, 10), _
xlSheet.Cells(50, 19)).Value
'Do something with the array here
'Send the array back to different cells in the spreadsheet
xlSheet.Range(xlSheet.Cells(30, 30), _
xlSheet.Cells(30, 39)).Value = x

I *really* appreciate any help you can give me.
Thanks,
Lee
 
Hi Lee

I think your problem is that in Excel, only variants can
read data from sheets like this, ie
Dim x(9) as Variant

Of course, there is no variant type in VB.Net, but maybe
if you try
Dim x(9) as Object
it will work...

dermot
 
Hi dermot,
Okay, so the data type is the problem here. I tried your
suggestion about dimensioning my array and an Object but
that didn't work either.
-Lee
 
Lee said:
Hi dermot,
Okay, so the data type is the problem here. I tried your
suggestion about dimensioning my array and an Object but
that didn't work either.
Click to expand...

I'd use

dim o as object
o = xlSheet.Range(xlSheet.Cells(50, 10), xlSheet.Cells(50, 19)).Value

and examine o after the assignment to find out the type(s) of the returned
object(s).
 
This worked for me:

Dim x As System.Array
'Read in the values of a range of cells
x = CType(xlSheet.Range(xlSheet.Cells(50, 10), _
xlSheet.Cells(50, 19)).Value, System.Array)
'Do something with the array here
'Send the array back to different cells in the spreadsheet
xlSheet.Range(xlSheet.Cells(30, 30), _
xlSheet.Cells(30, 39)).Value = x

--
 
Back
Top