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

On Tue, 8 Jul 2008 08:29:01 -0700, J. Freed wrote:

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

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

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.

"fredg" wrote:

> On Tue, 8 Jul 2008 08:29:01 -0700, J. Freed wrote:
>
> > 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.
>
> --
> Fred
> Please respond only to this newsgroup.
> I do not reply to personal e-mail
>

hi,

fredg wrote:
> 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 <--

hi J.,

J. Freed wrote:
> 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 <--