excel file => array

does anyone know how to save the entire contents of an excel file into an array withou reading the file line by line
regards
jcd

Why don't you want to read it line by line? I think that's the easiest way....Unless you're doing this in VBA, then there might be a better way to do it...

"I do not agree with a word you say, but I will defend to the death your right to say it" - Voltaire

line by line is slow...the HD is constantly spinning and as you know mechanical is relatively slow...if I could put it in an array all at once I think it would speed things up...maybe it wouldnt???...any suggestions would be appreciated.
regards
jcd

I think where the confusion here is, that you are saying an "entire file". Excel spreadsheets can be enormous. Do you have a particular "range" of data that you are trying to pull? If so, then there is a very quick way to do this.

D

loop each worksheet in the workbook and assign the Sheet(x).UsedRange.Value (or .Formula) property to a variant. You will get an array of the cells contents.
However I assume you will still have to output the array contents somehow, and if you need to retrieve paticuar data items, you will be faced withthe same problem (in reverse).

You may want to consider returning a recordset object using SQL as discussed here (http://www.visualbasicforum.com/bbs/showthreaded.php?Cat=&Board=vba&Number=50743&Search=true&Forum=CatSearch-1&Words=SQL%20Excel&Match=And&Searchpage=0&Limit=25&Old=1month&Main=48863).

"He who dares my son!.. He who dares.." - Del-Boy Trotter

Timbo I didnt even think that could be done...amazing...I only new that you either had to fire up an excel application or you had to use file input and go line by line...I am going to test it out thanks alot...
regards
jcd

thats the kind of thing I need, but how would you get the seperate values out ie if you had

usedrange.value=(1 to 8, 1 to 1)

how could you get those 4 values into seperate variables to set up a nested for loop like
<pre><font color=blue>For</font color=blue> X = intColStart <font color=blue>To</font color=blue> intColEnd
<font color=blue>For</font color=blue> Y = intRowStart <font color=blue>To</font color=blue> intRowEnd
strReplace = xlSheet.Worksheets(1).Range(Chr(Y + 64) & X).Value
txtHTML.Text = Replace(txtHTML.Text, "ID=MHDDEExcel><", ID="MHDDEExcel>" & strReplace & "<", , 1, vbTextCompare)
<font color=blue>Next</font color=blue> Y
<font color=blue>Next</font color=blue> X
</pre>
I tried to find the values in my watch window but it crashes my system when I try to access it

try using your Locals window (View menu),
and use the '.Cells(X,Y).Value' method instead..



"He who dares my son!.. He who dares.." - Del-Boy Trotter

the locals window is still crashing out my system when I try to expand "cells", anyway I got the values I wanted by putting
<pre>var = xlBook.Worksheets(1).UsedRange.Value
intRowStart = <font color=blue>LBound</font color=blue>(var,1)
intColStart = <font color=blue>LBound</font color=blue>(var,2)
intRowEnd = <font color=blue>UBound</font color=blue>(var,1)
intColEnd = <font color=blue>UBound</font color=blue>(var,2)

</pre>



cheers for the tip