#1
|
||||
|
||||
Linking to an Access query with function call from Excel
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? |
#2
|
|||
|
|||
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). |
#3
|
||||
|
||||
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! |
#4
|
||||
|
||||
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! |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How does an Access query of linked Excel tables decide on field formatting? | junkcarp | Excel | 1 | 09-01-2020 02:43 PM |
Excel ADO query from Access Not Working | bconner | Excel Programming | 0 | 03-22-2017 09:29 AM |
Linking specific cells in Excel to function with MergeField? | Fabulist | Word | 0 | 02-17-2016 02:19 AM |
Using MS Access Query and VBA to create Excel Chart | jrmontgom | Excel Programming | 0 | 04-07-2015 11:21 AM |
How do I call/execute an Excel macro from Access 2003 | KramerJ | Excel | 2 | 05-11-2009 11:33 AM |