如何利用VBA获取指定工作簿中的工作表名称

来源:互联网 时间:2016-01-03

如果需要用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

相关阅读:
Top