If Trim(.TextMatrix(.rows - 1, 3)) <> "" Then If IsNumeric(.TextMatrix(.rows - 1, 3)) Then dblSumMoney = dblSumMoney + CDbl(.TextMatrix(.rows - 1, 3)) End If End If
Screen.MousePointer = vbDefault Exit Sub '错误处理 Err_Proc: MsgBox "操作失败,错误原因为:" & Err.Description, vbExclamation, "提示" If ar_Tmp.State = adStateOpen Then ar_Tmp.Close Pbr.Visible = False Screen.MousePointer = vbDefault Exit Sub End Sub
不用打开excel,引用microsoft activex data objects 2.8 library就行了
Set Cnn = New ADODB.Connection Cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\abc.xls";Extended properties=""Excel 8.0;HDR=Yes;IMEX=1""" Set rs = New ADODB.Recordset rs.Open "select * from [" & sheetname & "$]", Cnn, adOpenKeyset, adLockOptimistic msgbox rs.fields(0)
Dim ac_Excel As New ADODB.Connection
Dim Tmp_TblName As String 'EXCEL工作表名称
Dim dblSumMoney As Double '金额合计
On Error GoTo Err_Proc
If Trim(txtPath.Text) = "" Then
MsgBox "请先选择EXCEL文件!", 48, "提示"
Exit Sub
End If
Screen.MousePointer = vbHourglass
Call InitHFlexgrid
'建立与EXCEL文件的连接
With ac_Excel
.CursorLocation = adUseClient
If .State = adStateOpen Then .Close
'.ConnectionString = "Data Provider=MSDASQL.1;driver=Microsoft Excel Driver (*.xls);DBQ=" & txtPath.Text
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & txtPath.Text _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
.Open
.CommandTimeout = 300
End With
If Trim(txtcExcelTableName.Text) = "" Then
MsgBox "请输入EXCEL表名!", 48, "提示"
Call SelAllTxt(Me, "txtcEXCELTableName")
Screen.MousePointer = vbDefault
Exit Sub
Else
Tmp_TblName = Trim(txtcExcelTableName.Text)
End If
'打开sheet$表
StrSql = " select * from [" & Tmp_TblName & "$]"
If ar_Tmp.State = adStateOpen Then ar_Tmp.Close
ar_Tmp.Open StrSql, ac_Excel, adOpenKeyset, adLockReadOnly
'MsgBox ar_Tmp.RecordCount
If ar_Tmp.RecordCount = 0 Then
MsgBox "选中的EXCEL文件没有数据!", 48, "提示"
Screen.MousePointer = vbDefault
ar_Tmp.Close
Exit Sub
Else
Pbr.Visible = True
Pbr.Min = 1
If ar_Tmp.RecordCount > 100 Then
Pbr.Max = ar_Tmp.RecordCount
Pbr.Value = 1
Pbar_i1 = 1
pbar_i2 = 1
Else
Pbr.Max = 100
Pbr.Value = 1
Pbar_i1 = 1
pbar_i2 = 100 / ar_Tmp.RecordCount
End If
dblSumMoney = 0
For i_Row = 1 To ar_Tmp.RecordCount
Pbar_i1 = Pbar_i1 + pbar_i2
If Pbar_i1 > Pbr.Max Then Pbar_i1 = Pbr.Max
Pbr.Value = Pbar_i1
With HFlexgrid
.rows = .rows + 1
.rowheight(.rows - 1) = 370
.TextMatrix(.rows - 1, 0) = ""
.TextMatrix(.rows - 1, 1) = Trim(ar_Tmp(0)) & "" '部门
.TextMatrix(.rows - 1, 2) = Trim(ar_Tmp(1)) & "" '产品
.TextMatrix(.rows - 1, 3) = Trim(ar_Tmp(2)) & "" '领用资金小计
If Trim(.TextMatrix(.rows - 1, 3)) <> "" Then
If IsNumeric(.TextMatrix(.rows - 1, 3)) Then
dblSumMoney = dblSumMoney + CDbl(.TextMatrix(.rows - 1, 3))
End If
End If
.TextMatrix(.rows - 1, 4) = Trim(ar_Tmp(3)) & "" '单据张数
.TextMatrix(.rows - 1, 15) = "" '操作结果
End With
ar_Tmp.MoveNext
Next i_Row
If HFlexgrid.rows > 1 Then HFlexgrid.FixedRows = 1
End If
txtfSumMoney.Text = Format(dblSumMoney, "standard")
lbliRowCounts.Caption = "记录数:" & HFlexgrid.rows - 1
ar_Tmp.Close
Pbr.Visible = False
Screen.MousePointer = vbDefault
Exit Sub
'错误处理
Err_Proc:
MsgBox "操作失败,错误原因为:" & Err.Description, vbExclamation, "提示"
If ar_Tmp.State = adStateOpen Then ar_Tmp.Close
Pbr.Visible = False
Screen.MousePointer = vbDefault
Exit Sub
End Sub
Set Cnn = New ADODB.Connection
Cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\abc.xls";Extended properties=""Excel 8.0;HDR=Yes;IMEX=1"""
Set rs = New ADODB.Recordset
rs.Open "select * from [" & sheetname & "$]", Cnn, adOpenKeyset, adLockOptimistic
msgbox rs.fields(0)