
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.