|
发表于 2014-11-13 08:05
|
显示全部楼层
本楼为最佳答案
- Private Sub CommandButton1_Click()
- Const adUseClient = 3
- Const adModeRead = 1
- Dim AdoConn As Object, AdoRst As Object
- Dim strConn$, strSQL$, strFullname$, strCondition$
- Dim blnHasHeader As Boolean
- On Error GoTo ErrorHandler
- blnHasHeader = True
- strFullname = ThisWorkbook.FullName
- Set AdoConn = CreateObject("ADODB.Connection")
- strSQL = "select 定点医疗机构名称,分类,发生人次,总费用,统筹支付,IC卡支付,公务员补助,大额补助,扣减费用,实际应付 from [数据库$a1:o] "
- If Len(Range("b2").Value) Then
- strCondition = " 定点医疗机构名称='" & Range("b2").Value & "' and "
- End If
- If Len(Range("f2").Value) Then
- strCondition = strCondition & "报表时间=#" & Range("f2").Value & "# and "
- End If
- If Len(Range("j2").Value) Then
- strCondition = strCondition & "报表类别='" & Range("j2").Value & "' and "
- End If
- If Len(strCondition) Then
- strSQL = strSQL & "where " & Left(strCondition, Len(strCondition) - 5)
- End If
- Select Case Application.Version
- Case "14.0", "15.0", "12.0"
- strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source='" & _
- strFullname & "';Extended Properties='Excel 12.0;HDR=" & blnHasHeader & ";imex=1';"
- Case Else
- strConn = "Provider= Microsoft.Jet.OLEDB.4.0;" & _
- "Data Source='" & strFullname & "';Extended Properties='Excel 8.0;HDR=" & blnHasHeader & ";imex=1';"
- End Select
- 'Debug.Print strConn
- With AdoConn
- .CommandTimeout = 5
- .ConnectionTimeout = 5
- .CursorLocation = adUseClient
- .Mode = adModeRead
- .ConnectionString = strConn
- .Open
- End With
- 'MsgBox strSQL
- Application.ScreenUpdating = False
- Dim lRow&
- Set AdoRst = AdoConn.Execute(strSQL)
- lRow = Cells(Rows.Count, 2).End(xlUp).Row
- If lRow > 3 Then
- Range("a4:k" & lRow).Clear
- End If
- If AdoRst.RecordCount > 0 Then
- Range("b4").CopyFromRecordset AdoRst
- MsgBox "一共查询到了 " & AdoRst.RecordCount & " 条记录"
- Range("a4").Resize(AdoRst.RecordCount, 11).Borders.LineStyle = 1
- Dim arr
- arr = Application.Evaluate("=row(a1:a" & AdoRst.RecordCount & ")")
- With Range("a4").Resize(AdoRst.RecordCount)
- .Value = arr
- .NumberFormat = "000"
- End With
- Else
- MsgBox "没有符合条件的记录"
- End If
- Application.ScreenUpdating = True
- AdoConn.Close
- Exit Sub
- ErrorHandler:
- MsgBox Err.Number & vbCrLf & _
- Err.Description
- Application.ScreenUpdating = True
- Set AdoRst = Nothing
- Set AdoConn = Nothing
- End Sub
复制代码 |
评分
-
查看全部评分
|