Create an array from EXCEL worksheet

[BillEdd]

I am trying to populate an array from data in an opened EXCEL file and can't get it to work. I need to populate a 2 dimensional array from Range("G7:H700"), for example. I did not include any code here since I have tried many ways and all have raised errors. Would someone please offer a solution? I prefer not to have to loop thru the cells one by one. I would like to pass the entire range into the array in one pass. Thanks

This is how I am creating an instance of EXCEL in VB.NET

Dim xl As Object
Dim wrkbk As Object
Dim wrksht As Object

xl = CreateObject("Excel.Application")
wrkbk = xl.Workbooks.Open(Filename)
wrksht = wrkbk.Sheets(1)

 

[jmcilhinney]

Generally speaking, you traverse a 2D array or similar data structure using nested For loops. You need to have created the array first, based on the size of your range, then traverse it using the loops. Inside the inner loop, you have a single line to read data from the appropriate cell to the appropriate element. Show us your best attempt at doing that and we can take it from there.

Also, it's generally preferable to avoid using late binding if possible. Are you trying to support multiple versions of Excel? If not, you ought to reference the specific library version you want and then use early binding, i.e. turn Option Strict On and use specific types rather than Object.

 

[JohnH]

A 2D array is returned when you use the Value property of that Range. Range.Value property (Excel)