Run python from excel

Now run Python from Excel-VBA using xlwings and try working with Excel files on python.

There may be talk that VBA is okay, but I don't want to write VBA, so it can't be helped.

For how to use xlwings, refer to Try operating Excel using Python (Xlwings).

By the way, the environment is Mac, but xlwings works on Windows.

Official documentation

VBA: Calling Python from Excel

procedure

Overview

• Prepare the python script you want to run
• Register a macro that calls python
• Import xlswings vba module into excle file
• Run

Prepare the python script you want to run

Anything is fine, but I will prepare a script that sets the appropriate value in the Excel file.

from xlwings import Workbook, Range

def myfunction():
    wb = Workbook.caller()
    Range('A1').value = "Call Python!"

Register a macro that calls python

Create a new Excel file, open the editor with Alt + F11, and register the macro

Sub SampleCall()
    RunPython ("import myproject;myproject.myfunction()")
End Sub

Myproject here is the python script name.

The function that myfunction wants to execute.

Import xlswings vba module into excel file

You need to import a VBA module called xlwings.bas into a file.

xlwings.bas is in the installed xlwigns directory.

If you want to find out the path, you can easily find it in the Python Console

import xlwings
xlwings.__path__

Run

Register the created macro SampleCall () in an appropriate object and execute it to complete.

If you get an error

For Mac, the xlswings vba module seems to read .bash_profile and set the python path.

If you are using bash, there will be no problem because it will be set, but if you are using zsh like yourself, you will get the following error because .bash_profile is not prepared.

importError: No module named xlwings

Create bash_profile or rewrite xlwings.bas to load zshrc. To rewrite, just rewrite the .bash_profile below to .zshrc.

    'Check if .bash_profile is existing and source it
    Res = system("source ~/.bash_profile")
    If Res = 0 Then
        Res = system("source ~/.bash_profile;" & RunCommand & """" & WORKBOOK_FULLNAME & """ ""from_xl""" & " " & Chr(34) & ToPosixPath(Application.Path) & "/" & Application.Name & Chr(34) & ">" & Chr(34) & LOG_FILE & Chr(34) & " 2>&1 &")
    Else
        Res = system(RunCommand & """" & WORKBOOK_FULLNAME & """ ""from_xl""" & " " & Chr(34) & ToPosixPath(Application.Path) & "/" & Application.Name & Chr(34) & ">" & Chr(34) & LOG_FILE & Chr(34) & " 2>&1 &")
    End If

Tips

If it is troublesome to import xlwings.bas one by one If you execute the following command in the console, it will generate an imported Excel file and a python script called.

xlwings quickstart myproject