EOF of excel in vb6

Question

how do i write the code in vb6 in finding the EOF of excel file

can anyone help me?

i try to code this and it works..

--->

Dim excelApp as Excel.Application

Dim excelWB as Excel.Workbook

Set excelApp = New Excel.Application

Set excelWB = excelApp.Workbooks.Open("D:\Book1.xls")

Dim xlsRow as Long

Dim EOF as Boolean

xlsRow = 1

Do While (EOF = False)

  If (excelWB.Sheets("Sheet1").Cells(xlsRow, 1).Value = "") Then

    EOF = True

  Else

    xlsRow = xlsRow + 1

  End If

Loop

<---

this code is working, but the only problem is only the column 1 will be checked and the others is not. Can anyone help me on how to improve this code to check all rows and column of excel cells

Answer 1

I loaded your example into VB6 but I don't think I had my references right as I wasn't getting full intellisense.

However, I would recommend against using the Excel application objects as they tend to be slow for just accessing Excel files.

I would recommend using ADODB.

E.g make a connection to your spreadsheet (sFilename)

Set cnExcel = New ADODB.Connection

cnExcel.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
   "Data Source=" & sFilename & ";" & _
   "Extended Properties=""Excel 8.0;IMEX=1;HDR=NO"""

Then read through it. ADODB does the EOF work for you. Here I display rows 0 and 1

rs.Open "select * from " & sSheetName, cnExcel, adOpenDynamic, adLockOptimistic

While Not rs.EOF
    debug.print rs(0) 
    debug.print rs(1) 
    rs.MoveNext
Wend

This is just a quick taste but there are resources on the net about doing it this way and its is WAY faster that using excel objects! The above is cut and paste from my own code so I know it works!!