CODEWITHSUNDEEP
pythonexcel
XYZ123
XYZ123

Reputation: 69

Running Python script directly from Excel

I have a Python script I want to run from Microsoft Excel.
However, the script currently writes data to that excel file when I run it from Python.
If I keep the excel file open when running from Python I get a permission denied error which I can fix by closing the excel file.

Will running the python script from within the excel file still allow it to write to it?

Upvotes: 1

Views: 5493

Answers (3)

M. Hoffman
M. Hoffman

Reputation: 66

Use a "separate" py script to read/write Excel:

openpyxl is a Python library to read/write Excel 2010 xlsx/xlsm/xltx/xltm files.

https://pypi.org/project/openpyxl/

If you want to run Py against your sheet from w/in that sheet, see this tutorial for 1 way to do so:

https://pythonandvba.com/blog/how-to-execute-a-python-script-from-excel-using-vba/

Upvotes: 1

DS_London
DS_London

Reputation: 4271

Here is one (admittedly clunky) solution for handling a DataFrame output, as a proof of concept. The code takes a Python script written in the spreadsheet, writes it to "c:\temp\script.py", executes it in a shell and extracts the stdout output to a range on the same sheet.

In a VBA module in the Worksheet:

NB.Include reference to 'Windows Script Host Object Model'

Requires:

The spreadsheet Names 'Script' and 'Output' are defined;

%PYTHONPATH% environment variable is set to the python.exe folder.

Option Explicit

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As LongPtr)

Function processResponse(sh As WshExec) As Variant
    processResponse = CVErr(xlErrNA)

    If sh.Status = WshFailed Then Exit Function
    
    If Not sh.StdErr.AtEndOfStream Then
        processResponse = sh.StdErr.ReadAll()
        Exit Function
    End If

    Dim vRet As Variant
    Dim vLine As Variant
    
    Dim strLine As String
    Dim nRows As Integer
    nRows = 0
    Dim nCols As Integer
    nCols = 0
    
    Dim res As New Collection
    
    While Not sh.StdOut.AtEndOfStream
        strLine = sh.StdOut.ReadLine()
        vLine = Split(strLine, ",")
        res.Add vLine
        If UBound(vLine) + 1 > nCols Then
            nCols = UBound(vLine) + 1
        End If
        nRows = nRows + 1
    Wend
    
    ReDim vRet(1 To nRows, 1 To nCols) As Variant
    
    Dim nRow As Integer
    nRow = 1
    Dim nCol As Integer
    
    Dim r As Variant
    For Each r In res
        For nCol = LBound(r) To UBound(r)
            vRet(nRow, nCol + 1) = r(nCol)
        Next nCol
        
        nRow = nRow + 1
    Next r
    
    processResponse = vRet
End Function

Sub RunScript()
    Dim rngScript As Range
    Set rngScript = Range("Script")
    
    Dim cl As Range
    
    Open "c:\temp\script.py" For Output As #1
    For Each cl In rngScript.Cells
        Print #1, cl.Value
    Next cl
    Print #1, ""
    Close #1
    
    Dim rngOutput As Range
    Set rngOutput = Range("Output")
    
    Dim sh As WshShell
    Set sh = CreateObject("WScript.Shell")

    Dim strPython As String
    strPython = Environ("PYTHONPATH")
    
    Dim strCommand As String
    strCommand = strPython & "\python.exe c:\temp\script.py"
    
    Dim shellExec As WshExec
    Set shellExec = sh.Exec(strCommand)
    
    Do While shellExec.Status = WshRunning
        Sleep 100
    Loop

    Dim vRes As Variant
    vRes = processResponse(shellExec)
    If Not IsArray(vRes) Then
        rngOutput.Value = vRes
        Exit Sub
    End If
    
    Set rngOutput = rngOutput.Resize(UBound(vRes, 1), UBound(vRes, 2))
    rngOutput.Value = vRes
End Sub

The spreadsheet with the script and result:

enter image description here

Clearly you don't need to have the script sitting in the Excel sheet (this is just a demo): you can amend the code to pick up an existing script file. I'm using a CSV output from the script, which can be decoded by VBA to fill a Range with tabular values. Obviously if your output is strings containing ',' then this will cause problems!

Upvotes: 4

Dominique
Dominique

Reputation: 17565

I have the impression that you have written a program to modify an Excel file. In order to do that, that Excel file can't be accessed by some application (like Excel).

When you open that file in Excel, and you try to run that program, then that program tries to open the file you have just opened, returning an "access denied" error.

So, I believe there are two things you can do:

  • Run that program from outside Excel.
  • Run that program from Excel itself, but without opening that file in Excel.

Upvotes: 2

Related Questions