使用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