Any way to run an Access VBA Subroutine/Function/Method from within Excel VBA
Any way to run an Access VBA Subroutine/Function/Method from within Excel VBA
Is there any way to call an Access VBA subroutine, function or Class Method from within an Excel VBA routine?
In Access, by using Tools, References, I can add-in subs, functions, classes from other databases, but when in Excel, Outlook, etc. VBA, I'm unable to jump across applications to invoke references to Access databases.
Many thanks in advance if anyone knows a way I can run a program in an Access database from within Excel?
As written, Excel and Access need to be in the same directory.
Access need not be open.
In Excel:-
Option Explicit
Private Sub cmdCallAccessSub_Click()
Dim strDBName As String
Dim strMessage As String
strDBName = ThisWorkbook.Path & "\" & "TestAccess_A2003.mdb"
With CreateObject("Access.Application")
.OpenCurrentDatabase strDBName
.Run "Invoke", "CalledFromExcel", strMessage
.Quit
End With
MsgBox strMessage
End Sub
In Access:-
Option Explicit
Option Compare Text
Public Sub Invoke(ByVal strSubName As String, _
ByRef strReturnMessage As String)
Dim strReturn As String
On Error Resume Next
Application.Run strSubName, strReturn
If (Err.Number) Then
strReturnMessage = Err.Description
Err.Clear
Else
strReturnMessage = strReturn
End If
End Sub
Public Sub CalledFromExcel(ByRef strReturn As String)
On Error Resume Next
strReturn = "Returned message from Access."
End Sub