用ado访问Excel文件

李寻欢

Dim adoConnection As New ADODB.Connection
    Dim adoRecordset As New ADODB.Recordset
    'OLE DB + ODBC Driver 方式:
    'adoConnection.Open "Data Provider=MSDASQL.1;driver=Microsoft Excel Driver (*.xls);DBQ=e:\temp\book2.xls"
    'Microsoft.Jet.OLEDB.4.0 方式,(建议)
    adoConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=e:\temp\book2.xls;Extended Properties='Excel 8.0;HDR=Yes'"
     
    adoRecordset.Open "select * from [sheet1$]", adoConnection, adOpenKeyset, adLockOptimistic
    Debug.Print adoRecordset.RecordCount
    Dim i As Integer
    Do Until adoRecordset.EOF
     For i = 0 To adoRecordset.Fields.Count - 1
     Debug.Print adoRecordset.Fields.Item(0).Name
     Debug.Print adoRecordset.Fields.Item(0).Value
     Next i
     adoRecordset.MoveNext
    Loop
     
    '注: OLE DB + ODBC Driver 方式不支持以下语句,但 Microsoft.Jet.OLEDB.4.0 方式支持!
    adoConnection.Execute "insert into [sheet1$](F1) values(3)"
    adoRecordset.AddNew Array("f1", "f2", "f3", "f4"), Array(1, 2, 3, 4)

764300778

eeee

kindlin

不错不错