运行的时候报错"外部表不是预期的格式",代码如下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:\工资汇总\1.xls;Extended Properties='Excel 8.0;HDR=Yes'"
adoConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=e:\工资汇总\1.xls;Extended Properties=Excel 8.0"
adoRecordset.Open "select * from [sheet1$]", adoConnection, adOpenKeyset, adLockOptimistic
MsgBox 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
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:\工资汇总\1.xls;Extended Properties='Excel 8.0;HDR=Yes'"
adoConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=e:\工资汇总\1.xls;Extended Properties=Excel 8.0"
adoRecordset.Open "select * from [sheet1$]", adoConnection, adOpenKeyset, adLockOptimistic
MsgBox 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
我开发的机器EXCEL是2003的
XSL文件可能是97的
sheet1$不是一个有效的名称,请确认不包括无效字符和标点
adoConnection.Open "Data Provider=MSDASQL.1;driver=Microsoft Excel Driver (*.xls);DBQ=e:\temp\book2.xls"