De-VBA Excel tool! Eliminate VBA tool with Python in environment where Python cannot be installed

I can't install Python, but Python !?

I don't know what you're talking about, but I don't know ... it's a story that anyone who understands can understand.

Excel tools by VBA are still actively used in some industries.

After all, the presence of Excel in user work is overwhelming, Therefore absolutely ExcelVBA becomes necessary.

So, I know in my head that it's best to write VBA normally,

But after all VBA is not accepted by the body,

I want to make a tool in a more popular (?) Language such as Ruby or Python!

So, the story of trying to destroy the Excel VBA tool with Python.

However, it is difficult to install Python on business terminals in a certain industry. (Of course, it may be possible after adjustment)

Therefore, even in an environment where Python cannot be installed, an Excel tool made based on Python is used. Let's write a procedure to do it.

before that

This post is specifically for tools that use xlwings to mess with Excel in Python.

If you don't know about xlwings, please read the following posts in advance.

Try working with Excel using Python (Xlwings) Run Python from Excel

Environment for creating tools

• The environment for executing the created tool is also windows7.

Method

• Make the python script you want to execute from Excel into an exe
• Execute the exe created from Excel using xlwings to execute the actual processing

Preparation

Install the package pyinstaller to exe

conda install pyinstaller

pyinstaller -v

procedure

Convert the created python script to exe

pyinstaller tool.py --onefile

Define a macro to execute the created exe file from Excel

Sub SampleCall()
    RunFrozenPython ("tool.exe")
End Sub

Image to replace the following code with the above code when executing the previously written python script

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

However, when calling exe, it seems that you can not specify the function you want to execute, so When the script is executed on the python script side You need to make sure that the function you want to call is executed.

the end

Now, if you execute the registered macro, the python script that has been converted to an exe will be executed, and various processes will be executed.

With this, get the data from the DB, process it, paste the value on Excel, I started to use Python to create forms without writing VBA at all \ (^ o ^) /

Impressions

Well, if it's about such a detour, I think it's a story to write VBA quietly. I can make a tool properly with this, but if I make a script using xlwings into an exe That alone makes it a tool of about 130MB (bitter smile)

If you use it in this business ... (´ ・ ω ・ ｀)