Copy Excel range into system.array
Copy Excel range into system.array
Copy Excel range into system.array
Copy Excel range into system.array
Copy Excel range into system.array
Copy Excel range into system.array Copy Excel range into system.array Copy Excel range into system.array Copy Excel range into system.array Copy Excel range into system.array Copy Excel range into system.array Copy Excel range into system.array Copy Excel range into system.array
Copy Excel range into system.array Copy Excel range into system.array
Copy Excel range into system.array
Go Back  Xtreme Visual Basic Talk > > > Copy Excel range into system.array


Reply
 
Thread Tools Display Modes
  #1  
Old 05-23-2008, 02:34 AM
rajeevbhatt17 rajeevbhatt17 is offline
Freshman
 
Join Date: May 2008
Posts: 38
Default Copy Excel range into system.array


What would be the most efficient way of copying a range of cells into an array, in VB.NET. These may be contiguous or non-contiguous.

Something like resize or currentregion

Thanks,
Rajeev
Reply With Quote
  #2  
Old 05-23-2008, 08:59 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hi Rajeev,

Welcome to the forum.

It's pretty easy to retrieve an array of values from a Range. The Range.Value property will return a two dimentional array of values if the first area of the range has more than one cell. Using VB.NET, it could look something like this:

Code:
Dim myRange as Excel.Range myRange = xlApp.Range("A1:C3") Dim myArray As Object(,) '<-- declared as 2D Array myArray = myRange.Value

Be aware, however, that an array returned from the Excel.Range.Value property will be a base-1 array, not a base-0 arrays one would otherwise expect when using .NET.

So you should iterate your values as follows on a base-1 basis, such as in the following:
Code:
For r As Integer = 1 To myArray.GetUpperBound(0) For c As Integer = 1 To myArray.GetUpperBound(1) Dim myValue As Object = myArray(r, c) ' ' Now you can work with each 'myValue' as needed. ' Next c Next r

Even better would be to not assume the 1-based lower-bound:
Code:
For r As Integer = myArray.GetLowerBound(0) To myArray.GetUpperBound(0) For c As Integer = myArray.GetLowerBound(1) To myArray.GetUpperBound(1) Dim myValue As Object = myArray(r, c) ' ' Now work with each 'myValue' as needed.... ' Next c Next r

However, you said that the cells in your range could be "contiguous or non-contiguous." If the cells are non-contiguous, then you have have a multi-area range, in which case you could iterate through the areas of the range as follows:

Code:
For Each area As Excel.Range In myRange.Areas Dim myArray As Object(,) '<-- declared as 2D Array myArray = area.Value ' ' Now work with each 'myArray' as needed... ' Next area

Is this what you were looking for?
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb

Last edited by webbone; 05-24-2008 at 11:05 AM. Reason: fixed typo per MikeR's request
Reply With Quote
  #3  
Old 05-24-2008, 09:51 AM
rajeevbhatt17 rajeevbhatt17 is offline
Freshman
 
Join Date: May 2008
Posts: 38
Default

Thanks, Mike.... I had resolved the problem in exactly the same way, but was wondering if there was another (better) way of getting around the base 1 issue.


I have recently seen this site, and am amazed at how good it is. How do you manage to give such detailed replies to so many....Great stuff.

Good Luck and look forward to interacting with you.

Rajeev
Reply With Quote
  #4  
Old 05-24-2008, 02:39 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hey Rajeev,

Thanks so much for the positive feedback. The volume is not too much, so I can put the time in to try to explain clearly... and the questions that are asked are pretty high quality, so this is a motivator.

As to your remaining issue, there is no way to directly re-base an array in .NET. Therefore, in order to "change" the base from base 1 to base 0, you could create a new array (which will be base 0 by default) and then copy the contents from the original array to the new array. You could wrap this approach into a method or even into an extension method to make these calls convenient for you.

If you only need read-only access to the array elements, then you could use a For Each loop and not worry about the base.

Lastly, you can write to (that is, transform) the array elements using the Array.ConvertAll() generic method, by passing in a System.Converter delegate.

This is about all I can think of. Of course, you can always simply iterate from the .GetLowerBound() to the .GetUpperBound() and not have to worry about the base at all. It all depends on what works for you...

Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb

Last edited by Mike Rosenblum; 05-24-2008 at 03:54 PM.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Copy Excel range into system.array
Copy Excel range into system.array
Copy Excel range into system.array Copy Excel range into system.array
Copy Excel range into system.array
Copy Excel range into system.array
Copy Excel range into system.array Copy Excel range into system.array Copy Excel range into system.array Copy Excel range into system.array Copy Excel range into system.array Copy Excel range into system.array Copy Excel range into system.array
Copy Excel range into system.array
Copy Excel range into system.array
 
Copy Excel range into system.array
Copy Excel range into system.array
 
-->