|
发表于 2014-5-25 22:12
|
显示全部楼层
本楼为最佳答案
本帖最后由 hwc2ycy 于 2014-5-25 22:14 编辑
- Sub demo()
- Const adUseClient = 3
- Const adModeRead = 1
- Dim AdoConn As Object, AdoRst As Object
- Dim StrConn$, strFullName$
- Dim i As Integer
- Dim strSql$, strSql2$, arr1, arr2
-
- On Error GoTo ErrorHandler
-
- strSql = "select 年份,时间段,城市,机型,"
- strSql2 = " from [地市+厂家+KPI$a2:o]"
- arr1 = Array("无线接通率", "无线掉线率", "切换成功率", "无线利用率", "吞吐量")
- arr2 = Array("无线接通率", "无线掉线率", "切换成功率", "无线利用率", "总吞吐量")
- strFullName = ThisWorkbook.FullName
-
- Set AdoConn = CreateObject("ADODB.Connection")
- Select Case Application.Version
- Case "14.0", "15.0", "12.0", "14.0"
- StrConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source='" & _
- strFullName & "';Extended Properties='Excel 12.0;HDR=YES;imex=1';"
- Case Else
- StrConn = "Provider= Microsoft.Jet.OLEDB.4.0;" & _
- "Data Source='" & strFullName & "';Extended Properties='Excel 8.0;HDR=YES;imex=1';"
- End Select
- With AdoConn
- .CursorLocation = adUseClient
- .Mode = adModeRead
- .ConnectionString = StrConn
- .Open
- End With
- For i = LBound(arr1) To UBound(arr1)
- Set AdoRst = AdoConn.Execute(strSql & arr1(i) & strSql2)
- With Worksheets(arr2(i))
- .Range("a2").CopyFromRecordset AdoRst
- With .ChartObjects(1).Chart
- .SeriesCollection(1).XValues = Worksheets(arr2(i)).Range("a2:d" & 2 + AdoRst.RecordCount)
- .SeriesCollection(1).Values = Worksheets(arr2(i)).Range("E2:E" & 2 + AdoRst.RecordCount)
- .Refresh
- End With
- End With
- Next
- Set AdoRst = Nothing
- AdoConn.Close
- Set AdoConn = Nothing
- MsgBox "完成"
- Exit Sub
- ErrorHandler:
- Dim strErr$
- strErr = "出现异常!" & vbCr
- strErr = strErr & "错误代码:" & Err.Number & vbCr
- strErr = strErr & "错误描述:" & Err.Description & vbCr
- strErr = strErr & "错误来源:" & Err.Source
- MsgBox strErr, vbCritical + vbOKOnly
- End Sub
复制代码 没有错,忘了刷新,你的数据表因为有使用的空行,所以一直是11行。
|
|