如何利用VBA获取指定工作簿中的工作表名称
时间:2022-01-27 06:11:53
如果需要用VBA的方法获得某个工作簿中工作表的名称,可以使用下面的代码,其中使用了 Microsoft ActiveX Data Objects (ADO),可以将指定工作簿中的工作簿名称显示在A列中,在使用时需将代码中的工作簿名称及路径进行更改。
Sub GetSheetNames()
Dim objConn As Object
Dim objCat As Object
Dim tbl As Object
Dim iRow As Long
Dim sWorkbook As String
Dim sConnString As String
Dim sTableName As String
Dim cLength As Integer
Dim iTestPos As Integer
Dim iStartpos As Integer
‘在此输入工作簿名称及路径.
sWorkbook = "G:\Excel文件\book2.xls"
sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sWorkbook & ";" & _
"Extended Properties=Excel 8.0;"
Set objConn = CreateObject("ADODB.Connection")
objConn.Open sConnString
Set objCat = CreateObject("ADOX.Catalog")
Set objCat.ActiveConnection = objConn
iRow = 1
For Each tbl In objCat.Tables
sTableName = tbl.Name
cLength = Len(sTableName)
iTestPos = 0
iStartpos = 1
If Left(sTableName, 1) = "’" And Right(sTableName, 1) = "’" Then
iTestPos = 1
iStartpos = 2
End If
If Mid$(sTableName, cLength – iTestPos, 1) = "$" Then
Cells(iRow, 1) = Mid$(sTableName, iStartpos, cLength – _
(iStartpos + iTestPos))
iRow = iRow + 1
End If
Next tbl
objConn.Close
Set objCat = Nothing
Set objConn = Nothing
End Sub
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
excel2016官方版表格数字怎么进行求和?
![](https://img.aspxhome.com/file/2023/3/36263_0s.jpg)
excel怎样快速输入当前日期和时间
Word怎么显示隐藏的标题3?
![](https://img.aspxhome.com/file/2023/1/28931_0s.jpg)
Excel中sumproduct函数统计工资总和的教程
![](https://img.aspxhome.com/file/2023/2/a141682_0s.jpg)
Pdf怎么添加文本? Pdf文件添加内容的教程
![](https://img.aspxhome.com/file/2023/1/15861_0s.jpg)
excel2003序号自动生成的方法
在Word2010文档中打印隐藏文字和文档属性的图文教程
![](https://img.aspxhome.com/file/2023/0/29990_0s.jpg)
Execl表格COUNTIFS函数如何运用
word如何设置每行字符数
![](https://img.aspxhome.com/file/2023/5/35955_0s.png)
Win10如何使任务栏完全透明?Win10使任务栏完全透明的方法
![](https://img.aspxhome.com/file/2023/1/51721_0s.png)
WPS表格的入门教程之新建文档
![](https://img.aspxhome.com/file/2023/9/a164059_0s.jpg)
如何在excel2016中插入折线图
![](https://img.aspxhome.com/file/2023/1/a142961_0s.jpg)
word中常见的MathType公式问题
![](https://img.aspxhome.com/file/2023/6/32326_0s.png)
word怎么删除页眉页脚
![](https://img.aspxhome.com/file/2023/4/33674_0s.jpg)
Win10 22h2系统重启转圈无法启动解决方法分享
![](https://img.aspxhome.com/file/2023/1/46381_0s.png)
如果在Excel2016中打开的文件没有响应怎么办
![](https://img.aspxhome.com/file/2023/1/a142951_0s.jpg)
excel怎样另存为记事本?
Word文档应用怎么写期末评语总结
用excel打印标签的方法步骤
Word如何一次性删除回车符?
![](https://img.aspxhome.com/file/2023/4/24194_0s.jpg)