get sheet names

[J. Freed]

I want to set up a VBA routine that will open up a specific Excel sheet, get
the tab names, store them in a table then close. The tab names and number of
tabs can vary so I need to be able to retrieve as many as there are. I will
then use this to import the data in each of the tabs. Any ideas? TIA....

 

[fredg]

I want to set up a VBA routine that will open up a specific Excel sheet, get
the tab names, store them in a table then close. The tab names and number of
tabs can vary so I need to be able to retrieve as many as there are. I will
then use this to import the data in each of the tabs. Any ideas? TIA....

Create a new Table.
TableName "tblWorksheetNames"
Field Name "SheetName" Text datatype, Indexed Yes/No Duplicates

Create a new Module.

Click on tools + references
Set a reference to the Microsoft Excel 10.0 Object Library.

Then copy and paste the below code into the module

Public Sub GetWorksheetNames()
On Error GoTo Err_Handler
Dim xlx As Object, xlw As Object
Set xlx = CreateObject("Excel.Application")

Set xlw = xlx.workbooks.Open("C:\MyFolderName\SpreadsheetName.xls", ,
True)

Dim S As Excel.Worksheet

For Each S In xlw.Worksheets
CurrentDb.Execute "Insert Into tblWorksheetnames(SheetName)
Values('" & S.Name & "');", dbFailOnError
Next S

Exit_Sub:
xlw.Close False
Set xlw = Nothing
xlx.Quit
Set xlx = Nothing
Exit Sub
Err_Handler:
If Err = 3022 Then
Resume Next
Else
MsgBox "Error: " & Err.Number & " " & Err.Description
Resume Exit_Sub
End If
End Sub

Run the code. The sheet names will be added to the table. No name will
be duplicated.

 

[J. Freed]

Thanks. Came up with a slightly different way to do it (where tab_name is the
destination table for the tab names):

Function refresh()
Dim aa As Object, ab As Object
DoCmd.SetWarnings 0
DoCmd.OpenQuery "delete_tab_name"
Set aa = CreateObject("excel.application")
Set tabs = CurrentDb.OpenRecordset("tab_name")
filename =<name of spreadsheet>
Set ab = aa.workbooks.Open(filename, , True)
numsheets = aa.sheets.Count
k = 1
Do While k <= numsheets
sheetname = ab.sheets(k).Name
tabs.AddNew
tabs!tab_name = sheetname
tabs.Update
k = k + 1
Loop
ab.Close
End Function

This give me a count then I set up the loop.

 

[Stefan Hoffmann]

hi,

Click on tools + references
Set a reference to the Microsoft Excel 10.0 Object Library.
Dim xlx As Object, xlw As Object
Set xlx = CreateObject("Excel.Application")
Dim S As Excel.Worksheet

You shouldn't mix early binding with late binding. Choose one strategy.
I use in the development phase early binding with references. When I
deploy a database the references are removed.


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi J.,

Function refresh()
Dim aa As Object, ab As Object
Set aa = CreateObject("excel.application")
Set tabs = CurrentDb.OpenRecordset("tab_name")
Set ab = aa.workbooks.Open(filename, , True)
Do While k <= numsheets
Loop
ab.Close
End Function

Always close the objects you use. Your code may leave open Excel
instances running, check it with the Task Manager.

In your case:

tabs.Close
Set tabs = Nothing

ab.Close False
Set ab = Nothing

aa.Quit
Set aa = Nothing


mfG
--> stefan <--