
Python is great, Excel is great. How cool would it be to run Python from an Excel workbook?
“But that’s stupid,” you protest.
… Yes, and?
Copy and paste this into a VBA module in your Excel workbook, modify the parameters, create a helloworld.py
in the same directory you’ve saved the Excel file, and enjoy.
Sub RunPython()
''' Run Python in Excel. '''
If ThisWorkbook.Path = "" Then
MsgBox "Please save this file first"
Stop
End If
'--------------------------------------------------------------------------
' Parameters
'--------------------------------------------------------------------------
' Name of Python file. Must be in same dir as Excel file
Dim pyFile As String: pyFile = "helloworld.py"
' How you call python in cmd
Dim pyExe As String: pyExe = "python"
' Sheet name where you want to write results
Dim outputSheet As String: outputSheet = "Sheet1"
'--------------------------------------------------------------------------
' NOTE: Nothing past this point should need to be changed by the user,
' unless you are working on a network drive and already have A: mapped.
'--------------------------------------------------------------------------
' Declarations
'--------------------------------------------------------------------------
Dim wbI As Workbook
Dim wsI As Worksheet
Dim tempDir As String: tempDir = _
IIf(Environ$("tmp") <> "", Environ$("tmp"), Environ$("temp"))
Dim batFile As String: batFile = tempDir & "\runpy.bat"
Dim pyConverterFile As String: pyConverterFile = "pyinexcel.py"
Dim wsh As Object
Set wsh = VBA.CreateObject("WScript.Shell")
Dim waitOnReturn As Boolean: waitOnReturn = True
Dim windowStyle As Integer: windowStyle = 1
Dim pyOutput As String: pyOutput = tempDir & "\1jb20gb2eog0.txt"
'--------------------------------------------------------------------------
' Create and run .bat and .py files
'--------------------------------------------------------------------------
ChDir ThisWorkbook.Path
Call WriteBat(pyFile, pyExe, outputSheet, batFile, pyConverterFile)
Call WritePyConverter(pyConverterFile)
wsh.Run (Chr(34) & batFile & Chr(34)), windowStyle, waitOnReturn
'--------------------------------------------------------------------------
' Output
'--------------------------------------------------------------------------
Set wbI = ThisWorkbook
Set wsI = ThisWorkbook.Sheets(outputSheet)
Set wbO = Workbooks.Open(pyOutput)
wsI.UsedRange.ClearContents
wbO.Sheets(1).Cells.Copy wsI.Cells
wbO.Close SaveChanges:=False
'--------------------------------------------------------------------------
' Delete temporary files
'--------------------------------------------------------------------------
Call DelFile(batFile)
Call DelFile(pyConverterFile)
Call DelFile(pyOutput)
End Sub
Private Sub WritePyConverter(pyConverterFile As String)
'''Write the file that makes the magic happen.'''
Call DelFile(pyConverterFile)
Dim pyLines(1 To 11) As String
pyLines(1) = "import logging"
pyLines(2) = "import sys"
pyLines(3) = "import os"
pyLines(4) = "try:"
pyLines(5) = " tmpdir = os.environ['TMP']"
pyLines(6) = "except:"
pyLines(7) = " tmpdir = os.environ['TEMP']"
pyLines(8) = "logfile = os.path.join(tmpdir, '1jb20gb2eog0.txt')"
pyLines(9) = "sys.stdout = open(logfile, 'w+')"
pyLines(10) = "logging.basicConfig(stream=sys.stdout, level=logging.INFO)"
pyLines(11) = "exec(open(sys.argv[1]).read()) # I know, I know..."
Open pyConverterFile For Output As #1
For i = 1 To 11
Print #1, pyLines(i)
Next i
Close #1
End Sub
Private Sub WriteBat(pyFile As String, pyExe As String, _
outputSheet As String, batFile As String, _
pyConverterFile As String)
''' Write batch file that runs our python. '''
Call DelFile(batFile)
Dim batLines(1 To 4) As String
If Mid(ThisWorkbook.Path, 2, 1) = ":" Then
batLines(1) = Left(ThisWorkbook.Path, 2) & " || exit"
batLines(2) = _
"cd " & Chr(34) & ThisWorkbook.Path & Chr(34) & " || exit"
batLines(3) = pyExe & " pyinexcel.py " & Chr(34) _
& pyFile & Chr(34)
Open batFile For Output As #1
For i = 1 To 3
Print #1, batLines(i)
Next i
Close #1
Else
batLines(1) = _
"net use a: " & ThiWorkbook.Path & ", persistent: no || exit"
batLines(2) = "a:"
batLines(3) = pyExe & " " & pyConverterFile & " " & Chr(34) _
& pyFile & Chr(34)
batLines(4) = "net use a: /delete"
Open batFile For Output As #1
For i = 1 To 4
Print #1, batLines(i)
Next i
Close #1
End If
End Sub
Private Sub DelFile(fN As String)
''' Delete a file after checking first that it exists. '''
If Dir(fN) <> "" Then
SetAttr fN, vbNormal
Kill fN
End If
End Sub
UPDATE [June 6, 2019]: My friends are concerned about my sanity.

UPDATE [December 6, 2019]: Someone named Sidinei tried running this, and they found an issue with my code. I have not been able to test out his error, but I believe the issue was here: pyLines(9) = "sys.stdout = open(logfile, 'w')"
. It should not be 'w'
, it should be 'w+'
. The +
makes sure that the file is written if it did not previously exist.
You must be logged in to post a comment.