先查询看看你有什么 表,字段吧!
1.
Dim cn as ADODB.Connection
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:\MyFolder\MyWorkbook.xls;" & _
"Extended Properties=Excel 8.0;"
.Open
End With
Excel 版本：对于 Excel 95 工作簿（Excel 版本 7.0），应指定 Excel 5.0；对于 Excel 97、Excel 2000 或 Excel 2002 (XP) 工作簿（Excel 版本 8.0、9.0 和 10.0），应指定 Excel 8.0 版本。
2.
Dim cn as ADODB.Connection
Set cn = New ADODB.Connection
With cn
.Provider = "MSDASQL"
.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
"DBQ=C:\MyFolder\MyWorkbook.xls; ReadOnly=False;"
.Open
End With
3.
Dim cn as ADODB.Connection
Set cn = New ADODB.Connection
With cn
.Provider = "MSDASQL"
.ConnectionString = "DSN=MyExcelDSN;"
.Open
End With
使用以下代码可以检索工作簿中可用表的列表：
Set rs = cn.OpenSchema(adSchemaTables)
Jet 提供程序返回的记录集包含九 (9) 个字段，但其中只有四 (4) 个字段有数据：
- table_name
- table_type（“表”或“系统表”）
- date_created
- date_modified
Dim cn As ADODB.Connection
Dim rsT As ADODB.Recordset
Dim intTblCnt As Integer, intTblFlds As Integer
Dim strTbl As String
Dim rsC As ADODB.Recordset
Dim intColCnt As Integer, intColFlds As Integer
Dim strCol As String
Dim t As Integer, c As Integer, f As Integer
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & App.Path & _
"\ExcelSrc.xls;Extended Properties=Excel 8.0;"
'.Provider = "MSDASQL"
'.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
"DBQ=" & App.Path & "\ExcelSrc.xls; "
.CursorLocation = adUseClient
.Open
End With
Set rsT = cn.OpenSchema(adSchemaTables)
intTblCnt = rsT.RecordCount
intTblFlds = rsT.Fields.Count
List1.AddItem "Tables: " & intTblCnt
List1.AddItem "--------------------"
For t = 1 To intTblCnt
strTbl = rsT.Fields("TABLE_NAME").Value
List1.AddItem vbTab & "Table #" & t & ": " & strTbl
List1.AddItem vbTab & "--------------------"
For f = 0 To intTblFlds - 1
List1.AddItem vbTab & rsT.Fields(f).Name & _
vbTab & rsT.Fields(f).Value
Next
List1.AddItem "--------------------"
Set rsC = cn.OpenSchema(adSchemaColumns, Array(Empty, Empty, strTbl, Empty))
intColCnt = rsC.RecordCount
intColFlds = rsC.Fields.Count
For c = 1 To intColCnt
strCol = rsC.Fields("COLUMN_NAME").Value
List1.AddItem vbTab & vbTab & "Column #" & c & ": " & strCol
List1.AddItem vbTab & vbTab & "--------------------"
For f = 0 To intColFlds - 1
List1.AddItem vbTab & vbTab & rsC.Fields(f).Name & _
vbTab & rsC.Fields(f).Value
Next
List1.AddItem vbTab & vbTab & "--------------------"
rsC.MoveNext
Next
rsC.Close
List1.AddItem "--------------------"
rsT.MoveNext
Next
rsT.Close
cn.Close
