Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function not allowed in Excel Query of Access DB?
Using Office 2003 Pro.
I wish to use a subset of data in an AccessDB Table as the data source for an Excel PivotTable. This is normally easy and I have done it many times. However, in this case one of the fields in the Table has several spellings for the same entity. I need to generate a name mapping function to standardize this field. This is also simple. I define a VBA routine in Access to perform the mapping. Now the Access Query has syntax (abbreviated here) like this: SELECT AccountQuery.TransDate, NameMapper([AccountQuery]![Memo]) AS CurName, ....FROM AccountQuery ... In this case, NameMapper is my VBA function defined in Access. Naturally, it gives the proper results in Access. Any reference, in Excel, to this Access Query fails complaining that NameMapper is unknown. I can work around this using VBA in Excel to perform the name mapping, but that leads me to some fairly complex code in getting the PivotTable set up and, more difficult, to catching the PivotTable Refresh so that I can do the right thing. Before I get into that, I would like to know if there is a way to get MSQuery to accept/use the Access VBA routine as defined in the Access Query? In other words, I want to define the query in Access, using the name mapping function, and then have Excel able to directly use that query as the data for a PivotTable. An alternative would be to get MSQuery to run the name mapping function as defined in an Excel VBA module. Since this one case is not too vital to me, I can work around it in other ways, however, I can forsee other cases in which a user defined function in Access would be very useful when used in a Access query providing data to Excel. -- Jim Parsells |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function not allowed in Excel Query of Access DB?
I'm not sure if you are just using Excel Queries or you are using an Access
object like set obj = getobject(C:\temp\database.mdb") I usually try to get the macro running 1st in the application they are using and then convert the macro to excel. "Jim Parsells" wrote: Using Office 2003 Pro. I wish to use a subset of data in an AccessDB Table as the data source for an Excel PivotTable. This is normally easy and I have done it many times. However, in this case one of the fields in the Table has several spellings for the same entity. I need to generate a name mapping function to standardize this field. This is also simple. I define a VBA routine in Access to perform the mapping. Now the Access Query has syntax (abbreviated here) like this: SELECT AccountQuery.TransDate, NameMapper([AccountQuery]![Memo]) AS CurName, ...FROM AccountQuery ... In this case, NameMapper is my VBA function defined in Access. Naturally, it gives the proper results in Access. Any reference, in Excel, to this Access Query fails complaining that NameMapper is unknown. I can work around this using VBA in Excel to perform the name mapping, but that leads me to some fairly complex code in getting the PivotTable set up and, more difficult, to catching the PivotTable Refresh so that I can do the right thing. Before I get into that, I would like to know if there is a way to get MSQuery to accept/use the Access VBA routine as defined in the Access Query? In other words, I want to define the query in Access, using the name mapping function, and then have Excel able to directly use that query as the data for a PivotTable. An alternative would be to get MSQuery to run the name mapping function as defined in an Excel VBA module. Since this one case is not too vital to me, I can work around it in other ways, however, I can forsee other cases in which a user defined function in Access would be very useful when used in a Access query providing data to Excel. -- Jim Parsells |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function not allowed in Excel Query of Access DB?
Just a regular query from Excel. The query and function run as expected in
Access. I am not using CreateObject. If MSQuery would handle the use of the Access function, there would be no VBA required in Excel. The desired behavior is: In Excel-Create a PivotTable using an external data source, the Query as defined in Access. Finish setting up the PivotTable. Be able to simply Refresh data on the PivotTable as more data is added to the AccessDB. -- Jim Parsells "Joel" wrote: I'm not sure if you are just using Excel Queries or you are using an Access object like set obj = getobject(C:\temp\database.mdb") I usually try to get the macro running 1st in the application they are using and then convert the macro to excel. "Jim Parsells" wrote: Using Office 2003 Pro. I wish to use a subset of data in an AccessDB Table as the data source for an Excel PivotTable. This is normally easy and I have done it many times. However, in this case one of the fields in the Table has several spellings for the same entity. I need to generate a name mapping function to standardize this field. This is also simple. I define a VBA routine in Access to perform the mapping. Now the Access Query has syntax (abbreviated here) like this: SELECT AccountQuery.TransDate, NameMapper([AccountQuery]![Memo]) AS CurName, ...FROM AccountQuery ... In this case, NameMapper is my VBA function defined in Access. Naturally, it gives the proper results in Access. Any reference, in Excel, to this Access Query fails complaining that NameMapper is unknown. I can work around this using VBA in Excel to perform the name mapping, but that leads me to some fairly complex code in getting the PivotTable set up and, more difficult, to catching the PivotTable Refresh so that I can do the right thing. Before I get into that, I would like to know if there is a way to get MSQuery to accept/use the Access VBA routine as defined in the Access Query? In other words, I want to define the query in Access, using the name mapping function, and then have Excel able to directly use that query as the data for a PivotTable. An alternative would be to get MSQuery to run the name mapping function as defined in an Excel VBA module. Since this one case is not too vital to me, I can work around it in other ways, however, I can forsee other cases in which a user defined function in Access would be very useful when used in a Access query providing data to Excel. -- Jim Parsells |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function not allowed in Excel Query of Access DB?
You may need to do this with a simple macro. Can you add an OR to your query
where each or is a different spelliing of the database item. "Jim Parsells" wrote: Just a regular query from Excel. The query and function run as expected in Access. I am not using CreateObject. If MSQuery would handle the use of the Access function, there would be no VBA required in Excel. The desired behavior is: In Excel-Create a PivotTable using an external data source, the Query as defined in Access. Finish setting up the PivotTable. Be able to simply Refresh data on the PivotTable as more data is added to the AccessDB. -- Jim Parsells "Joel" wrote: I'm not sure if you are just using Excel Queries or you are using an Access object like set obj = getobject(C:\temp\database.mdb") I usually try to get the macro running 1st in the application they are using and then convert the macro to excel. "Jim Parsells" wrote: Using Office 2003 Pro. I wish to use a subset of data in an AccessDB Table as the data source for an Excel PivotTable. This is normally easy and I have done it many times. However, in this case one of the fields in the Table has several spellings for the same entity. I need to generate a name mapping function to standardize this field. This is also simple. I define a VBA routine in Access to perform the mapping. Now the Access Query has syntax (abbreviated here) like this: SELECT AccountQuery.TransDate, NameMapper([AccountQuery]![Memo]) AS CurName, ...FROM AccountQuery ... In this case, NameMapper is my VBA function defined in Access. Naturally, it gives the proper results in Access. Any reference, in Excel, to this Access Query fails complaining that NameMapper is unknown. I can work around this using VBA in Excel to perform the name mapping, but that leads me to some fairly complex code in getting the PivotTable set up and, more difficult, to catching the PivotTable Refresh so that I can do the right thing. Before I get into that, I would like to know if there is a way to get MSQuery to accept/use the Access VBA routine as defined in the Access Query? In other words, I want to define the query in Access, using the name mapping function, and then have Excel able to directly use that query as the data for a PivotTable. An alternative would be to get MSQuery to run the name mapping function as defined in an Excel VBA module. Since this one case is not too vital to me, I can work around it in other ways, however, I can forsee other cases in which a user defined function in Access would be very useful when used in a Access query providing data to Excel. -- Jim Parsells |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function not allowed in Excel Query of Access DB?
I could modify the query, but it would be very unweildly and difficult to
change. I plan to fix this one by adding a name mapping table to Access and incorporating that into my Access query. However, using user defined Access functions to Access queries is a handy technique which works in Access but not in MSQuerys that use that view of the data as their source. My real question is how to get MSQuery to accept the use of such user defined functions in the Access query. -- Jim Parsells "Joel" wrote: You may need to do this with a simple macro. Can you add an OR to your query where each or is a different spelliing of the database item. "Jim Parsells" wrote: Just a regular query from Excel. The query and function run as expected in Access. I am not using CreateObject. If MSQuery would handle the use of the Access function, there would be no VBA required in Excel. The desired behavior is: In Excel-Create a PivotTable using an external data source, the Query as defined in Access. Finish setting up the PivotTable. Be able to simply Refresh data on the PivotTable as more data is added to the AccessDB. -- Jim Parsells "Joel" wrote: I'm not sure if you are just using Excel Queries or you are using an Access object like set obj = getobject(C:\temp\database.mdb") I usually try to get the macro running 1st in the application they are using and then convert the macro to excel. "Jim Parsells" wrote: Using Office 2003 Pro. I wish to use a subset of data in an AccessDB Table as the data source for an Excel PivotTable. This is normally easy and I have done it many times. However, in this case one of the fields in the Table has several spellings for the same entity. I need to generate a name mapping function to standardize this field. This is also simple. I define a VBA routine in Access to perform the mapping. Now the Access Query has syntax (abbreviated here) like this: SELECT AccountQuery.TransDate, NameMapper([AccountQuery]![Memo]) AS CurName, ...FROM AccountQuery ... In this case, NameMapper is my VBA function defined in Access. Naturally, it gives the proper results in Access. Any reference, in Excel, to this Access Query fails complaining that NameMapper is unknown. I can work around this using VBA in Excel to perform the name mapping, but that leads me to some fairly complex code in getting the PivotTable set up and, more difficult, to catching the PivotTable Refresh so that I can do the right thing. Before I get into that, I would like to know if there is a way to get MSQuery to accept/use the Access VBA routine as defined in the Access Query? In other words, I want to define the query in Access, using the name mapping function, and then have Excel able to directly use that query as the data for a PivotTable. An alternative would be to get MSQuery to run the name mapping function as defined in an Excel VBA module. Since this one case is not too vital to me, I can work around it in other ways, however, I can forsee other cases in which a user defined function in Access would be very useful when used in a Access query providing data to Excel. -- Jim Parsells |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - User Defined Function Error: This function takes no argume | Excel Programming | |||
How to call a function of an user defined DLL from Excel? | Excel Programming | |||
How to access MSSQL table in a user defined function | Excel Programming | |||
Access connection works in Macro, not in user-defined Function | Excel Programming |