none
how to get physical path instead of URL (OneDrive) RRS feed

  • Question

  • Hi!

    I am writing a Word 2013 macro. I use ActiveDocument.FullName on a file in OneDrive (synced to my hard drive). I want to get a direct reference to my hard drive file name (c:\Users\User X\document.docx), instead of OneDrive UNC (https://d.docs.live.net/blaah/Documents). How can I get a physical filename/path in VBA?

    Thanks,

    Kaido

    Monday, November 17, 2014 5:42 PM

Answers

  • Hi Kaido,

    Sorry for the mistake, after the further investigation, it is impossble.

    For the Word Application, the file is stored on the OneDrive, and the "Offline" is cache mode for OneDrive, it is transparent to Word Application (Word Application only know it is a document on OneDrive), so when you check the location of the opened document, the location is "http:/d.docs.live.net/xxxx/xx.docx" rather than "C:\XXX\XXX".

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, November 21, 2014 2:57 AM
    Moderator

All replies

  • Thanks. I have already done that, ie. my files are available offline. Still, ActiveDocument.FullName returns UNC https://...

    br,

    Kaido

    Tuesday, November 18, 2014 9:45 AM
  • Hi Kaido,

    Sorry for the mistake, after the further investigation, it is impossble.

    For the Word Application, the file is stored on the OneDrive, and the "Offline" is cache mode for OneDrive, it is transparent to Word Application (Word Application only know it is a document on OneDrive), so when you check the location of the opened document, the location is "http:/d.docs.live.net/xxxx/xx.docx" rather than "C:\XXX\XXX".

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, November 21, 2014 2:57 AM
    Moderator
  • Hi Kaido,

    It's been a while since you posted this so I suppose you don't need an answer any longer... in which case I'll add my reply for anyone who's interested. I'm using Word 2016 but I'm pretty sure this can be done in Word 2013 and Word 2010 as well. So it's not impossible, as Fei Xue suggests. ;^)

    Private Function GetDocLocalPath(doc As Document) As String
    'return the local path for doc, which is either already a local document or a document on OneDrive
    Const strcOneDrivePart As String = "https://d.docs.live.net/"
    Dim strRetVal As String, bytSlashPos As Byte
        
        strRetVal = doc.Path & "\"
        If Left(LCase(doc.Path), Len(strcOneDrivePart)) = strcOneDrivePart Then 'yep, it's the OneDrive path
            'locate and remove the "remote part"
            bytSlashPos = InStr(Len(strcOneDrivePart) + 1, strRetVal, "/")
            strRetVal = Mid(doc.Path, bytSlashPos)
            'read the "local part" from the registry and concatenate
            strRetVal = System.PrivateProfileString("", "HKEY_CURRENT_USER\Environment", "OneDrive") & strRetVal
            strRetVal = Replace(strRetVal, "/", "\") & "\" 'slashes in the right direction
            strRetVal = Replace(strRetVal, "%20", " ") 'a space is a space once more
        End If
        GetDocLocalPath = strRetVal
        
    End Function

    • Proposed as answer by LucasHol Thursday, October 26, 2017 4:59 AM
    Sunday, May 21, 2017 1:32 PM
  • I am about to try this, and if it works, you win at life!!
    Thursday, October 26, 2017 3:45 AM
  • I was using excel not word, so the registry grabber "System." did not work for me. I did the following:

    (Also notice I did change some slashes stuff for what I wanted returned! Other than that works great! Thank you so much!


    'reads the value for the registry key i_RegKey
    'if the key cannot be found, the return value is ""
    Function RegKeyRead(i_RegKey As String) As String
    Dim myWS As Object

      On Error Resume Next
      'access Windows scripting
      Set myWS = CreateObject("WScript.Shell")
      'read key from registry
      RegKeyRead = myWS.RegRead(i_RegKey)
    End Function



    ' This is needed to get the local path, not the one drive path
    Private Function GetDocLocalPath(docPath As String) As String
    'return the local path for doc, which is either already a local document or a document on OneDrive
    Const strcOneDrivePart As String = "https://d.docs.live.net/"
    Dim strRetVal As String, bytSlashPos As Byte
        
        strRetVal = docPath & "\"
        If Left(LCase(docPath), Len(strcOneDrivePart)) = strcOneDrivePart Then 'yep, it's the OneDrive path
            'locate and remove the "remote part"
            bytSlashPos = InStr(Len(strcOneDrivePart) + 1, strRetVal, "/")
            strRetVal = Mid(docPath, bytSlashPos)
            'read the "local part" from the registry and concatenate
            strRetVal = RegKeyRead("HKEY_CURRENT_USER\Environment\OneDrive") & strRetVal
            strRetVal = Replace(strRetVal, "/", "\") 'slashes in the right direction
            strRetVal = Replace(strRetVal, "%20", " ") 'a space is a space once more
        End If
        GetDocLocalPath = strRetVal
        
    End Function

    • Proposed as answer by LucasHol Thursday, October 26, 2017 4:59 AM
    Thursday, October 26, 2017 4:59 AM
  • Thank you! This code works really well and has dug me out of a hole.
    Monday, February 25, 2019 12:13 AM
  • Hello there,

    In case your OneDrive folder is not in default location, the code below will allow you to locate the file wherever it is.

    The code checks all instances of OneDrive in the system and verify if file is there.

    Simply call it from another sub or function to obtain the file path.

    Private Function strOneDriveLocalFilePath() As String
    On Error Resume Next 'invalid or non existin registry keys check would evaluate error
        Dim ShellScript As Object
        Dim strOneDriveLocalPath As String
        Dim strFileURL As String
        Dim iTryCount As Integer
        Dim strRegKeyName As String
        Dim strFileEndPath As String
        Dim iDocumentsPosition As Integer
        Dim i4thSlashPosition As Integer
        Dim iSlashCount As Integer
        Dim blnFileExist As Boolean
        Dim objFSO As Object
        
        strFileURL = ThisWorkbook.FullName
        
        'get OneDrive local path from registry
        Set ShellScript = CreateObject("WScript.Shell")
        '3 possible registry keys to be checked
        For iTryCount = 1 To 3
            Select Case (iTryCount)
                Case 1:
                    strRegKeyName = "OneDriveCommercial"
                Case 2:
                    strRegKeyName = "OneDriveConsumer"
                Case 3:
                    strRegKeyName = "OneDrive"
            End Select
            strOneDriveLocalPath = ShellScript.RegRead("HKEY_CURRENT_USER\Environment\" & strRegKeyName)
            'check if OneDrive location found
            If strOneDriveLocalPath <> vbNullString Then
                'for commercial OneDrive file path seems to be like "https://companyName-my.sharepoint.com/personal/userName_domain_com/Documents" & file.FullName)
                If InStr(1, strFileURL, "my.sharepoint.com") <> 0 Then
                    'find "/Documents" in string and replace everything before the end with OneDrive local path
                    iDocumentsPosition = InStr(1, strFileURL, "/Documents") + Len("/Documents") 'find "/Documents" position in file URL
                    strFileEndPath = Mid(strFileURL, iDocumentsPosition, Len(strFileURL) - iDocumentsPosition + 1)  'get the ending file path without pointer in OneDrive
                Else
                    'do nothing
                End If
                'for personal onedrive it looks like "https://d.docs.live.net/d7bbaa#######1/" & file.FullName, _
                '   by replacing "https.." with OneDrive local path obtained from registry we can get local file path
                If InStr(1, strFileURL, "d.docs.live.net") <> 0 Then
                    iSlashCount = 1
                    i4thSlashPosition = 1
                    Do Until iSlashCount > 4
                        i4thSlashPosition = InStr(i4thSlashPosition + 1, strFileURL, "/")   'loop 4 times, looking for "/" after last found
                        iSlashCount = iSlashCount + 1
                    Loop
                    strFileEndPath = Mid(strFileURL, i4thSlashPosition, Len(strFileURL) - i4thSlashPosition + 1)  'get the ending file path without pointer in OneDrive
                Else
                    'do nothing
                End If
            Else
                'continue to check next registry key
            End If
            If Len(strFileEndPath) > 0 Then 'check if path found
                strFileEndPath = Replace(strFileEndPath, "/", "\")  'flip slashes from URL type to File path type
                strOneDriveLocalFilePath = strOneDriveLocalPath & strFileEndPath    'this is the final file path on Local drive
                'verify if file exist in this location and exit for loop if True
                If objFSO Is Nothing Then Set objFSO = CreateObject("Scripting.FileSystemObject")
                If objFSO.FileExist(strOneDriveLocalFilePath) Then
                    blnFileExist = True     'that is it - WE GOT IT
                    Exit For                'terminate for loop
                Else
                    blnFileExist = False    'not there try another OneDrive type (personal/business)
                End If
            Else
                'continue to check next registry key
            End If
        Next iTryCount
        'display message if file could not be located in any OneDrive folders
        If Not blnFileExist Then MsgBox "File could not be found in any OneDrive folders"
        
        'clean up
        Set ShellScript = Nothing
        Set objFSO = Nothing
    End Function

    You are welcome.



    • Edited by MatChrupczalski Thursday, May 9, 2019 5:45 PM Solution is for excel, changeing ThisWorkbook with ActiveDocument should work
    Thursday, May 9, 2019 5:39 PM
  • Great solution. local onedrive can be read in VBA from environement varaible using ENVIRON("OneDrive")
    Sunday, May 19, 2019 12:16 PM