8

I am calling python from Excel using xlwings. I find that when running my macro, Excel closes and reopens in order to run the code. It functions correctly but it slows things down. In addition, if the Excel file is unsaved a dialog will mention that the file is already open and that I will lose unsaved changes.

Is there a way to call python without reopening the Excel file?

This is my python code (in loaddf.py):

from xlwings import Workbook, Range, Sheet

def my_macro():
    wb = Workbook.caller()
    Range('A1').value = Range('A1').value + 1

And the VBA code in my Excel file:

Sub loaddfsub()
    RunPython ("import loaddf; loaddf.my_macro()")
End Sub

Thanks for the help.

KieranPC
  • 8,525
  • 7
  • 22
  • 25
  • That's obviously not the intended behaviour - are you facing this bug? https://github.com/ZoomerAnalytics/xlwings/issues/10 – Felix Zumstein Jan 07 '15 at 19:10
  • Having same issue. Python 3.4 and Excel 2013 15.0.4675.1001 – Joe McGrath Jan 09 '15 at 05:04
  • @JoeMcGrath Which Python distribution are you using? xlwings version 0.3.0? Excel 32 or 64bit? – Felix Zumstein Jan 11 '15 at 18:04
  • @JoeMcGrath also, can you please send me a sample of the Excel workbook and python code that is showing this behavior? my email is on GitHub: https://github.com/fzumstein – Felix Zumstein Jan 11 '15 at 18:07
  • @FelixZumstein Python version 3.4.0 on Windows downloaded from https://www.python.org a year ago or so. Excel is 32-bit (surprised me I never checked before). Will email file. Thanks for help! – Joe McGrath Jan 12 '15 at 01:48
  • I'm using microsoft office 2007 SP3, 32bit. I have a 32 bit Anacondas distribution of python 2.7. Thanks for the help Joe, will email files – KieranPC Jan 12 '15 at 15:12
  • Same issue Python 3.4.1 from Anaconda 2.1.0 (64-bits), Excel V14.0.7140.5002 (32 bits) – Penbeuz Jan 13 '15 at 14:36
  • Joe and Penbeuz, do you have excel add-ins installed? My company has a number of them. I want to rule that out as a cause. Also do you use a PERSONAL.xls file to access VBA macros in all excel instances? – KieranPC Jan 13 '15 at 22:24
  • @KieranPC No add-in for me, I've got PERSONAL.xlsb but removing it from directory didn't change anything. – Penbeuz Jan 14 '15 at 13:37
  • I think I have found a fix for this - will post instructions on how to test soon. – Felix Zumstein Jan 14 '15 at 13:59

1 Answers1

5

It seems that under certain circumstances, Excel doesn't register an Excel Workbook properly in the RunningObjectTable, a precondition so it can be found via COM. So far I've only noticed this behaviour for Workbooks downloaded from the internet given it opens them in the Protected View mode first (depends on Settings). However, based on the feedback here, it seems that it can also happen under other circumstances, possibly caused by some add-ins or security settings.

I've implemented a fix for this which will be present in v0.3.1, but you can get it right now directly from GitHub. Let me know if you need help there.

Update (16-Jan-2015): xlwings v0.3.1 including this fix has just been released.

Update2 (13-Sept-2015): xlwings v0.4.0 should finally fix this bug in a reliable way.

Felix Zumstein
  • 6,737
  • 1
  • 30
  • 62
  • Thanks. Glad bounty got auto-awarded. Security issue maybe caused by it being on a network mounted drive? – Joe McGrath Jan 19 '15 at 14:49
  • Yes, that would make a lot of sense... does the file open in `Protected view` everytime you open it? – Felix Zumstein Jan 19 '15 at 15:13
  • No. That's why I was confused with your initial suggestion. I updated to your newest version and no issues. Haven't had time to use it, but verified it is functioning correctly. – Joe McGrath Jan 20 '15 at 01:10