Office VBA automation

[AndersNorell]

Hi everyone,
I'm trying to make a MS Access add-in using VB.NET, the solution will be
used for a maternal care company. The add-in works fine, however now the
VB.NET add-in needs to call VBA code in the MS Access database and also
pass parameters (i.e. can't use macros).


Does anyone know if it's possible to do this? If so, how?

As an example I would like to do the following:

VB.NET add-in sample code:

Dim mappAccess As Microsoft.Office.Interop.Access.Application
        Dim OrderArgs As String
        Const strConPathToSamples = "C:\Documents and Settings\Anders\" _
                    & "My Documents\"
 
        strDB = strConPathToSamples & "MyAccessDatabase.mdb"
       [COLOR=Lime] ' Gets current instance of MyAccessDatabase.[/COLOR]        
        mappAccess = _
           GetObject(strDB).Application
 
 [COLOR=Lime]       'Open a form[/COLOR]
        OrderArgs=<something>
        mappAccess.DoCmd.OpenForm("Orders", , , , , , OrderArgs)
[COLOR=Lime]        'Run a macro[/COLOR]
        mappAccess.DoCmd.RunMacro("Order_macro")
 
[B]'Can you call a VBA sub-procedure located in a Class module or a standard module, and if so how?[/B]
        Dim OrderNr as String = "A0001"
        Dim OrderStatus as Integer = 0
        mappAccess.<what should be written here?> ("UpdateOrder", OrderNr, OrderStatus)

Sample MS Access VBA code from MyAccessDatabase.mdb, inside standard module "Module1":

'This function is located in a MyAccessDatabase class module or standard
'VBA module and should be called by MyAccessAddin.dll:
        Public Function UpdateOrder(OrdNr As String, OrdStatus As Integer) As Integer
        'Do what you gotta do
 
        'Return
        UpdateOrder = 0
        End Function

I have been unable to find any information about this. Does anyone know
anything about this?

Thanks for any help,
Anders, programmer

[RobDog888]

It may be easier to just write the fuinction in .net instead of running it inside of Access. Not to mention that this way your code will be centralized in your .net project.

[AndersNorell]

Well, thanks for the help, why didn't I think about that!!

Excuse me for the irony *no harm ment , but my example naturally assumes that the call from the add-in back into MS Access is needed, and that a macro isn't sufficient since it won't allow passing any parameters (or least I think so).

I guess the answer is that one has to use some ugly solution with dummy forms using openargs as parameters, together with the DoCmd.Openform method.

Cheers,
Anders