Annoying issue - WindowsApps loads Python install instead of user's installed Python

I have a pretty annoying problem that is a result of corporate policy - i.e. users can't edit their paths. Now I figured out via VBA to alter the USER paths, but there's just one issue - all the installations have, in the SYSTEM environment variables: %USERPROFILE%\AppData\Local\Microsoft\WindowsApps

So what happens is that the PATH of a script session will take the SYSTEM paths and append the USER paths to it. Pretty normal Windows behavior. The issue I run into after setting paths is the ORDER causes issues. WindowsApps has Python as an installation package, so if it comes first in the PATH statement, on some PCs it tries to load the Python install, rather than the user's installed Python (!) Annoying, right? And yes, I am calling Python from it's installation directory, so I'm not sure what exactly the problem is.

Now I have developer rights so I have basically 100% control of my laptop. BUT my end users DO NOT. If there wasn't a company lockdown, I'd just have users remove the WindowsApps from their SYSTEM PATH...

The normal full directory paths are like this:

%USERPROFILE%\Anaconda3\;
%USERPROFILE%\Anaconda3\Library;
%USERPROFILE%\Anaconda3\Library\bin;
%USERPROFILE%\Anaconda3\Scripts\;

The current code is quite simple:

Sub RunPythonScript()

    Dim objShell As Object
    Dim PythonExePath, PythonScript As String

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

      
    Dim WaitOnReturn As Boolean
    Dim WindowStyle As Integer

    WindowStyle = 1
    WaitOnReturn = True

' find the Python Path in the workbook
    PythonPath_Row = ThisWorkbook.Sheets(ActiveSheet.Name).Columns(1).Find(What:="Python Path", LookIn:=xlValues).Row   1
    PythonPath = ThisWorkbook.Sheets(ActiveSheet.Name).Cells(PythonPath_Row, 1)

' set only the User environment settings since no admin permission required
    Set colUserEnvVars = objShell.Environment("User")

' save the original PATH
    originalPATH = colUserEnvVars.Item("Path")

' add the needed Python directories to the PATH
    colUserEnvVars.Item("PATH") = PythonPath & ";" & PythonPath & "Library" & ";" & PythonPath & "Library\bin" & ";" & PythonPath & "Scripts" & ";" & colUserEnvVars.Item("Path")

    PythonScript = """" & ThisWorkbook.Path & "\MyScript.py" & """"

    PythonEXE = PythonPath & "python.exe"

' run Python script
    objShell.Run PythonEXE & " " & PythonScript, WindowStyle, WaitOnReturn

' put path back to normal
    colUserEnvVars.Item("PATH") = originalPATH

End Sub

What doesn't make any sense to me, is that I'm am EXPLICITY CALLING PYTHON FROM IT'S INSTALLATION DIRECTORY! So why are some PCs still opening Python from WindowsApps??? I step through the VBA code and verify the command line passed to the shell is correct. I've opened a CMD prompt after setting the path and it's all correctly modified. I'm just confused as to why this is not working for everyone. If someone understands what the issue could be, please let me know.

CodePudding user response：

Please, try the next function to obtain the installation path of any (installed) application which open a specific extension (as default):

Private Function GetExePath(ext As String) As String
    Dim strAppl As String, strPathExe As String, strExeFile As String
    Dim WSHShell As Object

    Set WSHShell = CreateObject("WScript.Shell")
    On Error Resume Next
    strAppl = WSHShell.RegRead("HKEY_CLASSES_ROOT\" & WSHShell.RegRead("HKEY_CLASSES_ROOT\" & ext & "\") & _
                                                                                  "\shell\open\command\")
    If err.Number <> 0 Then
        err.Clear: On Error GoTo 0
        GetExePath = ""
        MsgBox "No program installed for extension """ & ext & """"
        Exit Function
    End If
    On Error GoTo 0
    strExeFile = Split(strAppl, """ """)(0)
    strExeFile = Right(strExeFile, Len(strExeFile) - 1)
    GetExePath = strExeFile
End Function

You can test it in the next way:

Private Sub testGetExeP()
   Debug.Print GetExePath(".xls")
   Debug.Print GetExePath(".pdf")
   Debug.Print GetExePath(".py")
End Sub

Please, try comparing the path from the used workbook with the returned exe path. Firstly, to check if my supposition (from the above comment) is confirmed and secondly to use the function instead of the path extracted from any workbook.