[FIXED] Why isn't Jupyter notebook running from VBA?

Issue

I'm trying to run a Jupyter notebook from Access VBA:

Sub import_hawk()

Dim objShell As Object
Dim PythonExe, PythonScript As String

    Set objShell = VBA.CreateObject("Wscript.Shell")

    PythonExe = """C:\Users\Philip\.conda\envs\latest\python.exe"""
    PythonScript = "C:\Users\Philip\OneDrive\Betting\Capra\Tennis\polgara\polgara.ipynb"

    objShell.run PythonExe & PythonScript

End Sub

When I run it then a box does briefly appear and then disappears. I've run the notebook manually and it works fine. The most frustrating thing is that it was working for about 5 mins and now isn't...

Solution

Figured it out. I have put square brackets where other people would need to put their specific info:

Sub execute_notebook()

Dim objShell As Object
Dim new_cmd_window              As String
Dim full_script                 As String
Dim activate_env                As String
Dim change_dir_script           As String
Dim convert_and_run_nb          As String

Set objShell = VBA.CreateObject("Wscript.Shell")

new_cmd_window = "cmd /c"
activate_env = "cd /d [path to Anaconda\Scripts folder which on my machine is C:\ProgramData\Anaconda3\Scripts] & activate [environment_name] &"
change_dir_notebook = "cd /d [path to folder where notebook is] &"
convert_and_run_nb = "jupyter nbconvert --to notebook --execute [notebook name].ipynb"
full_script = new_cmd_window & " " & Chr(34) & activate_env & " " & change_dir_script & " " & convert_and_run_nb & Chr(34)

objShell.run full_script

End Sub

Couple of notes on my findings:

1. Didn't work without new_cmd_window
2. For some reason simply using activate [environment name] wouldn't work from VBA. Was fine when I typed it into the cmd line. In the end I had to manually change the directory and run it that way.

Answered By - Jossy