pythonexcelvisual-studioxlwings

Exception thrown in Visual Studio when trying to use xlwings in python to run a basic subroutine in an excel VBA module

I'm trying to use Microsoft Visual Studio to run python code that uses the xlwings library to open a macro-enabled Excel workbook and run a VBA subroutine called MySub() that lives in Module1. When I try to run my code, I get an exception thrown in Visual Studio. I've included a snapshot image of the exception thrown (I know this is considered bad form, but I genuinely don't even know what I'd type from the exception... or even how useful it would be)

I'm using: Microsoft Visual Studio Community 2022 (64-bit) - Current Version 17.11.1 Python 3.9.13 (64-bit) Microsoft Excel for Microsoft 365 MSO (version 2407 Build 16.0.17830.20056) 64-bit

VS Exception

Attempt 1

import xlwings as xw

file_path = r'C:\PythonExcelTest\pythonmacro.xlsm'
wb = xw.Book(file_path)                               #<--- Exception thrown here
macro1 = wb.macro("Module1.MySub")
macro1()

Attempt 2

import xlwings as xw

file_path = r'C:\PythonExcelTest\pythonmacro.xlsm'
app = xw.App(visible=True, add_book=False)
wb = app.books.open(file_path)                        #<--- Exception thrown here
run_macro = wb.app.macro("Module1.MySub")
run_macro()

both of the above attempts produced the same exception when trying to open the macro-enabled workbook. Attempt 1 was based off the xlwings documentation online and attempt 2 was based off the youtube example linked below.

Any assistance or direction on what could be causing the exception would be greatly appreciated thank you.

xlwings YoutTube example

Solution

  • With the comment "Running without debugging does not cause a crash." I was making the differentiation between running the code in VS either with the general default F5, 'Start Debugging' which causes the crash or selecting Ctrl+F5, 'Start Without Debugging' which doesn't.

    VS Debug Options

    But certainly both of your code examples are fine and would work from the command line or in another IDE in debug mode or not.

    I use Pycharm and there is certainly no issue running your code in either mode on that IDE, (Community or Paid versions).

    So the issue appears to be VS related and may be specific to 2022 Community release or wider ranging. I don't have another VS release installed currently to check.
    As noted while I use VS for C# and a couple of other things I've always used Pycharm or notepad for Python so have never encountered this error with Xlwings so no experience if this is something new or always been an issue with VS.

    There are some other SO Posts with the same error code on VS but none appear to relate to opening a valid workbook.
    I tried a few different code arrangements but VS always failed opening the workbook in debug mode.
    And as mentioned, when creating a new workbook, wb = xw.Book() instead, there was no crash.

    Not being an expert in VS I can't say if there is a config or if adding specific files/libs could fix or workaround the issue.
    It may be worth raising the issue with the Xlwings Dev to see if they have any suggestions.