Importing Excel Sheets to Access

[Guest]

How do i import more than one excel sheet or tabs at once?

 

[Ken Snell [MVP]]

If you mean in "one" import process, the answer is you don't. You must do
multiple imports. You may be able to automate this by using a macro or VBA
code.

 

[onedaywhen]

If you mean in "one" import process, the answer is you don't. You must do
multiple imports.

Are you sure? Try this:

INSERT INTO MyTable
(key_col, data_col)
SELECT DT1.key_col, DT1.data_col
FROM (
SELECT F1 AS key_col, F2 AS data_col
FROM [Excel 8.0;HDR=NO;Database=C:\MyWorkbook.xls;].[Sheet1$]
UNION
SELECT F1 AS key_col, F2 AS data_col
FROM [Excel 8.0;HDR=NO;Database=C:\MyWorkbook.xls;].[Sheet2$]
UNION
SELECT F1 AS key_col, F2 AS data_col
FROM [Excel 8.0;HDR=NO;Database=C:\MyWorkbook.xls;].[Sheet3$]
) AS DT1;

Jamie.

--

 

[Ken Snell [MVP]]

Good suggestion, Jamie. Hadn't tried this before.
--

Ken Snell
<MS ACCESS MVP>

If you mean in "one" import process, the answer is you don't. You must do
multiple imports.

Are you sure? Try this:

INSERT INTO MyTable
(key_col, data_col)
SELECT DT1.key_col, DT1.data_col
FROM (
SELECT F1 AS key_col, F2 AS data_col
FROM [Excel 8.0;HDR=NO;Database=C:\MyWorkbook.xls;].[Sheet1$]
UNION
SELECT F1 AS key_col, F2 AS data_col
FROM [Excel 8.0;HDR=NO;Database=C:\MyWorkbook.xls;].[Sheet2$]
UNION
SELECT F1 AS key_col, F2 AS data_col
FROM [Excel 8.0;HDR=NO;Database=C:\MyWorkbook.xls;].[Sheet3$]
) AS DT1;

Jamie.