Del.
Feel free to modify the following to suit your need, but it should
definitely get you off to a good start.
'---------------------------------------------------------------------------------------
' Procedure : ListXlsSheets
' Author : CARDA Consultants Inc.
' Website :
http://www.cardaconsultants.com
' Purpose : List the sheet name of an Excel Workbook
' Copyright : The following may be altered and reused as you wish so long as
the
' copyright notice is left unchanged (including Author, Website
and
' Copyright). It may not be sold/resold or reposted on other
sites (links
' back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile - The Excel file to list the sheets
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
'
**************************************************************************************
' 1 2008-Jul-15 Initial Releas
'---------------------------------------------------------------------------------------
Function ListXlsSheets(sFile As String)
'On Error GoTo Error_Handler
Dim NumSheets As Integer
Dim i As Integer
Dim xlApp As Object
Dim xlWrkBk As Object
Dim xlWrkSht As Object
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application") 'Bind to existing instance
of Excel
If Err.Number <> 0 Then
'Could not get instance of Excel, so create a new one
Err.Clear
' On Error GoTo Error_Handler
Set xlApp = CreateObject("excel.application")
Else
' On Error GoTo Error_Handler
End If
xlApp.Visible = False 'make excel visible or not to the user
Set xlWrkBk = xlApp.Workbooks.Open(sFile)
NumSheets = xlWrkBk.Sheets.Count
For i = 1 To NumSheets
Debug.Print i & " - " & xlWrkBk.Sheets(i).Name
Next i
xlWrkBk.Close False
xlApp.Close
Set xlWrkSht = Nothing
Set xlWrkBk = Nothing
Set xlApp = Nothing
If Err.Number = 0 Then Exit Function
If Err.Number = 0 Then Exit Function
Error_Handler:
If Err.Number <> 438 Then
MsgBox "MS Access has generated the following error" & vbCrLf &
vbCrLf & "Error Number: " & _
Err.Number & vbCrLf & "Error Source: ListXlsSheets" & vbCrLf &
"Error Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
Exit Function
Else
Resume Next
End If
End Function
--
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.