开发者

EOF of an Excel spreadsheet in VB6

开发者 https://www.devze.com 2022-12-22 21:04 出处:网络
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

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!!

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号