'Undefined Function' Error in Microsoft Query

[Jim Molter]

Have an Access 2000 query that utilizes a user-defined
function that I wrote in Access VBA. Query and Function
work fine in Access, but when I try to use the query as a
data source for an Excel Pivot Table, MS Query returns
an "Undefined Function" error.

Is there any way to work around this limitation?

 

[Stephen Bullen]

Hi Jim,

Have an Access 2000 query that utilizes a user-defined
function that I wrote in Access VBA. Query and Function
work fine in Access, but when I try to use the query as a
data source for an Excel Pivot Table, MS Query returns
an "Undefined Function" error.

Is there any way to work around this limitation?

There are two alternatives

1) If the function isn't too difficult, you might be able to include
directly in the query - i.e. instead of the VBA call

2) Instead of using MS Query, use automation to open Access, open the
database, run the query in Access and copy the results to Excel.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk