Function preventing query results from showing in Excel (1 Viewer)

thisbenroberts · Apr 4, 2013

I have a number of queries in Access 2007, the results of which I have imported to a table in Excel 2007. Most days, I open the Excel file, refresh the table, and the data updates.

I created a simple public function in VBA to use in one of the queries. When I try to refresh the table, I get an error message that says " Function Undefined". The query runs fine in Access, but gives me this strange error when I update the Excel table.

To trouble-shoot it, I re-created the query without including the VBA function. Opened a new Excel file, linked the data, and had no problem at all.

Then I created the the query and included the VBA function. I opened Excel, and tried to link the data but couldn't find the query listed.

For some reason, Excel doesn't seem to like queries that use VBA functions? Has anyone else run in to this? Is there some way to resolve this issue?

Thanks

Here is the VBA function, if it matters.

Code:

Public Function SplitString(MainStr As String, strDelimeter As String, intSpot As Integer) As String
Dim varSplit As Variant

varSplit = Split(MainStr, strDelimeter, , vbTextCompare)
SplitString = varSplit(intSpot)

End Function

thisbenroberts · Apr 4, 2013

Quick update, if I remove the VBA function from the query, then I can select the query again in Excel.

jdraw · Apr 4, 2013

Can you post the sql (query) that uses this function?

The function works in Access

Code:

Public Function SplitString(MainStr As String, strDelimeter As String, intSpot As Integer) As String
Dim varSplit As Variant

varSplit = Split(MainStr, strDelimeter, , vbTextCompare)
SplitString = varSplit(intSpot)

End Function

Sub testsplit()
Dim main As String
Dim dlim As String
Dim ispt As Integer
ispt = 8
dlim = " "
main = " MsgBox Error Err.number Err.Description ) in procedure PopulateZeroSales of Module AWF_Related"
Debug.Print SplitString(main, dlim, ispt)
End Sub

The output is

PopulateZeroSales

thisbenroberts · Apr 4, 2013

Here is the SQL for the query. The query and the function both work fine in Access. The problem is when I try to get the results of the query to show up in Excel.

Code:

SELECT TodaysINM.[Invoice Status], TodaysINM.[Rx Number], SplitString([Rx Number],"-",0) AS Expr1
FROM TodaysINM
WHERE (((TodaysINM.[Invoice Status])="HOLD CMN INV COMP"));

Function preventing query results from showing in Excel (1 Viewer)

thisbenroberts

New member

thisbenroberts

New member

jdraw

Super Moderator

thisbenroberts

New member

Similar threads

Users who are viewing this thread