Excel tabs

[thegameoflife]

Is there a way to capture all the names of the tabs on a given spreadsheet from access?

 

[LearningSql]

Hello GameOfLife --

Here's some short code that will Create a New Excel Application, Open an Excel Workbook and then Enumerate the TabNames using a Do Loop. You will need to pass the Complete path and filename. Once you get the tabname you can do with it what you need (msgbox, save to table, etc).

Copy and paste between the ==== signs into a new Module.

====
'Usage
'Call ExcelTabNames("c:\temp\ExampleFileWithTabsNamed.xls&quot

Sub ExcelTabNames(CompleteFileLocationAndName As String)
Dim objXL, objActiveWkb As Object
Dim WorksheetCount, WorksheetNumber As Integer
Dim WorksheetName As String

'Create New Excel Object and Open Workbook
Set objXL = CreateObject("Excel.Application&quot
objXL.Application.Workbooks.Open (CompleteFileLocationAndName) 'Uses value passed into Sub
Set objActiveWkb = objXL.Application.ActiveWorkbook

'Set Variables for Do Loop, Start at 1 and End at WorksheetCount
WorksheetNumber = 1
WorksheetCount = objActiveWkb.Worksheets.Count

'Do Loop using variables above
'Loop will put Name of Worksheet into a msgbox
Do While WorksheetNumber <= WorksheetCount
WorksheetName = objActiveWkb.Worksheets(WorksheetNumber).Name
MsgBox WorksheetName
WorksheetNumber = WorksheetNumber + 1
Loop

'Close and Quit Excel
objActiveWkb.Close savechanges:=False
objXL.Application.Quit

'Clear objects
Set objActiveWkb = Nothing: Set objXL = Nothing

End Sub
====

Good Luck.
Pru