Trouble Using Access Query for Excel Pivot Table

Jimbo Razza

Board Regular
Joined
May 21, 2005
Messages
114
Hi There

I'm trying to use a query I created in Access to drive a pivot table in excel. Problem is I've created a function in Access to do a calculation and when I try and import the data for the pivot it says excel does not recognise the function.

Is there a workaround for this?
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
If it's easy to replicate the function / calculation in Excel, consider creating a version of the query without the calc, importing that, and doing the calc in Excel.
I know you can run Excel functions from Access by setting a reference to Excel and I assume you should be able to do the reverse, but I don't know if that will let you run anything but the built-in functions.

Alternatively, have you tried using the query as an external data source for the pivot table? This gets around the import, and might do the job for you.
The option to use an external data source is on the first screen of the pivot table wizard.

Denis
 

Forum statistics

Threads
1,137,060
Messages
5,679,382
Members
419,824
Latest member
Mercy kiara

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
Top