VBA module to import data from a specific Excel workbook tab

[LemonTwist]

I'm trying to write an Access VBA module which imports data from several Excel files. I'm using the DoCmd.TransferSpreadsheet command like this:

DoCmd.TransferSpreadsheet _
            acImport, _
            acSpreadsheetTypeExcel5, _
            "Table Name", _
            Path & "Excel Data File.xls", _
            True

However, the difficulty is that some of the Excel files have more than one tab containing data. How do I specify which tab I want imported into which table? The Excel source files don't contain any named ranges, as the data in them is dynamic and number of rows changes from day to day. Is there a way of using VBA to import all the data from one specific tab into an Access table?

 

[HiTechCoach]

I'm trying to write an Access VBA module which imports data from several Excel files. I'm using the DoCmd.TransferSpreadsheet command like this:

DoCmd.TransferSpreadsheet _
            acImport, _
            acSpreadsheetTypeExcel5, _
            "Table Name", _
            Path & "Excel Data File.xls", _
            True

However, the difficulty is that some of the Excel files have more than one tab containing data. How do I specify which tab I want imported into which table? The Excel source files don't contain any named ranges, as the data in them is dynamic and number of rows changes from day to day. Is there a way of using VBA to import all the data from one specific tab into an Access table?

Try Adding another parameter:

DoCmd.TransferSpreadsheet _
            acImport, _
            acSpreadsheetTypeExcel5, _
            "Table Name", _
            Path & "Excel Data File.xls", _
            True, _
            "Sheetname!"

or

DoCmd.TransferSpreadsheet _
            acImport, _
            acSpreadsheetTypeExcel5, _
            "Table Name", _
            Path & "Excel Data File.xls", _
            True, _
            "Sheetname!A1:Z26"