Export to Excel - Error 3061

[Soegstad]

Hi guys,
I'm having some troubles passing a query to an existing .xls.

Code:
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qryTest", dbOpenDynaset)
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\Finanstemp.xls")
xlApp.Windows(1).Visible = True
xlBook.Sheets(1).Range("A1").CopyFromRecordset rst
xlBook.Close savechanges:=True
xlApp.Quit

Set rst = Nothing
Set dbs = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

It's driving me crazy, since I keep getting the same error even if I try other queries.

Any ideas? Thanks.

Forgot to mention that the code stops at:
Set rst = dbs.OpenRecordset("qryTest", dbOpenDynaset)

 

[Soegstad]

Looks like I've found the error. I did't have the reference to DAO. However, now I've got a problem with adding DAO to the references. Keep getting the error:
Name conflicts with existing module, project or object library
when i try to add.
Any ideas?

 

[The_Doc_Man]

Something you have defined in your own database has the same name as something in the DAO module.

Use the object browser (from a code/module window) to examine the contents of the DAO module. See what names it uses. If one of them looks familiar, you've found the culprit.

OR...

I think I've seen this one, too. If you have a reference to DAO and a reference to ADO at the same time, and if you careless qualify or instantiate something from one of those modules, you "step on" the other module, thus causing Access to get confused. Again, use Object Browser to figure out what name it could be.

 

[pbaldy]

What other references are selected?

 

[Soegstad]

The Doc: Thanks for your reply. I will try your suggestions. Not too familiar with VB though.

Pbaldy:
VB for applications
MS Access 12.0 Object Library
OLEAutomation
MS Office 12.0 Access Database engine Object Library
MS Excel 12.0 Object Library

I'm running Access 2007

 

[Soegstad]

Looks like there's a problem with Access itself. tried to open a blank database and I'm still getting the same error. Maybe I need the dao360.dll?
Nope, no joy. Downloaded a fresh copy of dao360.dll and registered it, but still the same.

 

[Soegstad]

Last try:
Could somebody help me translate the code from DAO to ADO?
Thanks again!

 

[boblarson]

    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook

    Dim rst As ADO.Recordset


    Set rst = New ADODB.Recordset

    rst.Open "qryTest", CurrentProject.Connection, adOpenStatic, adLockReadOnly

    Set xlApp = New Excel.Application
    Set xlBook = xlApp.Workbooks.Open("C:\Finanstemp.xls")
    xlApp.Visible = True
    xlBook.Sheets(1).Range("A1").CopyFromRecordset rst
    xlBook.Close True
    xlApp.Quit

    rst.Close

    Set rst = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing