本文介绍了使用ADO获取Excel的行号的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我们正在尝试根据soome条件获取特定记录集的行号。考虑下表

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

现在,如何获取员工 cde的行号或记录集号(根据下面的代码,我们需要得到的结果为2)。请对此提供任何帮助。

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.

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


推荐答案

最好的方法是在Excel文件中放置一列与行号相同的数据。然后在该列上查询。

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.

但是,如果由于某种原因您不能这样做,请尝试以下操作:

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

这篇关于使用ADO获取Excel的行号的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

07-17 19:04
查看更多