在vb中,如何通过代码获取一个excel文件中有多少个工作表(sheet),各个工作表的名字???

1ssp 2006-03-15 03:03:46

急急急!!!
解决了马上结帖!!!

...全文

1496 点赞收藏 8

写回复

8 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

1ssp 2006-03-16

谢谢of123!
也谢谢各位捧场!
给分了.

回复赞

of123 2006-03-16

Dim rstSchema As ADODB.Recordset

cnExcel.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & excel文件名及路径& ";Extended Properties=Excel 8.0"

Set rstSchema = cnExcel.OpenSchema(adSchemaTables)

Do Until rstSchema.EOF
Debug.Print "Table name: " & _
rstSchema!TABLE_NAME & vbCr & _
"Table type: " & rstSchema!TABLE_TYPE & vbCr
rstSchema.MoveNext
Loop
rstSchema.Close

回复赞

changechange 2006-03-16

为什么一定要调用 EXCEL.APPLICATION 呢？不调用不行吗？

回复赞

ZOU_SEAFARER 2006-03-15

For i = 1 To xlBook.Sheets.Count
Debug.Print xlBook.Sheets(i).Name '打印各个Sheet的名称
Next
可以在这个地方接入比如：

rsE.Open "select * from [" & xlBook.Sheets(i).Name & "$]", cnExcel, adOpenDynamic, adLockPessimistic

现在我要分多次打开每一个工作表,写入数据库.能不能用数据库的方法知道每个工作表的情况?
再次感谢!!
Do While Not rsE.EOF
If Not IsNumeric(rsE(2)) Then Exit Do
Cnn.Execute " insert into 客户资料(年份,月份,日期,姓名,省份,区域,手机,固话,传真,邮编,通讯地址,意向,网络,客户类型,备注) " _
& "values('" & rsE(1) & "'," & rsE(2) & ",'" & rsE(3) & "','" & rsE(4) & "','" & rsE(5) & "','" & rsE(6) _
& "','" & rsE(7) & "','" & rsE(8) & "','" & rsE(9) & "','" & rsE(10) & "','" & rsE(11) & "','" _
& rsE(12) & "','" & rsE(13) & "','" & rsE(14) & "','" & rsE(15) & "')"

回复赞

1ssp 2006-03-15

问题还没有解决,欢迎参与!!!

回复赞

1ssp 2006-03-15

谢谢!faysky2
我是在数据库要实现该功能,

'打开excel文件
cnExcel.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & excel文件名及路径& ";Extended Properties=Excel 8.0"
rsE.CursorLocation = adUseClient
'打开Excel的Sheet1表，准备导入数据
rsE.Open "select * from [芜湖市$]", cnExcel, adOpenDynamic, adLockPessimistic

现在我要分多次打开每一个工作表,写入数据库.能不能用数据库的方法知道每个工作表的情况?
再次感谢!!
Do While Not rsE.EOF
If Not IsNumeric(rsE(2)) Then Exit Do
Cnn.Execute " insert into 客户资料(年份,月份,日期,姓名,省份,区域,手机,固话,传真,邮编,通讯地址,意向,网络,客户类型,备注) " _
& "values('" & rsE(1) & "'," & rsE(2) & ",'" & rsE(3) & "','" & rsE(4) & "','" & rsE(5) & "','" & rsE(6) _
& "','" & rsE(7) & "','" & rsE(8) & "','" & rsE(9) & "','" & rsE(10) & "','" & rsE(11) & "','" _
& rsE(12) & "','" & rsE(13) & "','" & rsE(14) & "','" & rsE(15) & "')"

回复赞

faysky2 2006-03-15

Private Sub Command1_Click()
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")

Dim xlBook As Object
Set xlBook = xlApp.Workbooks.Open(App.Path & "\test.xls") '打开Excel文件

Debug.Print "Sheet的个数为：" & xlBook.Sheets.Count
For i = 1 To xlBook.Sheets.Count
Debug.Print xlBook.Sheets(i).Name '打印各个Sheet的名称
Next

xlBook.Close False
xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing
End Sub

回复赞

faysky2 2006-03-15

用Excel对象来做

回复赞