Access Save to Excel File

[jeffbruce]

I'm fairly new to Access/VBA but I know Excel like a brother. I have a database in Access featuring a VB-module calculation process that perform operations on queries and tables.

I want to write a RecordSet to an Excel file so that each time the calculation process is executed, a new Excel file is generated with an absolute File Name. Should the absolute File Name exist, the written RecordSet will completely overwrite the old File.

How should I do this?
I've tried something like -

Dim xlWrkBk As Excel.Workbook
Dim xlSht As Excel.Worksheet
Dim myRec As DAO.Recordset
Dim fldCustName As DAO.Field
Dim xlApp As New Excel.Application

Set xlWrkBk = GetObject("C:\Test.xls")
Set xlSht = xlWrkBk.Worksheets(1)

Set myRec = CurrentDb.OpenRecordset("tblPlantAddress")
Set fldCustName = myRec.Fields("Name")

myRec.MoveFirst

xlSht.Cells(1, "A") = fldCustName
xlWrkBk.Save

- and Access (or VB, not sure which one) does not recognize the custom type Excel.Application or any of those Excel.***'s.

Thanks in advance.

 

[pbaldy]

Have you set a reference to Excel in Tools/References?

 

[jeffbruce]

That eliminates the error I was having, now I'll just modify the code a bit. Thanks, trivial solution.

 

[jeffbruce]

New Problem

My database is ADO not DAO I think.. if that makes sense. When I enable the DAO tools my database fails, but enabling ADO tools is insufficient for VB to recognize the user-defined custom types:

ADO.Recordset
ADO.Field

How should I write to Excel with an ADO database?

 

[pbaldy]

You should also be able to set a DAO reference. It is not included by default in A2k or 2k2.

 

[jeffbruce]

Three DAO References

Under Tools -> References, there are three DAO libraries:

Microsoft DAO 3.51 Object Library
Microsoft DAO 2.5/3.5 Compatibility Library
Microsoft DAO 3.6 Object Library

I have tried each one separately and I get a run-time error for each one.
The enabling of DAO seems to throw off the rest of my database.

Under Tools -> References, there is one ADO library:

Microsoft ADO Ext. 2.8 for DDL and Security

This fails with a message of "Compile Error: user-defined type not defined"

 

[pbaldy]

I'll have to poke around. I include DAO 3.6 in most of my apps, without problem. What version of Access do you have?

 

[jeffbruce]

Microsoft Office Professional Edition 2003

appreciate the help.