Python code for running ms-Access Module Subroutin

I am very new to programming and this is my first question on stackoverflow. I am trying to make python open an .accdb file and run a subroutine which is already defined in Access. I manage to do it with Excel using this code:

import win32com.client
xl=win32com.client.Dispatch("Excel.Application")
xl.Visible=True
xl.Workbooks.Open(Filename="<mydirectory>\\open",ReadOnly=1)
xl.Application.Run("TestMe")
#...access spreadsheet data...
xl.Workbooks(1).Close(SaveChanges=0)
xl.Application.Quit()
xl=0

The Sub TestMe looks like this:

Sub TestMe()
MsgBox "Hi there"
End Sub

Running the Python code promtly launches Excel, opens the file open.xlsm and displays a messagebox. So far so good. Thanks to: Need skeleton code to call Excel VBA from PythonWin

I've modified the code to try to acheive the same with Access. I made a new .accdb file called "testdb" and copied the above subroutine "TestMe" into a VBA module. The modified python code looks like this:

import win32com.client
xl=win32com.client.Dispatch("Access.Application")
xl.Visible=True
xl.OpenCurrentDatabase("<mydirectory>\\testdb.accdb")
xl.Application.Run("TestMe")
#...access spreadsheet data...
xl.Workbooks(1).Close(SaveChanges=0)
xl.Application.Quit()
xl=0

The main change is that "Workbooks.Open" has changed to "OpenCurrentDatabase". I first tried to find something more similar, like "Databases.Open", but with no luck. Running the new code launches Access and opens the file testdb.accdb, but that's it, no messagebox appears. The only Console output I can imagine is of any interest is:

xl.Application.Run("TestMe")
File "<COMObject <unknown>>", line 14, in Run

result = self._oleobj_.InvokeTypes(*(dispid, LCID, wFlags, retType,        argTypes) + args)
pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2147352562), None)

I am quite at a loss. Any help would be greatly appreciated!

回答1:

Consider creating a new Access macro object with a RunCode action that calls the function in the module. Then, call the macro in Python's Windows COM API using use the DoCmd.RunMacro method.

MACRO

Macro
RunCode: TestMe()

NOTE: Only functions can be referenced with RunCode not subroutines unless you create a VBA module function that calls the subroutine: Call SubroutineName:

Python

import win32com.client
ac = win32com.client.Dispatch("Access.Application")
ac.Visible=True
ac.OpenCurrentDatabase("<mydirectory>\\testdb.accdb")
ac.DoCmd.RunMacro('MacroName')

ac.DoCmd.CloseDatabase
ac = None

回答2:

I am by no means an expert in Python but have reasonable familiarity with Access and Excel VBA. If I knew Python better and earlier in my amateur programming career I would never have attempted to do what I set out to do below using any VBA code at all. Given the investment in time in VBA code I had to find a way....

I have spent the last several days/week trying to find a way to talk Python->Access VBA with the following requirements:

call Access VBA function from Python
send parameter to Access VBA function from Python
return value from Access VBA function to Python

I made attempts with clr using pythonnet and IronPython and found more and more confusing and unclear error messages and exceptions. I tried the above suggested DoCmd.RunMacro method but Access macros do not return values. Tried a new(ish) version of Access macros (2010) called data macros which do have an action called SetReturnVar but they do not talk to VBA unless through the more traditional macros and as I mentioned above, traditional macros do not return values. Today I read some Microsoft documentation (Access Application.Run method) more carefully:

Access Application.Run method

I am not completely aware of the implications of that statement "you can't set a reference to an individual Microsoft Access database from any application other than Microsoft Access" but it occurred to me that a lot of the Python<->Office Application articles seemed more successful when talking Python<->Excel VBA. I reasoned that because I had been able to run Excel VBA <-> Access VBA in the past and if Python <-> Excel VBA worked as well as I had read, then a solution (albeit convoluted) seemed possible (I think the proper programmers call it a hack).

After about 1 1/2 hours of cutting/pasting code snippets and debugging:

Python

from win32com.client import Dispatch

