Thanks for this.

I have a lot on my plate and won't get round to implementing this code for a
while.

I will let you know it worked though.

Regards,

JVLin

"Jamie Collins" wrote:

Gents, We seem to overlooked the OP's other question:

can I copy data from one workbook to another (or for that matter
from within a workbook) WITHOUT OPENING EACH WORKBOOK?

If your data is arranged as a database (i.e. rows of columns,
preferable with column headers) then yes.

A simple example to copy the entire contents of one table (worksheet)
to another workbook where the table does not already exist:

SELECT
MyKeyCol, MyDataCol
INTO
[Excel 8.0;HDR=YES;Database=C:\My
Folder\MyTargetWorkbook.xls;].[Sheet8$]
FROM
[Excel 8.0;HDR=YES;Database=C:\My
Folder\MySourceWorkbook.xls;].[Sheet8$]
;

A more complex example where the table does already exist in the
target workbook and you only want to append non-duplicated rows:

INSERT INTO
[Excel 8.0;HDR=YES;Database=C:\My
Folder\MyTargetWorkbook.xls;].[Sheet8$]
(MyKeyCol, MyDataCol)
SELECT
T1.MyKeyCol, T1.MyDataCol
FROM
[Excel 8.0;HDR=YES;Database=C:\My
Folder\MySourceWorkbook.xls;].[Sheet8$] T1
LEFT JOIN
[Excel 8.0;HDR=YES;Database=C:\My
Folder\MyTargetWorkbook.xls;].[Sheet8$] T2
ON T1.MyKeyCol=T2.MyKeyCol
WHERE
T2.MyKeyCol IS NULL
;

Jamie.

--