0

Can anyone help me find a solution to the problem below? I'd like to be able to determine whether Sharepoint is available before I turn Autosave one. Has anyone else has faced the issue of converting Sharepoint file path to a local file path?

The code below finds various components of the file names and then writes them to specific cells in the chosen worksheet.

Is there a way to avoid this (occasional) error by checking the Sharepoint status before Autosave is turned on?

    Sub ConvertSharepointPath()
    Dim FilePath As String, FileName As String
    Dim Path As String, Path2 As String, ExtOnly As String, NameOnly As String 
    Dim LocalRoot As String, LocalFullPath As String
    Dim SrchStr As String, ReplStr As String
    
    ' Find current path settings for the active workbook
    With ActiveWorkbook
        FilePath = .FullName
        FileName = .Name
        Path = .Path
    End With
    
    NameOnly = Left(FileName, InStr(1, FileName, ".") - 1)
    ExtOnly = Right(FileName, Len(FileName) - InStr(1, FileName, "."))
    
    ' Strip out all text in path using the sharepoint locations
    ' For me, the string ".sharepoint.com/sites/" is preceded by a string specific to my installation.
    ' This code stripe that out and stores the directory structure in Path2
    SrchStr = ".sharepoint.com/sites/"
    Path2 = Right(Path, Len(Path) - (InStr(1, Path, SrchStr) + Len(SrchStr) - 1))
    
    ' Convert backward slash to forward slash, in order to adapt the directory location to a Windows naming convention
    SrchStr = "/"
    ReplStr = "\"
    Path2 = Replace(Path2, SrchStr, ReplStr)
    
    ' I have "\Shared" in the Sharepoint path and need " - " in the local path
    SrchStr = "\Shared "
    ReplStr = " - "
    Path2 = Replace(Path2, SrchStr, ReplStr)
    
    ' Find local path to OneDrive files, can use either "OneDrive" or "OneDriveCommercial"
    LocalRoot = Environ$("OneDriveCommercial")
    ' Need to remove "OneDrive - " as this isn't present in my local path
    SrchStr = "OneDrive - "
    LocalRoot = Left(LocalRoot, InStr(1, LocalRoot, SrchStr) - 1) & Right(LocalRoot, Len(LocalRoot) - (InStr(1, LocalRoot, SrchStr) + Len(SrchStr) - 1)) & "\" & Path2
    LocalFullPath = LocalRoot & "\" & Path2 & "\" & FileName
    
    ' Display various name components
    Sheets("Tracking").Activate
    With Range("A11")
        .Offset(0, 0) = "Sharepoint Full Name: "
        .Offset(0, 1) = FilePath
        .Offset(1, 0) = "File Name: "
        .Offset(1, 1) = FileName
        .Offset(2, 0) = "File Name w/o Ext: "
        .Offset(2, 1) = NameOnly
        .Offset(3, 0) = "File Ext: "
        .Offset(3, 1) = ExtOnly
        .Offset(4, 0) = "Sharepoint File Path: "
        .Offset(4, 1) = Path
        .Offset(5, 0) = "Local Path Ending: "
        .Offset(5, 1) = Path2
        .Offset(6, 0) = "Local File Path: "
        .Offset(6, 1) = LocalRoot
        .Offset(7, 0) = "Local Full Path: "
        .Offset(7, 1) = LocalFullPath
    End With

    End Sub

    Sub ListEnvVariables()
    ' Adapted from https://wellsr.com/vba/2019/excel/list-all-environment-variables-with-vba-environ/

    Dim EnvStr As String
    Dim EnvSplit As Variant
    Dim i As Integer, j As Integer
    
    For i = 1 To 255
        EnvStr = Environ$(i)
        If Len(EnvStr) = 0 Then GoTo iNext:
        EnvSplit = Split(EnvStr, "=")
        With Range("A20")
            .Offset(i, 0).Value = i
            For j = 1 To UBound(EnvSplit)
                .Offset(i, j).Value = EnvSplit(j - 1)
            Next j
        End With
    iNext:
    Next i

    End Sub

The second macro is used simply to list all of the Environmental variables in the event that the ones I've used don't provied the correct answer.

Any1There
  • 186
  • 1
  • 8
  • Make sure you actually *ask a question* - just a code dump isn't helpful. – BigBen Jul 17 '20 at 14:59
  • I run some large excel files which save data and are currently automatically saving this on the sharepoint site rather than on the local drive. So, I've developed this code to be able to convert the sharepoint file path to a local file path (where the save time is faster). – Any1There Jul 17 '20 at 15:00
  • So do you have a question then? – BigBen Jul 17 '20 at 15:00
  • Sharepoint/OneDrive then syncs the file in the background without delaying the macro execution. Interested to know whether anyone else has faced/solved this problem and whether there's a better way. – Any1There Jul 17 '20 at 15:01
  • You should [edit] your question with that detail. – BigBen Jul 17 '20 at 15:01
  • I run into this situation all the time: Open a local file in MS-Office. Under the "File" menu, all the links are URL (https) links to the file on my OneDrive Sharepoint. But I need to drag and drop the local instance of the file into JIRA, or SBM or other programs. Trying to drag/drop the URL in MS-Office either doesn't work or drops a URL into the target program. That link is useless for co-workers. SO. Question: How do I derive the UNC of the local instance of a file opened in MS-Office, when Office will only show me the URL of the file on OneDrive Sharepoint? – mnemotronic Apr 25 '21 at 02:51
  • @mnemotronic, the problem you described has a solution [here](https://stackoverflow.com/a/73577057/12287457). – GWD Sep 05 '22 at 23:58

0 Answers0