Importing from Excel to Access

[rhodri.gabe]

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.

 

[NickHK]

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

NickHK

 

[Rhodri]

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