Excel Pivot Table from Access database (function)

ecapox

New Member
Joined
Jul 28, 2006
Messages
25
So here i am with another issue that has popped up with this dam project im working on. Here is the idea:

I have created queries in an access database that compare 2 sets of data, previous and current, and compares them using a function that i wrote in VBA (fComp()). Instead of writing a HUGE iif statement in access, i wrote this function to make it nice and clean. It works very well.

Now comes the second part of my project, Excel pivot tables. Because everyone that will be using the data found in the Access database wants pivot tables, i must give them pivot tables. So i create a spreadsheet for each comparison query that i have written in Access and start setting up a pivot table using the Access database as the datasource. As simple as that sounds, i get this error "Undefined function 'fComp' in expression." I know what it's telling me, but i dont know WHY it's telling me this. Since Access would be runnign the query to populate the data for the pivot table, i cannot understand why it's pooping out on me. Is there a way to pass that function from Access VBA to Excel VBA?

help.

Emanuele
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
You run into this sort of thing all the time if you try and run queries from Excel thru Access. Nz() is a big bugbear of mine as it doesn't exist in Excel. You'll either need to push the data from Access to Excel or use queries that don't use Access-only functions, and do more processing in Excel...
 
Upvote 0
Or put a pivot table on a form in Access and skip Excel altogether.
 
Upvote 0
What kind of comparison do you need?
If it is for example the difference between two matching set of data, then you would need no function at all, since pivottable can do that very easily in excel.
A simple example:

assume the fields are (month,salesPerson,salesVolume)
assume you want to compare the results of each sales person for two different motn

this is very easy in xl pivot tables (using the "show data as" setting in the field options)

Your actual needs will determine the possible answers. As another example, a simple mathematical function N==>N is easily represented by a two fields table (as long as a small finite subset is needed). In that case, the function simply needs to be tabulated, no need for vba in the query.

It is true that VBA functions are not recognized by MSQuery, and that is really annoying.
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top