Using Excel Worksheet Functions

[GKWilly]

I'm trying to write a function that calls the Search Worksheet function from Excel to find the position of a character so I can use it to perform a Mid function from that posistion. The character can appear anywhere in the field.
I've referenced the Excel 9 Object Library and my function still doesn't recognise the Search as a function.
My function is below, can anyone tell me what I'm doing wrong please.
Many thanks
Martin

Public Function SplitGrp(Grp As String) As String

Dim appXL As Excel.Application

Set appXL = Search("-", Grp, 1) + 1

SplitGrp = Mid(Grp, appXL, 1)

appXL.Quit

Set appXL = Nothing

End Function

 

[RoyVidar]

Why use Excel, when the InStr function is available in Access?

[tt]dim lngCount as long
lngCount=instr(GRP,"-")
if lngCount=0 then
SplitGrp "not in string"
else
SplitGrp = Mid$(Grp, lngCount, 1)
end if[/tt]

Else for the excel function to work, perhaps something like this

[tt]Dim appXL As Excel.Application
set appXL = new Excel.Application
lngCount = appXL.worksheetfunction.Search("-", Grp, 1) + 1
SplitGrp = Mid(Grp, lngCount, 1)
appXL.Quit
Set appXL = Nothing[/tt]

Or just substitute all the excel references with

[tt]lngCount = excel.application.worksheetfunction.Search("-", Grp, 1) + 1[/tt]

Roy-Vidar

 

[GKWilly]

Excellent, Cheers for that it works a treat )
Many Thanks
Martin