How do summarize survey data stored in different Excel files?

[Guest]

I'd like to summarize the returned customer surveys, which are stored in
different Excel files, preferably using a VBA template.

The data structure of the source files are linear, i.e. all answers are
within one column in the source files.

I appreciate your support. Thanks

 

[Jamie Collins]

I'd like to summarize the returned customer surveys, which are stored in
different Excel files
all answers are
within one column in the source files.

Do you mean something like...?

SELECT
'File1' AS filename,
SUM(MyXLCol) AS summarization
FROM
[Excel 8.0;HDR=YES;Database=C:\File1.xls;].MyXLTable
UNION
SELECT
'File2' AS filename,
SUM(MyXLCol) AS summarization
FROM
[Excel 8.0;HDR=YES;Database=C:\File2.xls;].MyXLTable
UNION
SELECT
'File3' AS filename,
SUM(MyXLCol) AS summarization
FROM
[Excel 8.0;HDR=YES;Database=C:\File3.xls;].MyXLTable
;

Jamie.

--