How do I write the code in vb6 in finding the EOF of an Excel file?
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 works, but the only problem is only the column 1 will be checked and the others are not. Can anyone help me on how to improve this code to check all rows and column of Excel cells?
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!!
精彩评论