0

I'm running Python scrpit from VBA. The result should be a filled cell in Exel file. I tried using this VBA command:

RetVal = Shell("""C:\Python27\python.exe""" & " " & """M:\Analysis\PythonScripts\MySript.py""", vbHide)

But nothing happens as the result. However, if I run this scipt from python.exe directly, it works fine. The cell in Excel file is filled with a value. Which brings me to conclusion that something is wrong with VBA code.

Update

Fragment of Python Script:

ExcelApp = win32com.client.GetActiveObject("Excel.Application")
wb = ExcelApp.Workbooks('File1.xlsm')
sheet = wb.Worksheets(u'Result')
cell = sheet.Range('B1')
cell.Value = result
anna_vanna
  • 3
  • 4
  • 1
    What does that mean *"The cell in Excel file is filled with a value."*? If you don't get an arror in VBA this looks like the issue is in the python script. Please give the code of the python script too and explain what you would expect to happen. – Pᴇʜ May 18 '20 at 08:45
  • We can't help if you don't post the python code – Chadee Fouad May 18 '20 at 08:47
  • @ShadyMBA I added fragment of python code – anna_vanna May 18 '20 at 08:50
  • Are your running the VBA from File1.xlsm? – Chadee Fouad May 18 '20 at 08:55
  • @ShadyMBA yes, I am – anna_vanna May 18 '20 at 08:56
  • What's with the `u` in `wb.Worksheets(u'Result')`? Just a typo? Please clarify. • Also is the file `File1.xlsm` the one the VBA macro is in? If yes this does not work. You cannot open the file in python if it is currently open in Excel. • Actually I hope this python is only an example file, because it does not make any sense at all to use python to write a value into an Excel file if you can do the same from VBA directly. – Pᴇʜ May 18 '20 at 09:00
  • @Pᴇʜ Yes, it's a typo. • You are right, I'm launching VBA in File1.xlsm. • The reason I'm using python to write a value is that this value is a result of a python program. I need to write this value in Excel by interacting with Excel only (by running VBA). I gues I should try reading result of python program with VBA and then writing it to file. – anna_vanna May 18 '20 at 09:21
  • @anna_vanna Yes that would be the way to go. Let python return just the value you need, read that with VBA and let VBA write the value into the cell. This way you would have much more control. Check out this: [Return result from Python to Vba](https://stackoverflow.com/questions/39516875/return-result-from-python-to-vba) – Pᴇʜ May 18 '20 at 09:58

1 Answers1

0

I think that if you're running VBA from File1.xlsm this causes conflict between VBA and Python..like file sharing violation/permission denied because now VBA AND Python are trying to control the file at the same time. Try running the VBA from another file that is NOT the target file and let's see how that goes.

Chadee Fouad
  • 2,630
  • 2
  • 23
  • 29
  • I've tried launching from another Excel file, it also doesn't work. – anna_vanna May 18 '20 at 09:04
  • I tried your exact python script and I'm getting "pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2147352565), None)"...in this line: ExcelApp = win32com.client.GetActiveObject("Excel.Application") Are you sure the Python script is running correctly? What kind of error message are you getting – Chadee Fouad May 18 '20 at 09:24
  • I get no error message at all. When I run program, value apperas in opened File1 in cell B1, as it 's supposed. – anna_vanna May 18 '20 at 09:27
  • According to this [link](https://stackoverflow.com/questions/28879391/editing-an-open-document-with-python), you can't edit an open excel file. – Chadee Fouad May 18 '20 at 09:35
  • @anna_vanna you're welcome. If what I said was correct/helpful then I'll appreciate accepting my answer or up-voting it. Thanks :-) – Chadee Fouad May 18 '20 at 19:56