VB中如何访问Excel文件中的工作表？
在程序文件夹下有一个电子表格t.xls,其中有一张工作表czryda.
在VB中引用ADO , 代码如下:
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Persist Security Info=false;" & _
"Data Source=" & App.path & "\" & "t.xls;" & _
"Extended Properties='Excel 8.0;HDR=Yes'"
cn.Open
rs.Open "Select * From czryda", cn, adOpenKeyset, adLockOptimistic
在运行时,总是说czryda对象找不到.
在这个论坛上搜索"工作表"，找到
http://community.csdn.net/Expert/topic/3646/3646880.xml?temp=.7520105
按上面介绍的方法：
“
工作表名并不等同于表名.
打开工作簿,选中工作表中的数据区域,然后执行"插入"-->"名称"-->"定义",
添加一个名称,此名称即为SQL语句中的表名.
”
给目标数据区域添加了一个名称：WorkTable1
然后把相应代码改成：
rs.Open "Select * From WorkTable1", cn, adOpenKeyset, adLockOptimistic
在运行时,仍然说WorkTable1对象找不到!
请高手指点.
问题点数：0、回复次数：4Top
1 楼liuxiaoyi666（MSMVP 小猪妹荣誉马甲之八卦兔子）回复于 2005-06-01 18:49:08 得分 0
http://www.connectionstrings.com/
参考里面的写法Top
2 楼wljin（衣冠清瘦）回复于 2005-06-01 21:17:50 得分 0
建议看看MSDN,定义xlApp.xlSheet对象，可以实现Top
3 楼leayh（云卷云舒）回复于 2005-06-02 09:22:06 得分 0
Dim xlsApp As New Excel.Application
Dim xlsWorkbook As New Excel.Workbook
Dim xlsWorksheet As New Excel.Worksheet
'建立Excel Application对象
Set xlsApp = GetObject("", "Excel.Application")
'打开xls文件
Set xlsWorkbook = xlsApp.Workbooks.Open("文件名")
'获取某一Sheet
Set xlsWorksheet = xlsWorkbook.Sheets(1)
'取得其中单元格值
Debug.Print xlsWorksheet.Cells(Row, Col).Value
Top
4 楼zuoxingyu（激情成就梦想）回复于 2005-06-02 10:16:34 得分 0
操作EXCEL数据库的代码
Dim cnn As New ADODB.Connection
Private Sub Form_Load()
Dim str As String
str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\123.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
'"HDR=Yes;" indicates that the first row contains columnnames, not data
'"IMEX=1;" tells the driver to always read "intermixed" data columns as text
'TIP! SQL syntax: "SELECT * FROM [sheet1$]" - i.e. worksheet name followed by a "$" and wrapped in "[" "]" brackets.
cnn.Open str
Dim rs As New ADODB.Recordset
rs.Open "SELECT * FROM [sheet1$]", cnn, adOpenStatic, adLockReadOnly
While Not rs.EOF
Combo1.AddItem rs.Fields(0).Value & rs.Fields(1).Value
rs.MoveNext
Wend
Set DataGrid1.DataSource = rs
End SubTop
