Excel利用用VBA获取指定工作簿中的工作表名称
时间:2023-07-01 01:51:33
如果需要用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)
猜你喜欢
图2 选择自定义序列选项 3、打开自定义序列对话框
Win8应用商店默认安装路径在哪如何修改全程图解
![](https://img.aspxhome.com/file/2023/29/a256421_0s.jpg)
Win10错误代码0*80070035错误怎么办?Win10错误代码0*80070035解决方法
![](https://img.aspxhome.com/file/2023/2/51742_0s.jpg)
怎么调电脑风扇转速?风扇转速控制的方法
![](https://img.aspxhome.com/file/2023/6/a326365_0s.jpg)
NVIDIA驱动与Windows不嫌容怎么解决?
![](https://img.aspxhome.com/file/2023/6/a322991_0s.jpg)
wps怎么绘制梯形图案?
![](https://img.aspxhome.com/file/2023/5/a166475_0s.png)
爱奇艺自动续费怎么取消?爱奇艺视频取消会员自动续费方法
![](https://img.aspxhome.com/file/2023/8/a339823_0s.jpg)
win10共享文件夹怎么设置?win10共享文件夹设置教程
![](https://img.aspxhome.com/file/2023/1/a271946_0s.png)
Win7系统报错0xc0000098怎么办?
![](https://img.aspxhome.com/file/2023/27/a232780_0s.jpg)
电脑出现多个“本地连接”的解决方法
![](https://img.aspxhome.com/file/2023/7/a326816_0s.jpg)
怎么在excel表中拆分日期
![](https://img.aspxhome.com/file/2023/0/a167620_0s.jpg)
Win7电脑设置快速启动栏的方法?
![](https://img.aspxhome.com/file/2023/4/a303917_0s.jpg)
Windows 8 中找回“便笺”小工具和程序的方法(图)
![](https://img.aspxhome.com/file/2023/29/a258820_0s.gif)
Win7系统玩刺客信条提示计算机msvcp110.dll丢失怎么解决
![](https://img.aspxhome.com/file/2023/3/a302266_0s.jpg)
win10系统怎么建立透明文件夹?
![](https://img.aspxhome.com/file/2023/28/a242682_0s.jpg)
word如何录制宏?word录制宏方法图解
![](https://img.aspxhome.com/file/2023/4/28964_0s.png)
wps怎么弄页眉页脚?
![](https://img.aspxhome.com/file/2023/7/a168627_0s.jpg)
wps表格出错原因分析
Word2016文档的背景颜色/图片怎么打印?
![](https://img.aspxhome.com/file/2023/0/25300_0s.jpg)
在Word2010文档中使用不同颜色突出显示文本功能的教程
![](https://img.aspxhome.com/file/2023/2/29982_0s.jpg)