CODEWITHSUNDEEP
pythonexcelvbaxlwings
Bulky
Bulky

Reputation: 43

Using xlwings and macros in Personal.xlsb causes a "referenced by another workbook and cannot be closed" error when opening books

I usually don't ask questions and try to figure things out myself so apologies if the formatting or phrasing is poor.

I have a Python script which I want to call through a macro in excel using xlwings. I want this to be available in all the workbooks I open so I'm using PERSONAL.xlsb in the XLSTART folder. At some point in my development I started encountering an issue where opening new workbooks always gives the error "This workbook is currently referenced by another workbook and cannot be closed." Clicking OK just opens the workbook normally and the macro works perfectly, but it's a little annoying so I would rather get rid of it.

Here's a picture of my VBA Project references when I have a workbook open (xlwings has "- referenced" after it because i tried renaming the addin from a different users similar problem and solution but no luck)

All module1 in the personal book is doing is a macro with xlwings' RunPython.

Sub btnInitialize()
    RunPython "import PyCert; PyCert.btnInitialize()"
End Sub

I suspect it's a self referencing issue with personal.xlsb and xlwings? When I remove either the xlwings addin from the XLSTART folder or the reference, the error is gone but RunPython stops working. I ran xlwings addin install to get the .xlsb and manually imported the reference like the xlwings docs say. I've tried renaming the addin in case I was importing it twice, (in XLSTART and tools>references). I've tried the most common answer I found online, unchecking all addins and checking them again but I didn't have any checked in the first place? I've checked the registry in case the addins are opening in the wrong order however I didn't see any OPEN1,OPEN2 keys as another post suggested.

I'm quite new to this side of Excel and VBA and I'm trying to teach myself a few bits, please let me know if I need to post any more info.

Upvotes: 0

Views: 81

Answers (1)

Bulky
Bulky

Reputation: 43

I've found a solution for anyone that might have a similar issue. Trying what I thought I already tried and failed with, I renamed xlwings.xlam and moved it out of the XLSTART folder. Changed the reference to the new location and it works. My thinking was the file being in the folder and being referenced ment it was being called upon twice.

Upvotes: 0

Related Questions