VB操作excel
Public Function GetFieldValue1(CurRange As Range, FieldName As String, strCondition As String) As String
On Error GoTo err1
Dim i As Long
Dim RowCount As Long
Dim ColCount As Long
Dim j As Long
Dim FieldCol As Long
RowCount = CurRange.Rows.Count
ColCount = CurRange.Columns.Count
For j = 1 To ColCount
If CurRange.Cells(1, j).Value = FieldName Then
Exit For
End If
Next
For i = 2 To RowCount
For j = 2 To ColCount
CurRange.Cells(i, j).Value
Next 'j
Next 'i
Exit Function
err1:
Debug.Print Err.Description
Err.Clear
GetFieldValue1 = ""
End Function
Public Function SumByMutiCondition(ExcelFileName As String, SheetName As String, FieldName As String, strCondition As String) As Double
On Error GoTo err1
Dim Conn As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim SQL As String
Set Conn = New ADODB.Connection
If (Trim$(ExcelFileName) = "") Then
If (Right(Excel.ActiveWorkbook.Path, 1) = "\") Then
ExcelFileName = Excel.ActiveWorkbook.Path & Excel.ActiveWorkbook.Name
Else
ExcelFileName = Excel.ActiveWorkbook.Path & "\" & Excel.ActiveWorkbook.Name
End If
' MsgBox ExcelFileName
End If
With Conn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ExcelFileName & ";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
' SumByMutiCondition = Rs.Fields(0).Value & ""
' End If
SumByMutiCondition = 0
Do While Not Rs.EOF And Not Rs.BOF
SumByMutiCondition = SumByMutiCondition + IIf(IsNull(Rs.Fields(0).Value), 0, Rs.Fields(0).Value)
Rs.MoveNext
Loop
Rs.Close
End If
Set Rs = Nothing
End If
Conn.Close
Set Conn = Nothing
Exit Function
err1:
Debug.Print Err.Description
Err.Clear
SumByMutiCondition = 0
Set Rs = Nothing
Set Conn = Nothing
End Function