Execute Python from Excel

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.