I need to take 2 data reports, held in Excel, upload them to separate
tables in Access, execute a query to find the difference between the
two and output this reconciliation to the same Excel workbook, albeit a
different sheet. Most of this I have found straightforward, but it is
using Excel as an initial data source that is holding things up at the
minute.

I would like to import data held in a sheet of an Excel workbook into
an Access database using a SQL query within VBA. The Excel workbook is
connected via DSN and ADO to the Access database in question. I would
like to know if it is possible to execute a query such as the
following:

cn.Execute
"INSERT INTO TestCRecon ('ColumnA', 'ColumnB', 'ColumnC)
SELECT 'Column A', 'Column B', 'Column C'
FROM ExcelWorkbook.Worksheet

I have seen similar posts suggesting the following in place of
ExcelWorkbook.Worksheet:

[EXCEL 2003;C:\Task\Data.xls].[Sheet1$]"

but it hasn't worked for me. Can anybody help? Thanks in advance.

Why not just link the Excel tables to your access DB ?
Then can just query those linked tables.

NickHK

wrote in message
ups.com...
I need to take 2 data reports, held in Excel, upload them to separate
tables in Access, execute a query to find the difference between the
two and output this reconciliation to the same Excel workbook, albeit a
different sheet. Most of this I have found straightforward, but it is
using Excel as an initial data source that is holding things up at the
minute.

I would like to import data held in a sheet of an Excel workbook into
an Access database using a SQL query within VBA. The Excel workbook is
connected via DSN and ADO to the Access database in question. I would
like to know if it is possible to execute a query such as the
following:

cn.Execute
"INSERT INTO TestCRecon ('ColumnA', 'ColumnB', 'ColumnC)
SELECT 'Column A', 'Column B', 'Column C'
FROM ExcelWorkbook.Worksheet

I have seen similar posts suggesting the following in place of
ExcelWorkbook.Worksheet:

[EXCEL 2003;C:\Task\Data.xls].[Sheet1$]"

but it hasn't worked for me. Can anybody help? Thanks in advance.

Thanks, I'll try that, although am getting some odd results when using
linked tables compared to copy and paste so far.