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