使用ADO的方法操作EXCEL檔案

資料庫連接

    Dim adoConn As New ADODB.Connection
    Dim adoReco As New ADODB.Recordset
    Set adoConn = New ADODB.Connection

    'OLE DB + ODBC Driver 方式:
    'adoConn .Open "driver=Microsoft Excel Driver受能力(*.xls);DBQ=C:\1.xls"

    'Microsoft.Jet.OLEDB.4.0 方式,(建議)
    adoConn .Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data   Source=C:\1.xls;Extended Properties='Excel 8.0;HDR=Yes'"

    資料庫建立

    '建立任意名及工作表名的EXCEL檔案
    adoConn .Execute "Create table [C:\1.xls].Sheet1" & "(" & Sql & ")" 如Sql= "Num int, Name char(20)"
   
    資料集訪問

    Set adoReco = New ADODB.recordset
    adoReco .Open "Select * from [sheet1$]", connExcel, adOpenDynamic, adLockOptimistic

    資料插入,更新,刪除

    '必須以Microsoft.Jet.OLEDB.4.0 方式串連資料庫才支援INSERT,UPDATA!
    adoConn .Execute "insert into [sheet1$] (....) values (....)"
    adoConn .Execute "UPDATE [sheet1$] Set ....=... Where ..."
    adoConn .Execute "delete * from [sheet1$] where ...."

    資料庫關閉
    adoReco .Close
    Set adoReco = Nothing
   
    adoConn .Close
    Set adoConn = Nothing