"Bob Phillips" wrote ...

If the cell you were retrieving was fixed, you could query each
workbook with ADO, but it would need to retrieve everything and then work
through.

Bob,
Rather than the cells being the problem, I see the worksheet name
being the problem. If mybook.Worksheets(1) always had the same name,
say Sheet1, the OP could use the following query:

SELECT * FROM [Sheet1$A1:P65536];

Or rather

INSERT INTO
[MyDestRange]
SELECT * FROM
[Excel 8.0;Database=C:\MySearchFolder\MyBook1.xls;].[Sheet1$A1:P65536];

for each source workbook. And if there weren't too many workbooks it
could be done in one hit:

INSERT INTO
[MyDestRange]
SELECT * FROM
[Excel 8.0;Database=C:\MySearchFolder\MyBook1.xls;].[Sheet1$A1:P65536]
UNION ALL
SELECT * FROM
[Excel 8.0;Database=C:\MySearchFolder\MyBook2.xls;].[Sheet1$A1:P65536]
UNION ALL
SELECT * FROM
[Excel 8.0;Database=C:\MySearchFolder\MyBook3.xls;].[Sheet1$A1:P65536]
ORDER BY
1 ASC, 2 DESC;

ADO would discard any blank rows below the used range i.e. wouldn't
get all 65536 rows unless they had been populated.

Without the consistent sheet names, I know of no reliable way of
getting the name of the first worksheet using ADO. Both ADOX's Tables
collection and ADODB OpenSchema (adSchemaTables) method's recordset
appear in order of table name and, not surprisingly, have no 'table
ordinal position' property.

Jamie.

--