Calling an Excel Customed Function Within MS Access

[keirnus]

Hello once again...

I made a function in Excel. The function does some error checking within the Excel file.

To be easy for me, I want my code in MS Access to simply call the function in Excel.

I check this MS Support site: http://support.microsoft.com/kb/198571
I tried calling my function and it didn't work.

Here's my MS Access code:

    Dim oXL As Excel.Application    ' Excel Application

    Set oXL = CreateObject("Excel.application")
    oXL.Workbooks.Open (sXclFilePath)
    oXL.Application.ErrorCheck

    Set oXL = Nothing

Here's my Excel code:

Public Sub ErrorCheck()
    
    MsgBox "Inside Excel function."
    'Supposedly some error checking...
    
End Sub

The contents of the ErrorCheck function (in Excel) is just the MsgBox for testing. But my code above displays this error:

"Object doesn't support this property or method"


What's wrong with my code above?
How to call Excel customed function within MS Access?


-= keirnus =-

[keirnus]

nevermind...i thought it will take me long time to figure it out...

sharing my knowledge:

    With oXL
        .Workbooks.Open (sXclFilePath)
        .Workbooks(sXclFilePath).RunAutoMacros (xlAutoOpen)
        .Application.Run ("ErrorCheck")
        .Workbooks.Close
    End With

[NeoPa]

sharing my knowledge:

We like that attitude :)

PS. Would the following not work in place of your line #4 :

.Workbooks(sXclFilePath).ErrorChecking

[ADezii]

Hello once again...

I made a function in Excel. The function does some error checking within the Excel file.

To be easy for me, I want my code in MS Access to simply call the function in Excel.

I check this MS Support site: http://support.microsoft.com/kb/198571
I tried calling my function and it didn't work.

Here's my MS Access code:

    Dim oXL As Excel.Application    ' Excel Application

    Set oXL = CreateObject("Excel.application")
    oXL.Workbooks.Open (sXclFilePath)
    oXL.Application.ErrorCheck

    Set oXL = Nothing

Here's my Excel code:

Public Sub ErrorCheck()
    
    MsgBox "Inside Excel function."
    'Supposedly some error checking...
    
End Sub

The contents of the ErrorCheck function (in Excel) is just the MsgBox for testing. But my code above displays this error:

"Object doesn't support this property or method"


What's wrong with my code above?
How to call Excel customed function within MS Access?


-= keirnus =-

http://bytes.com/topic/access/insights/726129-how-use-excel-functions-access