vba 使用ADO获取excel的行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16086758/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get Row number of excel using ADO
提问by Prasad
We are trying to get the row number of particular recordset retreived based on soome condtion. consider below table
我们正在尝试根据某些条件获取检索到的特定记录集的行号。考虑下表
Name Id
abc 1
cde 2
efg 3
Now, how to get the row number or recordset number of employee "cde" (accrding to below code we need to get the result as 2). Any help on this please.
现在，如何获取员工“cde”的行号或记录集号（根据下面的代码，我们需要得到结果为 2）。请对此提供任何帮助。
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001
strSource="C:\Test.xls"
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strSource & ";Extended Properties=""Excel 8.0;HDR=Yes;"";"
Set conn = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
conn.Open strConnection
objRecordset.Open "Select Name FROM [sheet1$] Where Id = 2", conn, adOpenStatic, adLockOptimistic, adCmdText
msgbox objRecordset.GetString
conn.Close
Set conn = Nothing
回答by Pradeep Kumar
The best way is to put a column in the Excel file that has the same data as the row number. Then query on that column. That is a fool-proof solution.
最好的方法是在 Excel 文件中放置一个与行号具有相同数据的列。然后查询该列。这是一个万无一失的解决方案。
However, if for some reason you can't do that, then try this:
但是，如果由于某种原因你不能这样做，那么试试这个：
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1
Dim RowNumber
Dim SqlStmt
strSource = "C:\Temp\Test.xls"
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strSource & ";Extended Properties=""Excel 8.0;HDR=Yes;"";"
Set conn = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
conn.Open strConnection
SqlStmt = "Select Name, Id FROM [sheet1$]" ''-- you can optimize this query if your Id is in ascending order. e.g. "Select Name, Id FROM [sheet1$] Where Id <= 4"
objRecordset.Open SqlStmt, conn, adOpenStatic, adLockOptimistic, adCmdText
objRecordset.Find "Id = 4"
RowNumber = objRecordset!ID + 1 ''-- +1 for Header row.
conn.Close
Set conn = Nothing
MsgBox RowNumber
