“User-defined type not defined” error in VB in Access 2007

Question

I'm receiving a compile error on the following line of code:

Dim oXL As Excel.Application

The code is in VB in MS Access 2007. The line above is the beginning of a segment to generate an MS Excel file. The obvious answer to me was to ensure that the "Microsoft Office 12.0 Object Library" is checked under Tools > References. I've done this but the error persists. Does Excel need to be installed side-by-side for this to work? What have I done wrong? Thanks in advance.

Solution

You need to reference Microsoft Excel 12.0 Object Library or use late binding. Late binding is almost always necessary if you will be sharing your project with users who may have different versions of Excel installed.

For late binding, you would instead do:

Dim oXL as object
Set oXL = CreateObject("Excel.Application")

Then your code should work as expected, without the need to make the reference... assuming you aren't using any other Excel specific values or objects.