[AccessD] Sum a column in Excel files in a specific folder

Hi Ervin

That's the beauty of an attached worksheet or Named Range. No troubles as there is no Excel object.
And if you use a query you don't even need to attach anything as the query can do that on the fly:

SELECT
    NameOfField1,
    NameOfField2
  FROM
    [Range] AS T IN '' [Excel 5.0;DATABASE=c:\windows\temp\some.xls;];

For the second syntax the trick is the brackets. They can be omitted in this example but are mandatory if you deal with filenames containing spaces:

  SELECT
    NameOfFiels1,
    NameOfField2
  FROM
    [Excel 5.0;DATABASE=c:\windows\temp\some.xls;HDR=YES].[Range] AS T;

In you case:

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

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

>>> viner at eunet.yu 16-03-2007 11:35 >>>
.. I have some problem with closing the Excel.