0

I am connected to an Excel application and can execute the "Debug"->"Compile VBAProject" from my Python code using win32com like so (inspired by code from here):

from win32com import client

def compile(self):
    self.__excel = client.GetActiveObject("Excel.Application")
    compile_button = self.__excel.VBE.CommandBars.FindControl(1, 578)
    compile_button.Execute()

If there is a compilation error in the Excel VBA code I get a popup message in Excel telling me the error just fine.

Now I would like to check from the Python code if there was a compilation error and raise an exception if there was. I don't necessarily need the compilation error to be part of the exception but if that were possible I would of course gladly take that, too.
Can this be done somehow?
I've been experimenting with all kinds of window counts before and after the compilation etc. but so far have not found a property of any object that would indicate that there was a popup or a compilation error.

user2606240
  • 641
  • 1
  • 6
  • 20

1 Answers1

0

Ok, I found a somewhat ugly but doable way - that I would like to document for others having the same issue: You need to import a code file into the opened Excel file that has (at least) one function defined. Then you can call this function from your Python code and catch any exception. If there was an exception your code - including the imported file - did not compile, if there is none the compilation was pass.

Here's my code:
compile_code.bas

Public Sub compileCode()
    ' doesn't need to do anything, it just needs to be available!
End Sub

Python file

from win32com import client

def compile(self) -> bool:
    self.__excel = client.GetActiveObject("Excel.Application")
    self.__book = self.__excel.ActiveWorkbook
    self.__book.VBProject.VBComponents.Import(<Path_to_compile_code.bas>)
    try:
        self.__excel.Application.Run("compileCode")
        # if you reach here the code compiled
        return True
    except Exception:
        return False
user2606240
  • 641
  • 1
  • 6
  • 20