[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

Importing from Excel to Access

rhodri.gabe

12/12/2006 4:02:00 PM

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.
2 Answers

NickHK

12/13/2006 2:56:00 AM

0

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

NickHK

<rhodri.gabe@citigroup.com> wrote in message
news:1165939347.245489.280530@16g2000cwy.googlegroups.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.
>

rhodri.gabe

12/19/2006 2:28:00 PM

0

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