|
改了下,有些已经在这里不适合用了。- Sub 年度查询Access()
- Range("A6:AG65536").ClearContents
- With Sheets("年度报销统计查询")
- .Unprotect ("695360052")
- .[C55].FormulaR1C1 = ""
- .Protect ("695360052")
- End With
- Dim AccessFile As String, Database As String
- Dim arr(), i&, arrTemp
- Dim AdoConn As Object, AdoRst As Object
- Dim StrConn$, strSql$
- Dim strName$
- AccessFile = ThisWorkbook.Path & "\data.mdb"
- Database = "data"
- If Len([c3]) = 0 Then
- MsgBox "模糊查询开始"
- strSql = "select 报销月份,序号,定点医疗机构名称,医保卡号,单位名称,姓名,性别,年龄,入院日期,出院日期,住院天数,出院诊断,本次住院医疗费总额,甲类药费,乙类药费,进口药费,自费药费,超出范围,进口材料费,国产材料费,特殊检查费特殊治疗费,丙类项目,其它费用,起付段金额,个人政策自付小计,自费药品及自费项目,实际结算自付,统筹基金支付,大病求助基金支付,个人支付金额,本年住院次数,本年范围内费用累计,本年大病范围内费用累计 from " & Database '‘ & " where 姓名='" & strName & "'"
- Else
- strName = [c3]
- strSql = "select 报销月份,序号,定点医疗机构名称,医保卡号,单位名称,姓名,性别,年龄,入院日期,出院日期,住院天数,出院诊断,本次住院医疗费总额,甲类药费,乙类药费,进口药费,自费药费,超出范围,进口材料费,国产材料费,特殊检查费特殊治疗费,丙类项目,其它费用,起付段金额,个人政策自付小计,自费药品及自费项目,实际结算自付,统筹基金支付,大病求助基金支付,个人支付金额,本年住院次数,本年范围内费用累计,本年大病范围内费用累计 from " & Database & " where 姓名='" & strName & "'"
- End If
- On Error GoTo Errcheck
- If Dir(AccessFile) = "" Then
- MsgBox "ACCESS数据文件不存在"
- Exit Sub
- End If
- StrConn = "Provider= Microsoft.Jet.OLEDB.4.0;" & _
- "Data Source=" & AccessFile & ";"""
- Set AdoConn = CreateObject("ADODB.Connection")
- With AdoConn
- .CursorLocation = 3 '游标类型
- .CommandTimeout = 5 '超时
- .connectionTimeout = 5 '超时
- .Open StrConn '打开
- End With
- Set AdoRst = AdoConn.Execute(strSql)
- If AdoRst.RecordCount = 0 Then
- MsgBox "无合乎条件的数据"
- Exit Sub
- Else
- Application.ScreenUpdating = False
- Range("a6").CopyFromRecordset AdoRst
- End If
- AdoConn.Close
- Set AdoConn = Nothing
- Application.ScreenUpdating = True
- MsgBox "查询完成"
- Exit Sub
- Errcheck:
- MsgBox Err.Number & vbNewLine & _
- Err.Description
- End Sub
复制代码 |
评分
-
查看全部评分
|