Application.ActiveWorkbook.Path change

[jono_oh]

Hello,

I have been using the macro below to find the filepath of the active workbook and open/loop through particular files. I did it in this manner so that it did not matter where users stored the workbook.

sub open_files()

Dim oFSO As Object
Dim oFolder As Object
Dim oFile As Object

Dim sFilePath As String

sFilePath = Application.Activeworkbook.Path & "\"

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder(sFilePath)

For Each oFile In oFolder.Files

    If InStr(1, oFile.Name, "TSP") > 0 And InStr(1, oFile.Name, "Blank Template") = 0 Then
    
         Workbooks.Open (oFile)
         
    End If
    
Next oFile

End Sub

However, my new work version of excel has changed this and now Application.Activeworkbook.Path returns as:

https://NameOfMyWorkplaceHeremy.sharepoint.com/personal/jonathan_ohara_name_of_my_workplace_here/Documents/2111wk17/Conversion

Previously, Application.Activeworkbook.Path returned in the following manner:

C:\Users\46802537\OneDrive - Name Of My Workplace Here\2111wk17\Conversion\


My question is whether is it possible to amend how Application.Activeworkbook.Path operates?

 

[6StringJazzer]

My question is whether is it possible to amend how Application.Activeworkbook.Path operates?

What is it you want to amend? It is giving you the correct path, which is now on a hosted SharePoint site.

 

[jono_oh]

What is it you want to amend? It is giving you the correct path, which is now on a hosted SharePoint site.

The filepath- the location of the files has not changed; the macro returns an error of 'path not found' when using the macro above

 

[6StringJazzer]

The path is returning as a SharePoint path, so all indications are that the location of the files has changed. How are you opening the file that contains this code?

 

[jono_oh]

The path is returning as a SharePoint path, so all indications are that the location of the files has changed. How are you opening the file that contains this code?

I am opening the file from OneDrive, thank you for your help so for @6StringJazzer, very new to excel/tech so appreciate your patience

 

[jono_oh]

I am opening the file from OneDrive, thank you for your help so for @6StringJazzer, very new to excel/tech so appreciate your patience

the file that contains the macro is HA_RL

 

[6StringJazzer]

Unfortunately I do not have access to a SharePoint site for testing. However, OneDrive is replicated storage, so if you are opening a file from OneDrive it should show up as a local file. What your code does makes it look like you are opening the file in SharePoint.

I am stuck but let me see what else I can find.

 

[jono_oh]

I

Unfortunately I do not have access to a SharePoint site for testing. However, OneDrive is replicated storage, so if you are opening a file from OneDrive it should show up as a local file. What your code does makes it look like you are opening the file in SharePoint.

I am stuck but let me see what else I can find.

think I have found a solution from StackOverflow:

I have inserted the below code into a module:

Sub TestLocalFullName()
    Debug.Print "URL: " & ActiveWorkbook.FullName
    Debug.Print "Local: " & LocalFullName(ActiveWorkbook.FullName)
    Debug.Print "Test: " & Dir(LocalFullName(ActiveWorkbook.FullName))
End Sub

Public Function LocalFullName$(ByVal fullPath$)
    'Finds local path for a OneDrive file URL, using environment variables of OneDrive
    'Reference https://stackoverflow.com/questions/33734706/excels-fullname-property-with-onedrive
    'Authors: Philip Swannell 2019-01-14, MatChrupczalski 2019-05-19, Horoman 2020-03-29, P.G.Schild 2020-04-02

    Dim ii&
    Dim iPos&
    Dim oneDrivePath$
    Dim endFilePath$

    If Left(fullPath, 8) = "https://" Then 'Possibly a OneDrive URL
        If InStr(1, fullPath, "my.sharepoint.com") <> 0 Then 'Commercial OneDrive
            'For commercial OneDrive, path looks like "https://companyName-my.sharepoint.com/personal/userName_domain_com/Documents" & file.FullName)
            'Find "/Documents" in string and replace everything before the end with OneDrive local path
            iPos = InStr(1, fullPath, "/Documents") + Len("/Documents") 'find "/Documents" position in file URL
            endFilePath = Mid(fullPath, iPos) 'Get the ending file path without pointer in OneDrive. Include leading "/"
        Else 'Personal OneDrive
            'For personal OneDrive, path looks like "https://d.docs.live.net/d7bbaa#######1/" & file.FullName
            'We can get local file path by replacing "https.." up to the 4th slash, with the OneDrive local path obtained from registry
            iPos = 8 'Last slash in https://
            For ii = 1 To 2
                iPos = InStr(iPos + 1, fullPath, "/") 'find 4th slash
            Next ii
            endFilePath = Mid(fullPath, iPos) 'Get the ending file path without OneDrive root. Include leading "/"
        End If
        endFilePath = Replace(endFilePath, "/", Application.PathSeparator) 'Replace forward slashes with back slashes (URL type to Windows type)
        For ii = 1 To 3 'Loop to see if the tentative LocalWorkbookName is the name of a file that actually exists, if so return the name
            oneDrivePath = Environ(Choose(ii, "OneDriveCommercial", "OneDriveConsumer", "OneDrive")) 'Check possible local paths. "OneDrive" should be the last one
            If 0 < Len(oneDrivePath) Then
                LocalFullName = oneDrivePath & endFilePath
                Exit Function 'Success (i.e. found the correct Environ parameter)
            End If
        Next ii
        'Possibly raise an error here when attempt to convert to a local file name fails - e.g. for "shared with me" files
        LocalFullName = vbNullString
    Else
        LocalFullName = fullPath
    End If
End Function

Then, from a different module I have:

Sub path_test()
'macro to test the application.activeworkbook.path
'to see whether I may have saved the previous version
'in sharepoint as opposed to OneDrive

ThisWorkbook.Activate

Dim oFSO As Object
Dim oFolder As Object
Dim oFile As Object

Dim sFilePath As String


sFilePath = LocalFullName(ActiveWorkbook.Path)
Debug.Print sFilePath

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder(sFilePath)

For Each oFile In oFolder.Files

    If InStr(1, oFile.Name, "TSP") > 0 And InStr(1, oFile.Name, "Blank Template") = 0 Then
       
        Workbooks.Open (oFile)

    End If

Next oFile

End Sub

A link to the source where I found the solution:

Excel's fullname property with OneDrive

If I want to use the open Workbook object to get the fullname of an Excel file after saving it, but that file has been synchronized to OneDrive, I get a "https" address instead of a local one, which

stackoverflow.com