Create an array from EXCEL worksheet

BillEdd

New member
Joined
Dec 12, 2019
Messages
1
Programming Experience
5-10
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

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,397
Location
Sydney, Australia
Programming Experience
10+
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

VB.NET Forum Moderator
Staff member
Joined
Dec 17, 2005
Messages
15,517
Location
Norway
Programming Experience
10+
Top Bottom