User defined function in Access will not let me link to pivot table

Hi is the data source for the pivot table in Access or Excel or SQL? What does the UDF do, can you post a sample? What versions of Excel/Access/SQL?

You can use ADO to pull the data into either Access or Excel, but you can't use a pivot table as a data source. Although potentially you could use a crosstab query in Access instead of a pivot in Excel
...Terry

Depends on what the UDF does, and whether it uses Access-specific functionality. If it does, then you have little choice other than the temp table method (which often works out better with pivot table data anyway).

The source data  for the pivot table is in Access 2010, the Excel version I am using is 2010, and here is an example of the  UDF. I hope i am posting it correctly.

Public Function RtnMonthNum(pstrMonName As String) As Integer
'*******************************************
'Purpose:   Returns month number (1 - 12)
'           when provided a complete or partial
'           month name.
'Coded by:  raskew
'Input:     From debug (immediate) window:
'           1) ? RtnMonthNum("April")
'           2) ? RtnMonthNum("Sep")
'Output: 1) 4
'           2) 9
'*******************************************
 
Dim strHold  As String
Dim strMonth As String
Dim intMonth As Integer
 
   strMonth = "JanFebMarAprMayJunJulAugSepOctNovDec"
   strHold = Left(pstrMonName, 3)
   intMonth = InStr(strMonth, strHold)
   RtnMonthNum = intMonth \ 3 + 1
 
End Function

The function works exactly the same in Excel, no changes required.
...Terry

How do i plug it into excel? i have tried, but when i refresh my pivot i get the user defined function error.

Sorry let's backtrack a bit here. Can you advise how Access uses the function? Are you running a query in access which provides the data for the pivot table? Does the query use the UDF? Does the UDF work correctly in Access when you run that query?

So access uses the function in a query to return the number of the month based on a field that only has month names. Yes i am running a query in access that provides the data to the pivot, Yes the query does use the UDF and works correctly in my query.

As part of the troubleshooting can you open another file, connect to the query and just import the data only into a spreadsheet (not a pivot), and check what is in the UDF field? By the way can you post a screenshot of the error message you are getting?

So the query I want to import does not show up as an option on the import list when I attempt to get external data, but when I remove the UDF the query does show as an option. I was attempting to work around this by linking the pivot table to a seperate query in the same access db then adjusting the "connetion propeties" under "change data source". That is when I get the errors on the screen shots
Screenshot.doc

Thank you the data comes from a microstrategy report I will look to modify that report good to know what my options are