How to get Excel sheet data into an Array in VB, w/o reading each Cell

[Som76]

How to get Excel sheet data into an Array in VB, w/o reading each Cell?

If I read each cell one by one, it takes half a minute to 5 or 6 minutes to load the file since my excel files have too much data.
Is there any other way I can read the excel file?

Thank you in advance for the kind help.

 

[TonyJollans]

Hi Som76,

What do you want to do with your data? It is already in an array ..

For x = 1 to yourrange.count
debug.print yourrange(x)
next

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[Som76]

Hello Tony,

I tried taking the way u said into a variant object, and I got the result.
I am using VSFlexGrid which has a method LoadArray, and if I pass this variant object, it display me the excel sheet data very fast. Previously i was scrolling through all the rows & cols of Xl Sheet which was taking a lot of time. Thanx for the suggestion.

I have one more small problem, if there is a time in the excel sheet eg 00:30, its not reading it properly. When i display the time, instead of 00:30 it shows me something like 2.083333333333E-02.
Please let me know why its not getting proper value. I am facing this only in case of time, other data are fine.

my code:
Dim vdata As Variant
vdata = xlSheet.UsedRange
vsfgFileData.LoadArray vdata

Thanx again Tony.

Regards
Somu

 

[xlbo]

Quicker to compare arrays with arrays tho

This is how to suck up an entire range in one go - this will get all data in colA:

Dim myArr as variant, lRow as long
lRow = Range("A65536&quot.end(xlup).row
myArr = range("A1:A" & lRow)

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[TonyJollans]

Hi Som76,

It's a question of formatting. Times are held as numbers so 2.083333333333E-02, when formatted as a time, is 40 minutes (00:40). I'm sorry but I don't know how to control the formatting in your flexgrid.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[Som76]

Thanks Tony for ur efforts.
I just did a trick here. I got time in any of the columns, for that column I read the text again which gave me proper data. The way we did returns
xlsheet.Cell(lRow,lCol).Value
so time was not returning proper.
for the time column i wrote a loop and read
xlsheet.Cell(lRow,lCol).Text
and i got the desired result.

Thanks again for the efforts to everyone.
Somu