Excel 2010 VBA Error: Compile error User-defined type not defined

[TomTTT]

Hi,

I'm seeking to run some VBA code in Excel 2010 on a Windows XP machine. The goal of the code is to take a large Excel file and break it down into a set of distinct files (e.g. Report_Department100, Report_Department200, Report_Department300) which will then be examined and emailed out to the various departments.

Here is the error I get when I attempt to run it: "Compile error: User-defined type not defined." Excel highlights the "Dim DB As Database" line specifically.

Here is the context of the code:

Sub CreateFiles()

Dim DB As Database
Dim rst As Recordset
Dim rst2 As Recordset
Dim strSQL As String
Dim DeptID As String
Dim CustID as DAO.QueryDef
Dim FilePath As String

What do you suggest I do to address this problem?

 

[pgc01]

Hi Tom
Welcome to the board

You must be missing (at least) one reference.

Check the documentation of the code you are using.

In the VB Editor, in Tools->References you must set the appropriate references. My first guess is Microsoft DAO Object Library

 

[TomTTT]

Hi PGC,

Thanks for your prompt reply. I followed your suggestion and added a reference to "Microsoft DAO 3.6 Object Library." When I ran the code again, there was no compile error.

However, another error has come up.
Error: run-time error 424: Object required.

When I hit debug, VB Editor points to this line:

Set DB = CurrentDb

 

[Norie]

CurrentDb belongs to Access, not Excel.