VBA to Open MS Excel and get Tab names

[James C.]

Hoping someone can help...

I am trying to create some code in MS Access that will

1. Open up a specific MS Access workbook (portfolio.xlsx)
2. Loop through each Tab (currently there are 30 Tabs each with different
names)
3. Set the name of the Tab to a variable in which I can access later

Does anyone know how to do this? The endgame is to eventually copy the names
of the tabs into a MS Access Table along with some of the data on each tab.

Thanks for any help...

 

[Daniel Pineault]

The following procedure will open the specified Excel Workbook and return the
names of all the worksheets. You can easily adapt it as required.

Sub GetXLSShtNames(sXLSFile As String)
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
On Error GoTo Error_Handler

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False 'Control whether or not Excel should be visible to
'the user or not.
Set xlBook = xlApp.Workbooks.Open(sXLSFile) 'Open the workbook
For Each ws In xlBook.Worksheets
Debug.Print ws.Name
Next ws

xlBook.Close False 'Close the workbook without saving any changes
xlApp.Quit 'Close the instance of Excel we create

Error_Handler_Exit:
On Error Resume Next
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
Exit Sub

Error_Handler:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf &
"Error Number: " & _
Err.Number & vbCrLf & "Error Source: GetXLSShtNames" & vbCrLf & "Error
Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Sub
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.