Import multiple worksheets into one table

[lblanton]

I am trying to import an excel file with multiple worksheets into a single access table. The worksheets are formatted the same and with the same column headings but have varying rows. How do I import into a single access table?

Best regards,
lblanton

[PEB]

I am trying to import an excel file with multiple worksheets into a single access table. The worksheets are formatted the same and with the same column headings but have varying rows. How do I import into a single access table?

Best regards,
lblanton

Hi,

Use the folloing command:
DoCmd.TransferS preadsheet acImport, , _
"Employees","C: \my_file.xls", False, "Sheet1!A1: G12"

Cheers

:)

[LCS]

Hi,

Use the folloing command:
DoCmd.TransferS preadsheet acImport, , _
"Employees","C: \my_file.xls", False, "Sheet1!A1: G12"

Cheers

:)

I'm still just getting the first worksheet.

[NeoPa]

When you run it for Sheet2 - change the code to include that name instead of Sheet1.

TransferSpreads heet Method
The TransferSpreads heet method carries out the TransferSpreads heet action in Visual Basic.

expression.Tran sferSpreadsheet (TransferType, SpreadsheetType , TableName, FileName, HasFieldNames, Range, UseOA)
expression Required. An expression that returns one of the objects in the Applies To list.

TransferType Optional AcDataTransferT ype.

AcDataTransferT ype can be one of these AcDataTransferT ype constants.
acExport
acImport default
acLink
If you leave this argument blank, the default constant (acImport) is assumed.


SpreadsheetType Optional AcSpreadSheetTy pe.

AcSpreadSheetTy pe can be one of these AcSpreadSheetTy pe constants.
acSpreadsheetTy peExcel3
acSpreadsheetTy peExcel4
acSpreadsheetTy peExcel5
acSpreadsheetTy peExcel7
acSpreadsheetTy peExcel8 default
acSpreadsheetTy peExcel9 default
acSpreadsheetTy peLotusWJ2 - Japanese version only
acSpreadsheetTy peLotusWK1
acSpreadsheetTy peLotusWK3
acSpreadsheetTy peLotusWK4
Note You can import from and link to Lotus .WK4 files, but you can't export Microsoft Access data to this spreadsheet format. Microsoft Access also no longer supports importing, exporting, or linking data from Lotus .WKS or Microsoft Excel version 2.0 spreadsheets by using this method.

If you leave this argument blank, the default constant (acSpreadsheetT ypeExcel8) is assumed.


TableName Optional Variant. A string expression that's the name of the Microsoft Access table you want to import spreadsheet data into, export spreadsheet data from, or link spreadsheet data to, or the Microsoft Access select query whose results you want to export to a spreadsheet.

FileName Optional Variant. A string expression that's the file name and path of the spreadsheet you want to import from, export to, or link to.

HasFieldNames Optional Variant. Use True (–1) to use the first row of the spreadsheet as field names when importing or linking. Use False (0) to treat the first row of the spreadsheet as normal data. If you leave this argument blank, the default (False) is assumed. When you export Microsoft Access table or select query data to a spreadsheet, the field names are inserted into the first row of the spreadsheet no matter what you enter for this argument.

Range Optional Variant. A string expression that's a valid range of cells or the name of a range in the spreadsheet. This argument applies only to importing. Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.

UseOA Optional Variant.