Hi


I have some pivot tables/graphs in Excel with data linked to an Access query.


What I'd like to do is get the query to derive a value from a VBA function I've made in a module within Access, so I can use this data in Excel.




The function runs fine in the query from within Access, it's when I try to link to the query from Excel I run into a brick wall.


It seems this isn't possible because I either get an unrecognised function message, or the query does not appear in the list of possible links when I try to connect, depending on whether I connect via Data/From Access or Data/From Other Sources.


Does anyone know how I can connect to an Access query with a function call in it?

Simply create a similar function (an UDF) in Excel, and use it!

In case this function needs some data from Access table(s) to be available, use ODBC query/queries to read those data into some hidden sheet(s) in Excel, and set those queries to be refreshed on open. And in your Excel UDF use hidden querytable(s).

Interesting - I'll give that a go.


The other thing that occurred would be to run the Access query from excel and get it to make a table, then link to that.


Just a shame you can't have a function on the Access side.


Thanks for your reply!

OK, I ended up with:


1. Excel sheet supplied by colleague with raw data

2. New Excel sheet linked to my coleague's sheet which has the functions I need to make a few extra columns

3. Access database links to the new sheet to give the queries I wanted

4. A 3rd Excel sheet links to the Access queries and presents the results


So this is REALLY round-the-houses, but I couldn't replicate all the Access queries in Excel with the level of knowledge I have.



At least it works!