Running Python script from VBA, checking to see if 'RetVal = Shell' command is working

Question

Original Question

When I go into C:\Users\HP\Documents\Python Scripts and click EbayWebScraper.py the script runs. It takes about 30 seconds but the output is correct.

I want to run this script from within Excel / VBA. I tried using the code 'RetVal = Shell' and writing it in 6 different ways. After the 6 failed attempts, I turned the code into comments.

Sub Python()

'RetVal = Shell("python.exe C:\Users\HP\Documents\Python Scripts\EbayWebScraper.py", vbNormalFocus)


'RetVal = Shell("C:\Users\HP\AppData\Local\Programs\Python\Python37-32\python.exe " & "C:\Users\HP\Documents\Python Scripts\EbayWebScraper.py")

'RetVal = Shell("C:\Users\HP\AppData\Local\Programs\Python\Python37-32\python.exe C:\Users\HP\Documents\Python Scripts\EbayWebScraper.py")

'RetVal = Shell("C:\Users\HP\AppData\Local\Programs\Python\Python37-32\python.exe C:\Users\HP\Documents\Python Scripts\EbayWebScraper.py", vbNormalFocus)


'RetVal = Shell("python.exe" & "C:\Users\HP\Documents\Python Scripts\EbayWebScraper.py", vbNormalFocus)

'RetVal = Shell("python.exe" & "C:\Users\HP\Documents\Python Scripts\EbayWebScraper.py")


End Sub

Do I need to slow the VBA macro down somehow? Is it a case of the VBA macro working too quickly and not giving the Python script enough time?

I read this VBA Macro running too fast and experimented with

Application Wait (Now + TimeValue (0:00:10")).

Unfortunately the experiment did not seem to work.

I use Loom to record the screen and then I play back the recording and have a better look at any error messages that appear in CMD. Unfortunately to the best of my knowledge every error message I have occurred, I have now fixed.

Update 1

I don't know if I am being really stupid. To the best of my knowledge I have provided the right file directory. The link 'Windows Explorer File Directory' is for a screenshot I took.

Microsoft Windows [Version 10.0.17134.165]
(c) 2018 Microsoft Corporation. All rights reserved.

C:\Users\HP>python C:\Users\HP\Documents\Python Scripts\EbayWebScraper.py
python: can't open file 'C:\Users\HP\Documents\Python': [Errno 2] No such file or directory
>

Windows Explorer File Directory

Update 2

This also failed.

Microsoft Windows [Version 10.0.17134.165]
(c) 2018 Microsoft Corporation. All rights reserved.

C:\Users\HP>Documents\Python Scripts\EbayWebScraper.py
'Documents\Python' is not recognized as an internal or external command,
operable program or batch file.

Update 3

When I ran this code (which I got from Wait for Shell to finish, then format cells - synchronously execute a command) -

Sub Python2()

Dim wsh As Object
Set wsh = VBA.CreateObject("WScript.Shell")
Dim waitOnReturn As Boolean: waitOnReturn = True
Dim windowStyle As Integer: windowStyle = 1
Dim errorCode As Long

errorCode = wsh.Run("C:\Users\HP\Documents\Python Scripts\EbayWebScraper.py", windowStyle, waitOnReturn)

If errorCode = 0 Then
    MsgBox "Done! No error to report."
Else
    MsgBox "Program exited with error code " & errorCode & "."
End If

End Sub

I got the error -

Run-time error '-2147024894 (80070002)': Method 'Run' of object 'IWshShell3' failed

Update 4

I am looking at Error running Shell object / commands through Excel VBA.

Update 5

This code finally worked (I got the code from the link in Update 4).

    Sub Python3()

Dim wsh As Object
Set wsh = VBA.CreateObject("WScript.Shell")
Dim waitOnReturn As Boolean: waitOnReturn = True
Dim windowStyle As Integer: windowStyle = 1
Dim errorCode As Long

errorCode = wsh.Run(Chr(34) & "C:\Users\HP\Documents\Python Scripts" & "\EbayWebScraper.py" & Chr(34))

If errorCode = 0 Then
    MsgBox "Done! No error to report."
Else
    MsgBox "Program exited with error code " & errorCode & "."
End If

End Sub