EXCEL VBA之-QUERYTABLE对象获取外部数据

时　间:2012-07-27 09:08:27
作　者:叶海峰   ID：31  城市：广州
摘　要:EXCEL VBA之-QUERYTABLE对象获取外部数据
正　文:

'通过EXCEL的QUERYTABLE对象,速度较快

 
    Dim db     As ADODB.Connection
    Dim rst    As ADODB.Recordset
    Dim QryTbl As Excel.QueryTable
    Dim wksheet As Excel.Worksheet
    Set db = New ADODB.Connection
    Set rst = New ADODB.Recordset
    db.Provider = "Microsoft.jet.oledb.4.0"
    db.Open "c:\mas.mdb"
    Set rst = New ADODB.Recordset
    rst.Open "select * from 区域 where 区域='华东'", db, adOpenForwardOnly
    Set wksheet = ThisWorkbook.Worksheets.Add
    Set QryTbl = wksheet.QueryTables.Add(rst, wksheet.Range("A1"))
    QryTbl.Refresh
    rst.Close
    Set rst = Nothing
    db.Close
    Set db = Nothing
End Sub