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

environment version
window 7
python 3.4.3 (Anaconda 2.3.0)
pyinstaller 3.2

Method

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 ... (´ ・ ω ・ `)

Recommended Posts

De-VBA Excel tool! Eliminate VBA tool with Python in environment where Python cannot be installed
Video cannot be loaded with Spyder in Python development environment
Jupyter Notebook 6.0.2 cannot be installed in the Python 2.7 environment created in Anaconda
Japanese can be used with Python in Docker environment
TensorFlow 2.2 can't be installed with Python 3.8!
Django cannot be installed in the development environment of pipenv + pyenv
Get a list of packages installed in your current environment with python
Draw Nozomi Sasaki in Excel with python
Python modules with "-(hyphen)" cannot be removed
Introduced sip-4.14 in python3.2.2 environment with MacOS 10.7.4
I installed Pygame with Python 3.5.1 in the environment of pyenv on OS X
Create a virtual environment with conda in Python
Python environment settings (virtualenv + pip cannot be done)
Read Excel name / cell range with Python VBA
Investigation when import cannot be done with python
Work in a virtual environment with Python virtualenv.
Use jupyter-lab installed in python virtual environment (venv)
Matplotlib etc. cannot be imported in VirtualEnv environment
PIL cannot be installed by virtualenv with pyenv
Excel with Python
Run Python from Excel VBA with xlwings & tutorial supplement
Import libraries that cannot be pip installed with PyCharm
Use Python installed with pyenv for PL / Python execution environment
list comprehension because operator.methodcaller cannot be used in python 2.5
Operators ++,-cannot be used in python (difference from php)
When import matplotlib.pyplot cannot be done in pyenv-virtualenv environment
[Redash] Standard library cannot be used in python function
Excel sheet cannot be specified in sheet_name of pd.read_excel
[RHEL7 / CentOS7] LWP execution error in the environment where Perl is installed with the yum command
Run Python with VBA
Handle Excel with python
virtual environment in python
Python environment with docker-compose
Python programming in Excel
Development environment in Python
Virtual environment with Python 3.6
Operate Excel with Python (1)
Operate Excel with Python (2)
Solution when the image cannot be displayed with tkinter [python]
Update packages already installed with pip in user environment (non-su)
[SAP CP] Web API created with python in CF environment
Introduction to Python for VBA users-Calling Python from Excel with xlwings-
[CLPEX memo] Run DO Python API in COS installed environment
Install CaboCha in Ubuntu environment and call it with Python.