Error linking queries with VBA function to Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am trying to link an Access query into Excel to feed it into a PivotTable
or as a simple list. Normally I use the Excel "Get External data" function
and then the Wizard or MS Query to do such thing and it always worked
flawlessly.

However, this particular query uses a VBA function that I created and stored
as a Module in Access. In the wizard, when I select any field of this query I
get an error message saying: "Undefined function "(function name)" in
expression". The fields of the other queries (not using the function) work
fine.

In MS Query, I have a similar error message with the query: "Can't access
table "(Name of Query)".

Is there any workaround to get this link to work? I am using Access/Excel
2000.

Thanks for your help

Emir
 
Only Access can execute Jet queries that include custom VBA functions.
Unless you can replace the custom function with an expression using only
built-in functions, you'll need to retrieve the data without the custom
function, and do whatever the function was doing in Excel instead.
 
Access needs to be open to run a query with a custom function.

Using VBA, (from within Excel) you could open an instance of Access via
automation, execute the query, place the results into an Excel worksheet,
close Access, etc.

or, the reverse: from Access, export the query to an Excel workbook
(automation optional). Then open Excel and place the exported data where you
need it (or use MS query as you have been on the exported data).

HTH,
 
Back
Top