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

Gustav,
thanks for the great explanation, but in my situation it'll be better to 
stay at VBA, because I don't know in advance the number of the files and 
their names...
Ervin
----- Original Message ----- 
From: "Gustav Brock" <Gustav at cactus.dk>
To: <accessd at databaseadvisors.com>
Sent: Friday, March 16, 2007 12:14 PM
Subject: Re: [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.
>