ToolPath

Returns the path of ThisWorkbook considering OneDrive Personal or Business
This is part of the new FixPath() function that will be updated shortly


Public

Tested

My Own Work
Function ToolPath()
    ' Gets tool path in order to be used in other settings
    '
    ' This will be replacing FixPath() to cover issues with OneDrive
    '
    Rett = ThisWorkbook.Path
    If UCase(Left(Rett, 4)) = "HTTP" Then
        ThisPath = ThisWorkbook.FullName
        Dim Fso1
        Set Fso1 = CreateObject("Scripting.FileSystemObject")
        ' Assume it is Consumer OneDrive (There are usually 4 slashes "\" from start of http to the folder we are calling, let us test that)
        Rett = Replace(ThisPath, "/", "\")
        For Ctr = 1 To 4
            Rett = Mid(Rett, InStr(Rett, "\") + 1)
        Next
        ' Checks for the file
        If Fso1.fileexists(Environ("OneDriveConsumer") & "\" & Rett) Then
            Rett = Environ("OneDriveConsumer") & "\" & GetPapa(Rett)
        ElseIf Fso1.fileexists(Environ("OneDrive") & "\" & Rett) Then
            Rett = Environ("OneDrive") & "\" & GetPapa(Rett)
        Else
            For Ctr = 1 To 2
                Rett = Mid(Rett, InStr(Rett, "\") + 1)
            Next
            ' Oops, it is Commercial onedrive. There are usually 6 slashes "\" from start of http to the folder we are calling, let us test that
            If Fso1.fileexists(Environ("OneDriveCommercial") & "\" & Rett) Then
                Rett = Environ("OneDriveCommercial") & "\" & GetPapa(Rett)
            ElseIf Fso1.fileexists(Environ("OneDrive") & "\" & Rett) Then
                Rett = Environ("OneDrive") & "\" & GetPapa(Rett)
            Else
                Rett = "N/A"
            End If
        End If
        Set Fso1 = Nothing
    End If
    If Right(Rett, 1) <> "\" Then Rett = Rett & "\"
    ToolPath = Rett
End Function

None

Views 398 Downloads 88

VBA-Excel File System
ANmarAmdeen
791
Attachments
Revisions

v1.0

Needs