Importing multiple worksheets from a spreadsheet into Access

[stebbins]

Has anyone figured out how to automate the import of multiple worksheets from one specific spreadsheet. I wrote a macro to do this but the range name is then locked in. This is an ongoing project. Each month new worksheets will be added to the spreadsheet; therefore, more sheets will be imported and the names will be changing. I need a variable to identify the worksheets. I am at a loss. I also need to setup code for a form that allows the user to browse and select the appropriate spreadsheet file. Someone please help. Thanks!

 

[Hmadyson]

do you have a reference to Excel? If so you can create a workbook object, open it hidden, go through the worksheets and get the names, maybe you would put those names into a list box and the person can choose which pages they want to use.

dim wb as Excel.Workbook
dim xl as Excel.application
dim wks as Excel.Worksheet

    set xl = new Excel.Application
    xl.workbooks.open "Wb full path"
    for each wks in wb.Worksheets
         do something based upon wks.Name
    next

    wb.Close False
    xl.Quit
    set wb=nothing
    set xl=nothing
    set wks=nothing