Using Shell and variable path to open exe

[dbill74]

I am at a loss for what I am doing wrong.
The objective with this routine is to open/run a simple .exe located in the same folder as the workbook, presumably in a subfolder of a user's documents folder. User name and subfolder name will vary.
In the code below, I get the first 2 Call Shell line to work. But the third gives me "Run-time error '53': File not found"

I really need this third line to work.

One option I have is to have the exe in a predefined folder such as c:\program files\RO or in an appdata folder.
I don't want this to simplify distribution, keeping the workbook and ROInd2CSV.exe together in a single easy to 'install' package.

Application.ActiveWorkbook.path yields a value of "https://d.doc.live.net/#########/Documents/RRO"

Is OneDrive somehow messing with this, keeping the file from being found?

Code: Select all

Sub OpenROInd2CSV()
' Open ROInd2CSV.exe
    Dim folderPath As String
        
    folderPath = Application.ActiveWorkbook.path
    MsgBox folderPath
    
   'Call Shell(Environ("UserProfile") & "\OneDrive\Documents\RRO\ROInd2CSV.exe", vbNormalFocus)
   'Call Shell("C:\Users\[USER NAME]\OneDrive\Documents\RRO\ROInd2CSV.exe", vbNormalFocus)
   Call Shell(Application.ActiveWorkbook.path & "\ROInd2CSV.exe", vbNormalFocus)
   
End Sub

[HansV]

See Excel's fullname property with OneDrive for a way to convert OneDrive paths to the local equivalent.

You could use:

Code: Select all

Public Function Local_Name(theName As String) As String
    Dim i               As Integer
    Dim objShell        As Object
    Dim UserProfilePath As String

    ' Check if it looks like a OneDrive location.
    If InStr(1, theName, "https://", vbTextCompare) > 0 Then

        ' Replace forward slashes with back slashes.
        Local_Name = Replace(theName, "/", "\")

        'Get environment path using vbscript.
        Set objShell = CreateObject("WScript.Shell")
        UserProfilePath = objShell.ExpandEnvironmentStrings("%UserProfile%")

        ' Trim OneDrive designators.
        For i = 1 To 4
            Local_Name = Mid(Local_Name, InStr(Local_Name, "\") + 1)
        Next i

        ' Construct the name.
        Local_Name = UserProfilePath & "\OneDrive\" & Local_Name
    Else
        ' (must already be local).
        Local_Name = theName
    End If
End Function

Sub OpenROInd2CSV()
    ' Open ROInd2CSV.exe
    Dim folderPath As String

    folderPath = ActiveWorkbook.Path
    If Right(folderPath, 1) <> "\" Then
        folderPath = folderPath & "\"
    End If

   Call Shell(Local_Name(folderPath) & "\ROInd2CSV.exe", vbNormalFocus)
End Sub