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