Excel - Import Specs???

  • Thread starter Thread starter Bonnie
  • Start date Start date
B

Bonnie

Hi. Using A02 on XP. When I import an Excel file, I don't
get the Advanced button in the lower left corner of the
Import Wizard so I can't create/edit/save the import
specs (skip fields, name fields cannot be automated). I'd
like to make this as smooth as possible with a macro.
Must I create a delimited file, create specs and then
just select those specs for the Excel file?

Any suggestions? Am I missing something simple? (Usually
the case.)

Thanks in advance for any advice or help.
 
Hi Bonnie,

No, Access doesn't store import specs for Excel worksheets.
One thing you can do (Jamie Collins is an expert on this) is write
queries that select fields and map their names. Jamie posted these
examples here a few weeks ago:

To import into an existing table:

INSERT INTO
MyExistingTable
SELECT
F1, F3, F5
FROM
[Excel 8.0;HDR=No;database=C:\MyWorkbook.xls;].[Sheet1$A1:E100]
;

To import into a new table:

SELECT
F1 AS MyCol1,
F3 AS MyCol2,
F5 AS MyCol3
INTO
MyNewTable
FROM
[Excel 8.0;HDR=No;database=C:\MyWorkbook.xls;].[Sheet1$A1:E100]
;
 
Back
Top