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

ms-access vba ms-access-2007 ms-office
Gedalya picture Gedalya · Jul 19, 2012 · Viewed 27.7k times · Source

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.

Answer

Daniel picture Daniel · Jul 19, 2012

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.

Related questions

Now() function with time trim

So the function =Now()....is there a way I can use this and only get the date, not the time? or is there just a function for this idea?

Read More
Import an Excel worksheet into Access using VBA

I am attempting to import an Excel spreadsheet into Access using some simple VBA code. The issue I have run into is there are 2 worksheets in the Excel file, and I need the 2nd worksheet to be imported. Is it …

Read More
Retrieve column values of the selected row of a multicolumn Access listbox

How can I retrieve the value of specified column of the selected row in a multicolumn listbox? I populate the listbox by setting the RowSource property with a SQL string. BoundColumn set to value 1. I can retrieve the value of …

Read More