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...

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.



"James C." wrote:

> 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...