pull all worksheets in a excel file into a table

[webcat]

Hi

i need to get all the worksheet names from an excel file and dump them into a table

- so if the excel file was as created, my table would look like:

Sheet1
Sheet2
Sheet3

i can find various methods for listing the worksheet names from within excel, but how can i get Access (VBA) to reference the excel file and then pull in the worksheet names?

any help much appeciated.

using Office 2003

thanks
webcat

 

[ByteMyzer]

The following code will open an Excel file and append the names of all the worksheets into a table:

Public Sub Excel_Sheets_List( _
    ByVal sFileName As String, _
    ByVal sTableName As String, _
    ByVal sFieldName As String)
 
    Dim rs As DAO.Recordset
    
    Dim xlsApp As Object
    Dim xlsBook As Object
    Dim xlsSheet As Object
    
    Set xlsApp = CreateObject("Excel.Application")
    
    Set xlsBook = xlsApp.workbooks.Open(sFileName)
    
    Set rs = DBEngine(0)(0).OpenRecordset(sTableName, dbOpenTable)
    
    For Each xlsSheet In xlsBook.Sheets
        With rs
            .AddNew
            .Fields(sFieldName) = xlsSheet.Name
            .Update
        End With
    Next
    
    rs.Close
    Set rs = Nothing
    
    Set xlsSheet = Nothing
    
    xlsBook.Close False
    Set xlsBook = Nothing
    
    xlsApp.Quit
    Set xlsApp = Nothing
 
End Sub

For example, to append the worksheet names from C:\MyFolder\MyFile.xls into table WorkSheetList, field wsName, use:

Excel_Sheets_List "C:\MyFolder\MyFile.xls", "WorkSheetList", 'wsName"

See if this solution works for you.