Shell does not run python script on being called from VBA sub

Question

I am trying to run python script from VBA using shell. below is part of the sub and also the python codes. Program goes to the VBA function - ShellRunPython but it does not run python script. It does not execute the print in the python last statement.

My second issue is that assuming the python script is run successfully later, I need to get back value of PDFtext to variable str1 in sub. Thank you for suggestions

Sub RunPythonFromVBA()
    '............
    '............

     'Specify File Path
        sfilepath = Application.ThisWorkbook.Path & "\"
           
       pythonfilename = "CDAvsFAscript.py"
       xFilepath = Application.ThisWorkbook.Path & "\"
       xFilepath = Replace(xFilepath, "\", "\\")
        
        'Check for back slash
        If Right(sfilepath, 1) <> "\" Then
            sfilepath = sfilepath & "\"
        End If
            
        sFilename = Dir(sfilepath & "*.pdf")
        
        Do While Len(sFilename) > 0
            If Right(sFilename, 3) = "pdf" Then
                'Display file name in immediate window
        If sfilepath & "\" & sFilename Like "*052835022*" Then        

        xFilepath = xFilepath & sFilename
        ShellArg = "python " & sfilepath & pythonfilename & "" & " " & xFilepath
            Debug.Print ShellArg
            str1 = ShellRunPython(ShellArg)
        '................................
        '................................
        '................................
End Sub

    Public Function ShellRunPython(sCmd As String) As String
    
        'Run a shell command, returning the output as a string
    
        Dim oShell As Object
        Set oShell = CreateObject("WScript.Shell")
    
        'run command
        Dim oExec As Object
        Dim oOutput As Object
        Set oExec = oShell.Exec(sCmd)
        Set oOutput = oExec.StdOut
    
        'handle the results as they are written to and read from the StdOut object
        Dim s As String
        Dim sLine As String
        While Not oOutput.AtEndOfStream
            sLine = oOutput.ReadLine
            If sLine <> "" Then s = s & sLine & vbCrLf
        Wend
    
        ShellRunPython = s
    
    End Function

..................... ..................... Python script

import io

from pdfminer.pdfinterp import PDFResourceManager, PDFPageInterpreter
from pdfminer.converter import TextConverter
from pdfminer.layout import LAParams
from pdfminer.pdfpage import PDFPage


def convert_pdf_to_txt(path):

    rsrcmgr = PDFResourceManager()
    codec = 'utf-8'
    laparams = LAParams()

    with io.StringIO() as retstr:
        with TextConverter(rsrcmgr, retstr, codec=codec,
                           laparams=laparams) as device:
            with open(path, 'rb') as fp:
                interpreter = PDFPageInterpreter(rsrcmgr, device)
                password = ""
                maxpages = 0
                caching = True
                pagenos = set()

                for page in PDFPage.get_pages(fp,
                                              pagenos,
                                              maxpages=maxpages,
                                              password=password,
                                              caching=caching,
                                              check_extractable=True):
                    interpreter.process_page(page)

                return retstr.getvalue()


if __name__ == "__CDAvsFA__":
    #Filepath = 'C:\\passport all paged scan\\testPDF.pdf'
    PDFtext = convert_pdf_to_txt(xFilepath)
    print(PDFtext)

@ShriIyer: Removed the shell tag from your question, because it is not about POSIX shell. If you want to invoke Python via, i.e., powershell, feel free to add shell again to your tags, but then also add the specific tag for powershell (or cmd or whatever you are using). — user1934428
– user1934428, Commented Sep 7, 2021 at 7:34
@TimWilliams The variable xFilepath contains different value - xFilepath = Replace(xFilepath, "\", "\\") — Shri
– Shri, Commented Sep 7, 2021 at 8:14
xFilepath doesn't seem to be a variable that exists in your python script? How are you getting the value for that? — Tim Williams
– Tim Williams, Commented Sep 7, 2021 at 15:07
See tutorialspoint.com/python/python_command_line_arguments.htm and as an example stackoverflow.com/questions/63458304/… — Tim Williams
– Tim Williams, Commented Sep 8, 2021 at 15:28

Tim Williams · Accepted Answer · 2021-09-09 17:05:47Z

2

This worked for me:

Sub RunPythonFromVBA()
    
    Dim sfilepath, scriptPath, sFilename, str1, ShellArg As String
    
    sfilepath = Application.ThisWorkbook.Path & "\" 'Specify File Path
    
    scriptPath = sfilepath & "CDAvsFAscript.py"
    
    sFilename = Dir(sfilepath & "*052835022*.pdf")
    
    Do While Len(sFilename) > 0
        ShellArg = "C:\Python\Python39-32\python.exe """ & scriptPath & """ """ & sfilepath & sFilename & """"
        Debug.Print "Ran this: " & ShellArg
        
        str1 = ShellReturnOutput(ShellArg)
        Debug.Print "Got this: " & str1
        
        sFilename = Dir()
    Loop

End Sub

'Run a shell command, returning the output (or error) as a string
Public Function ShellReturnOutput(sCmd As String) As String
    Dim std, serr
    With CreateObject("WScript.Shell").Exec(sCmd)
        std = .Stdout.readall()
        serr = .Stderr.readall()
    End With
    ShellReturnOutput = IIf(serr <> "", serr, std)
End Function

With a very simple python script:

import sys

def convert_pdf_to_txt(path):

    return 'You passed in: '+path


Filepath = sys.argv[1]
PDFtext = convert_pdf_to_txt(Filepath)
print(PDFtext)

answered Sep 9, 2021 at 17:05

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shri Over a year ago

Thanks Tim It worked for me. I was able to remove all errors and got my job done. Thanks so much

Collectives™ on Stack Overflow

Shell does not run python script on being called from VBA sub

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related