Import Multiple Worksheets With Same Column Headings In Same Workbook

[Dugantrain]

Hi all. My Subject Line pretty much sums up what I'm trying to do; I could've sworn that I saw this topic covered on a post a few months back, but I can't seem to find it. Anyway, I will have a workbook who's filename will be selected by a dialog box and that filename will be captured by an invisible text box (I'm sure you guys know the routine). The workbook has several worksheets which all contain the exact same column headings. I will need to import each worksheet into the same table. If someone could either provide the code or direct me to a post which shows how to accomplish this, it'd be much appreciated.

 

[Dugantrain]

Well, either this topic seemed too redundant for a Reply or nobody knew the answer. If it was the former, I apologize, however, if it was the latter, I finally found what I was looking for and here it is:

Public Function f_Import_XL()
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim xlApp As New Excel.Application
Set xlWB = xlApp.Workbooks.Open(My_Form.MyFileName_Textbox)
For Each xlWS In xlWB.Worksheets
    DoCmd.TransferSpreadsheet acImport, , "My_Table", My_Form.MyFileName_Textbox _
        , True, xlWS.Name & "!"
Next
Set xlWS = Nothing
Set xlWB = Nothing
Set xlApp = Nothing
End Function