Execute Python code on clicking in Excel VBA button-CodePudding

in my company Excel is the primary tool to handle data.

I am facing this situation. People get info in excel about products that has to be translated to other languages. I have a python code that I run in jupyter. But it is not handy to ask users to run python code in jupyter.

Hence I thought of having excel file with button when users in “input” sheet would fill out the table and then would click on the button and python code would be run and get them data into “Output” sheet.

I thought of this as to put in VBA from https://vbaskill.com/tricks/python-script/


Sub RunPythonScript()
'Procedure to run a Python Script in a VBA Procedure using the shell

   'Declaration
   Dim objShell As Object 'For the Shell
   Dim Pythonexe, PythonScript As String
    
   'Create the Shell Object
   Set objShell = VBA.CreateObject("Wscript.Shell")
    
   Pythonexe = """C:\\...\Python\...\python.exe """ 'path of the python.exe
   PythonScript = "C:\\...\VBAPython.py" 'path of your Python script
    
   'Run your Python script
   objShell.Run Pythonexe & PythonScript 'run takes two arguments

   'free variables for memory
    Set objShell = Nothing
    
End Sub

As I want to load data as dataframe and manipulate them then as dataframe, I thought of adding this from https://www.soudegesu.com/en/post/python/pandas-with-openpyxl/#convert-openpyxl-object-to-dataframe


from openpyxl import load_workbook
 import pandas as pd
 
 # Load workbook
 wb = load_workbook('Translation.xlsm')
# load sheet
ws = wb['Input']
 
 # Convert to DataFrame
df = pd.DataFrame(ws.values)

Do you think it will work? Or how shall I do it to make it workb Thanks

CodePudding user response：

Your code should work, I have just tried it myself with no errors. Of course, you will need to return the data later on to see the results!

Alternatively, you could use xlwings. This package allows a lot of interaction between Python and Excel. This is all you would need to write in VBA to run a script (taken from the xlwings documentation RunPython section):

Sub HelloWorld()
    RunPython "import hello; hello.world()"
End Sub

And this is the example code in the hello.py file:

# hello.py
import numpy as np
import xlwings as xw
def world():
    wb = xw.Book.caller()
    wb.sheets[0].range('A1').value = 'Hello World!'