现要在Excel中实现如下功能
如现在Sheet1(它是一个第一行为栏位名的表)&sheet2,现在要在Sheet2上查找Sheet1中某查找符合条件的记录,并取中这条符合条件的记录中某个栏位的具体的值,到为Sheet2中的某一单元格中请问以上功能要怎么实现,谢谢
如现在Sheet1(它是一个第一行为栏位名的表)&sheet2,现在要在Sheet2上查找Sheet1中某查找符合条件的记录,并取中这条符合条件的记录中某个栏位的具体的值,到为Sheet2中的某一单元格中请问以上功能要怎么实现,谢谢
Sheet1它是一个第一行为栏位名的表现在要在Sheet1中查找符合条件的记录,并取这条符合条件的记录中某个栏位的具体的值,到Sheet2中的某一单元格中能否像用EXCEl自带的Sum函数一样,自己写个类似这样的函数GetFieldValue(表,要查找的字段,条件)请问以上功能要怎么实现,谢谢
Public Function GetFieldValue(SheetName As String, FieldName As String, strCondition As String) As String
On Error GoTo err1
Dim Conn As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim SQL As String
Set Conn = New ADODB.Connection
With Conn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Excel.ActiveWorkbook.Path & "\Book1.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
.Open
End With
'SELECT * FROM [sheet1$]"
SQL = "SELECT " & FieldName & " FROM [" & SheetName & "$] WHERE " & strCondition
Debug.Print SQL
Set Rs = Conn.Execute(SQL, , adCmdText)
If Not Rs Is Nothing Then
If Rs.State <> 0 Then
If Not Rs.EOF And Not Rs.BOF Then
GetFieldValue = Rs.Fields(0).Value & ""
End If
Rs.Close
End If
Set Rs = Nothing
End If
Conn.Close
Set Conn = Nothing
Exit Function
err1:
Debug.Print Err.Description
Err.Clear
GetFieldValue = ""
Set Rs = Nothing
Set Conn = Nothing
End Function