Getting an array from Excel

[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(x lSheet.Cells(50 , 10), _
xlSheet.Cells(5 0, 19)).Value
'Do something with the array here
'Send the array back to different cells in the spreadsheet
xlSheet.Range(x lSheet.Cells(30 , 30), _
xlSheet.Cells(3 0, 39)).Value = x

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

[dermot]

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

-----Original Message-----
Hi All,
My problem is trying to figure out how to get the numbersfrom a range of cells in Excel into an array (double) in aVB.NET program, do something with the array, and then sendit back to the spreadsheet. I AM able to open an exitingExcel 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 froma 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 verylarge 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(x lSheet.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(x lSheet.Cells(30 , 30), _
xlSheet.Cells( 30, 39)).Value = x

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

.

[Lee]

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

-----Original Message-----
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

-----Original Message-----
Hi All,
My problem is trying to figure out how to get thenumbers

from a range of cells in Excel into an array (double) ina

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

notan 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(x lSheet.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(x lSheet.Cells(30 , 30), _
xlSheet.Cells (30, 39)).Value = x

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

.

.

[Armin Zingler]

"Lee" <le********@lum ileds.com> schrieb

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.

I'd use

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

and examine o after the assignment to find out the type(s) of the returned
object(s).
--
Armin

http://www.plig.net/nnq/nquote.html
http://www.netmeister.org/news/learn2quote.html

[onedaywhen]

This worked for me:

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

--

"Lee" <le********@lum ileds.com> wrote in message news:<05******* *************** ******@phx.gbl> ...

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

-----Original Message-----
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

-----Original Message-----
Hi All,
My problem is trying to figure out how to get the numbersfrom a range of cells in Excel into an array (double) in
aVB.NET program, do something with the array, and then sendit back to the spreadsheet. I AM able to open an exitingExcel spreadsheet and do this with a single cell but notan 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 froma 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 verylarge 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(x lSheet.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(x lSheet.Cells(30 , 30), _
xlSheet.Cells (30, 39)).Value = x

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

.

.

[Lee]

BINGO!! That did it! Thanks a million for all the help.
-Lee

-----Original Message-----
This worked for me:

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

--

"Lee" <le********@lum ileds.com> wrote in message

news:<05******* *************** ******@phx.gbl> ...

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

>-----Original Message-----
>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
>
>
>
>>-----Original Message-----
>>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(x lSheet.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(x lSheet.Cells(30 , 30), _
>>xlSheet.Cells (30, 39)).Value = x
>>
>>I *really* appreciate any help you can give me.
>>Thanks,
>>Lee
>>
>>.
>>
>.
>

.