FILELOC = r'C:\Users\Desktop\PyExcel.xlsm'
PROGNAME='Excel.Application'
num = 4

#open excel workbook containing VBA code
#...could do more to ensure excel isn't already running
xl = Dispatch(PROGNAME)
xl.Visible = True

#open excel file containing the VBA code
#...could do more to check if file is already open, etc 
xl.Workbooks.Open(Filename=FILELOC)
#call to VBA code within excel
rtrn_int = xl.Run("RunCOMObject", num)

#print return value
print(rtrn_int)

#Quit excel-this doesn't work very well and there are articles about
#Python or the COM object not being able to actually remove Excel
#from the task manager
xl.Quit()

Excel VBA

Option Explicit

Private Const ACCESS_FILELOC As String = "C:\Users\Desktop\Test.accdb"
Private Const TEMP_FILELOC As String = "C:\Users\Desktop\TestTemp.accdb"

Function RunCOMObject(intNum As Integer) As Integer

    Dim objAcc As Object, objProject As Object
    Dim accAppl As Access.Application
    Dim MyAppl As String

    MyAppl = "Access.Application"

    If Not IsRunning(MyAppl) Then  'Access not running, simply start 
          'up Access and open file
        Set accAppl = CreateObject(MyAppl) 'start Access
        accAppl.Visible = True
        accAppl.OpenCurrentDatabase (ACCESS_FILELOC) 'open file
    Else:  'Access is running
        On Error Resume Next
        Set accAppl = GetObject(, MyAppl)  'assign the running application 
            'to a variable
        On Error GoTo Err_File_Open 'use an error in attempting to rename 
            'the database of interest to determine if the open file is the 
            'desired file
        Name ACCESS_FILELOC As TEMP_FILELOC 'rename the file of interest
        Name TEMP_FILELOC As ACCESS_FILELOC 'file was successfully renamed 
            'therefore not open
        Call NoFileOrOther(accAppl, MyAppl)
    End If

Err_File_Open:
    'Required Access file is open
    RunCOMObject = accAppl.Run("TestLink", intNum) 'run the VBA function in 
       'Access
    accAppl.CloseCurrentDatabase  'close database
    accAppl.Quit 'quit Access
    Set accAppl = Nothing

End Function


Function IsRunning(ByVal MyAppl As String) As Boolean

    Dim applRef As Object

    On Error Resume Next 'error occurs if GetObject is unable to find a 
       'running version of the application

    Set applRef = GetObject(, MyAppl) 'attempt to obtain the required 
       'application object
    If Not applRef Is Nothing Then  'if application is already running
        Set applRef = Nothing
        IsRunning = True
    Else  'application is not running
        IsRunning = False
    End If
    Set applRef = Nothing
End Function

Sub NoFileOrOther(accAppl As Access.Application, MyAppl As String)

    On Error GoTo Err_No_FileOpen
    If accAppl.CurrentProject.Name <> "" Then  'Access active with another a 
       'different file open
        Set accAppl = CreateObject(MyAppl) 'start a new instance of Access
        accAppl.Visible = True
        accAppl.OpenCurrentDatabase (ACCESS_FILELOC) 'open file
    End If

    Exit Sub

Err_No_FileOpen:
    accAppl.OpenCurrentDatabase (ACCESS_FILELOC)  'in the event of Access 
       'being active without a database open

End Sub

Access VBA

This is a completely trivial example and belies the amount of code I had already written in Access VBA to warrant this workaround but it serves to demonstrate the methodology

Option Compare Database
Option Explicit

Function TestLink(intNum As Integer) As Integer
    TestLink = intNum + 10
End Function

Python Output:

Success!!!! The number was initially 4 in Python and was sent as a parameter to Excel and Access where it had 10 added to it before being returned via Excel to Python print(rtrn_int) = 14.

If anyone knows how to definitively (ie through similar rigour demonstrated above) send arguments from Python -> Access VBA and return a value to Python without using Excel VBA as an intermediary I would be very happy to hear from you. Alternatively methods using pythonnet referencing clr would be similarly appreciated.