populating two dimensional array with excel data

Hi,
I'm looking for an efficient way to populate a two dimensional array. The
first cell of each row contains the First Name and the second cell has the
Last Name. I'm ok up to the part where I actually need to read the data in

The code I have for reading the data into the array works but it is just
awful:

'loop reading each spreadsheet element into the array
n = 0 'counter for array element position
intRow = 1
intCol = 1


Do Until objExcel.Cells(intRow,intCol).Value = ""

Fullname(n,0) = objExcel.Cells(intRow, intCol).Value
intRow = intRow + 1
n = n + 1

Loop

intRow = 1
intCol = 2
n = 0

Do Until objExcel.Cells(intRow,intCol).Value = ""

Fullname(n,1) = objExcel.Cells(intRow, intCol).Value
intRow = intRow + 1
n = n + 1

Loop

There has to be a better way. Can someone show me?

Thanks in Advance

OldPedant
Fri Jul 18 14:19:01 CDT 2008

For row = 1 to 999999
If objExcel.Cells(row, 1).Value = "" Then Exit For
Fullname(row-1,0) = objExcel.Cells( row, 1).Value
Fullname(row-1,1) = objExcel.Cells( row, 2).Value
Next

I should point out, though, that VBS typically works better with the columnd
and rows reversed from what you are using. The reason for that is that you
can do
ReDim Preserve arrayName( existingNumberOfColumns, newNumberOfRows )
to easily add rows to an array. With ReDim Preserve, only the *LAST*
dimension can be changed in the ReDim.

If this is strictly for your own use and you KNOW you won't need to expand
that FullName array, it won't matter, but consider it for the future.

Tim
Fri Jul 18 22:35:16 CDT 2008

If there are only 2 columns of data on the sheet and it's a contiguous
block:

Dim arrData
arrData = objExcel.Cells(1,1).CurrentRegion.Value

arrData will be a 2-D array.

Tim.


"Mecha77" <Mecha77@discussions.microsoft.com> wrote in message
news:1A97F690-57AC-42D8-911B-BE5C79ADF61F@microsoft.com...
> Hi,
> I'm looking for an efficient way to populate a two dimensional array. The
> first cell of each row contains the First Name and the second cell has the
> Last Name. I'm ok up to the part where I actually need to read the data in
>
> The code I have for reading the data into the array works but it is just
> awful:
>
> 'loop reading each spreadsheet element into the array
> n = 0 'counter for array element position
> intRow = 1
> intCol = 1
>
>
> Do Until objExcel.Cells(intRow,intCol).Value = ""
>
> Fullname(n,0) = objExcel.Cells(intRow, intCol).Value
> intRow = intRow + 1
> n = n + 1
>
> Loop
>
> intRow = 1
> intCol = 2
> n = 0
>
> Do Until objExcel.Cells(intRow,intCol).Value = ""
>
> Fullname(n,1) = objExcel.Cells(intRow, intCol).Value
> intRow = intRow + 1
> n = n + 1
>
> Loop
>
> There has to be a better way. Can someone show me?
>
> Thanks in Advance
>

Mecha77
Thu Jul 24 09:02:00 CDT 2008

Thank you to Old Pedant and Tim for your help

"Tim Williams" wrote:

> If there are only 2 columns of data on the sheet and it's a contiguous
> block:
>
> Dim arrData
> arrData = objExcel.Cells(1,1).CurrentRegion.Value
>
> arrData will be a 2-D array.
>
> Tim.
>
>
> "Mecha77" <Mecha77@discussions.microsoft.com> wrote in message
> news:1A97F690-57AC-42D8-911B-BE5C79ADF61F@microsoft.com...
> > Hi,
> > I'm looking for an efficient way to populate a two dimensional array. The
> > first cell of each row contains the First Name and the second cell has the
> > Last Name. I'm ok up to the part where I actually need to read the data in
> >
> > The code I have for reading the data into the array works but it is just
> > awful:
> >
> > 'loop reading each spreadsheet element into the array
> > n = 0 'counter for array element position
> > intRow = 1
> > intCol = 1
> >
> >
> > Do Until objExcel.Cells(intRow,intCol).Value = ""
> >
> > Fullname(n,0) = objExcel.Cells(intRow, intCol).Value
> > intRow = intRow + 1
> > n = n + 1
> >
> > Loop
> >
> > intRow = 1
> > intCol = 2
> > n = 0
> >
> > Do Until objExcel.Cells(intRow,intCol).Value = ""
> >
> > Fullname(n,1) = objExcel.Cells(intRow, intCol).Value
> > intRow = intRow + 1
> > n = n + 1
> >
> > Loop
> >
> > There has to be a better way. Can someone show me?
> >
> > Thanks in Advance
> >
>
>
>