'引用 ADO 和 ADOX
'引用 ADO: Microsoft ActiveX Data Objects 2.6 Library
'引用 ADOX: Microsoft ADO Ext. 2.6 for DDL ans Security
Dim adoConnection As New ADODB.Connection
'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=" & App.Path & "\book1.xls;Extended Properties='Excel 8.0;HDR=Yes'"
Dim adoCatalog As New ADOX.Catalog
Set adoCatalog.ActiveConnection = adoConnection
Dim i As Long
For i = 0 To adoCatalog.Tables.Count - 1
Debug.Print adoCatalog.Tables.Item(i).Name
Next iDim adoRecordset As New ADODB.Recordset
adoRecordset.Open "select * from [Sheet1$]", adoConnection, adOpenKeyset, adLockOptimistic
Dim temp As String
'Dim i As Long
For i = 0 To adoRecordset.Fields.Count - 1
temp = temp & adoRecordset.Fields.Item(i).Name & VBA.IIf(i < adoRecordset.Fields.Count - 1, ",", "")
Next i
If Not adoRecordset.EOF Then
temp = temp & vbCrLf & adoRecordset.GetString(, , ",", vbCrLf, "Null")
End If
Debug.Print temp'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)
'引用 ADO: Microsoft ActiveX Data Objects 2.6 Library
'引用 ADOX: Microsoft ADO Ext. 2.6 for DDL ans Security
Dim adoConnection As New ADODB.Connection
'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=" & App.Path & "\book1.xls;Extended Properties='Excel 8.0;HDR=Yes'"
Dim adoCatalog As New ADOX.Catalog
Set adoCatalog.ActiveConnection = adoConnection
Dim i As Long
For i = 0 To adoCatalog.Tables.Count - 1
Debug.Print adoCatalog.Tables.Item(i).Name
Next iDim adoRecordset As New ADODB.Recordset
adoRecordset.Open "select * from [Sheet1$]", adoConnection, adOpenKeyset, adLockOptimistic
Dim temp As String
'Dim i As Long
For i = 0 To adoRecordset.Fields.Count - 1
temp = temp & adoRecordset.Fields.Item(i).Name & VBA.IIf(i < adoRecordset.Fields.Count - 1, ",", "")
Next i
If Not adoRecordset.EOF Then
temp = temp & vbCrLf & adoRecordset.GetString(, , ",", vbCrLf, "Null")
End If
Debug.Print temp'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)
'引用 ADO: Microsoft ActiveX Data Objects 2.6 Library
'引用 ADOX: Microsoft ADO Ext. 2.6 for DDL ans Security
Dim adoConnection As New ADODB.Connection
'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=" & App.Path & "\book1.xls;Extended Properties='Excel 8.0;HDR=Yes'"
Dim adoCatalog As New ADOX.Catalog
Set adoCatalog.ActiveConnection = adoConnection
Dim i As Long
For i = 0 To adoCatalog.Tables.Count - 1
Debug.Print adoCatalog.Tables.Item(i).Name
Next iDim adoRecordset As New ADODB.Recordset
adoRecordset.Open "select * from [Sheet1$]", adoConnection, adOpenKeyset, adLockOptimistic
Dim temp As String
'Dim i As Long
For i = 0 To adoRecordset.Fields.Count - 1
temp = temp & adoRecordset.Fields.Item(i).Name & VBA.IIf(i < adoRecordset.Fields.Count - 1, ",", "")
Next i
If Not adoRecordset.EOF Then
temp = temp & vbCrLf & adoRecordset.GetString(, , ",", vbCrLf, "Null")
End If
Debug.Print temp'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)
adoRecordset.Open "select * from [Sheet1$]", adoConnection, adOpenKeyset, adLockOptimistic
时仍然有:
错误提示为“[Microsoft][ODBC Excel 驱动程序] 定义了过多字段。”
adoRecordset.Open "select * from [Sheet1$]", adoConnection, adOpenKeyset, adLockOptimistic
时仍然有:
错误提示为“[Microsoft][ODBC Excel 驱动程序] 定义了过多字段。” 哪位大虾帮帮忙呀