excel - Permission error for running Python on the back of VBA

Question

I am trying to create a simple excel file with a button to populate the cells from an excel file. The issue I am having is that if I have my excel file open, python can't open the excel due to permission error (since the file is already open). I know a simple way is to create variables in VBA and let python do all the calculations and then pass it back to VBA. And then VBA will populate the cells. That's not what I am trying to do. I have seen people online to be able to keep excel open while run python and populate simultaneously. What am I missing? Is there some way to give permission to the python when opening from the VBA?

Integrating the Python to VBA seems to be fairly simple. I made a module in VBA using this code:

Sub RunPythonScript()

Dim objShell As Object
Dim PythonExePath, PythonScriptPath As String
    Set objShell = VBA.CreateObject("Wscript.Shell")
    
    PythonExePath = """C:Program Files (x86)Microsoft Visual StudioSharedPython37_64python.exe"""
    PythonScriptPath = "E:My DocumentsFinanceEconomyexcel2.py"
    
    objShell.Run PythonExePath & PythonScriptPath
    
End Sub 

And for python:

from openpyxl import load_workbook
wb = load_workbook('Python_Button.xlsm')
ws = wb["Sheet1"]

for i in range (1,21):
    currentCell = "A" + str(i)
    ws[currentCell] = i
wb.save('Python_Button.xlsm')
wb.close()

question from:https://stackoverflow.com/questions/65907013/permission-error-for-running-python-on-the-back-of-vba

Answer 1

So this would be one way to do it:

Option Explicit
    
Sub RunPythonScript() 
 Dim objShell As Object 
 Dim PythonExe, PythonScript As String

 Set objShell = VBA.CreateObject("Wscript.Shell")
 PythonExe = """C:Program Files (x86)Microsoft Visual 
 StudioSharedPython37_64python.exe"""
 PythonScript = """E:My DocumentsFinanceEconomyexcel6.py"""
 objShell.Run PythonExe & PythonScript
End Sub

And on Python

import xlwings as xw
book.sheets["Sheet1"].range('A1').value = 100

Now one can add a button in the excel sheet and call the python script to populate the excel.