EOF in excel

[naholt]

i have a scrpt that opens and xls file and reads a column.  when I use a csv file there was a line "if line == eof then break".  I was hoping there is something like that in excel.  when the script is at the end of the file I want it to exit by itself
thanks
Nicholas

[td]

Assuming that you are using COM Automation, you don't need an EOF.  You simply get the number of rows and a range that includes your column of interest.  The Tech Database has several examples.

[naholt]

i have excels that vary in length and if i understand you i will have to give the script the number of row and a range for each one? i sure hope that is not the case.  i will find the examples and see how they are structured.

thanks
Nicholas

[stanl]

for starters see

http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/tsleft.web+WinBatch/OLE~COM~ADO~CDO~ADSI~LDAP/OLE~with~Excel+Determine~First~and~Last~Row~and~Column~Used.txt

[DAG_P6]

There is also the implicit UsedRange Range object, which has Rows and Columns properties, like any other such object, that can be used to identify the last row and column.

You are in the Land of Excel; take advantage of its object model.

[stanl]

There is also the implicit UsedRange Range object, which has Rows and Columns properties, like any other such object, that can be used to identify the last row and column.

Which often is inaccurate, hence the many workarounds which make that point. for example

http://www.mrexcel.com/forum/excel-questions/693294-activesheet-usedrange-rows-count-returning-wrong-value.html

[DAG_P6]

You are correct about UsedRange, although I discovered, some years ago, that if you save, close, and reopen a document after adding or deleting rows or columns, UsedRange is accurate until you do so again.

In any case, when working with imported CSV files, Excel associates a range with the import, which can be obtained by enumerating the QueryTables collection. The ResultRange property of each QueryTable returns a Range object that contains the data, and the dimensions of that Range are very dependable. In particular, its Rows property can be used in lieu of the EOF property to stop a loop that iterates over the rows. Hence, in VBA, I would do something like the following.

Code Select Expand

For Each Row rowOfDetail in MyQueryTable.ResultRange
    ... do stuff
Next  ' Row rowOfDetail


[stanl]

As we are both correct, UsedRange can be a gotcha. Most Excel templates I populate are from SQL sources, and I present them as tables.  (1) activeworksheet.clear  (2) copyfromrecordset (3) then build table on UsedRange which at that point is correct.