[AccessD] Import XLS File, Add a Field Not in Spreadsheet

Gustav Brock Gustav at cactus.dk
Thu Dec 16 12:42:37 CST 2004


Hi 65

You can link the Named Range into a query directly.
Here is for a select query which you easily can modify for a create
table or append query:

<SQL>

  SELECT 
    *,
    'X' AS Status
  FROM 
    [SomeNamedRange] AS xlsRange 
  IN '' [Excel 5.0;HDR=YES;IMEX=2;DATABASE=d:\path\yourworkbook.xls;];

</SQL>

Adjust HDR to NO if no headers.
Append a $ sign to a worksheet name if not using a Named Range:

  FROM 
    [SomeWorkSheet$] AS xlsSheet 

/gustav

>>> cyx5 at cdc.gov 16-12-2004 15:19:42 >>>
I am importing named ranges from Excel Spreadsheets into Access and
all
is well.  What I want to do is add a field while doing the import, a
status field.  Right now I have seven named ranges, one for new,
pending, existing, etc.  I am putting each into its own table, then
updating the status field to N, P, X, etc.  Then I have to pump all
these into a central table.  It would be nice if I could just directly
pump each spreadsheet into the final table instead of dancing around.




More information about the AccessD mailing list