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

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