How to get the path+filename of an excel workbook stored in my local OneDrive folder? (as opposed to its URL!)

Question

I have an excel workbook that is stored locally on my PC but inside my OneDrive sync-ed folder. Whenever I try (from the immediate window and programmatically as well):

? Excel.Application.ThisWorkbook.FullName

I get something like:

https://d.docs.live.net/c31ceb5b47a36fa2/VBA/learnVBAmacros.xlsb

whereas the real local path of my file is:

C:\Users\viset\OneDrive\VBA\learnVBAmacros.xlsb

How can I retrieve this latter LOCAL path to my workbook, instead of its URL on OneDrive?

A solution to this problem is available [here](https://stackoverflow.com/a/73577057/12287457). — GWD, Sep 05 '22 at 12:04

score 1 · Answer 1 · answered Dec 03 '21 at 01:29

1

Simply go to your one drive settings and untick the "Use office applications to sync...."

enter image description here

answered Dec 03 '21 at 01:29

CarlS

23
5

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - [From Review](/review/late-answers/30493994) – Martin Dec 05 '21 at 03:28
Thank you, good solution if one doesn't need collaboration. – WeAreOne Jan 02 '23 at 15:27

score 0 · Answer 2 · 2019-02-15T06:34:14.780

0

Split off the filename and use environment variables for your local OneDrive folder.

dim fn as string

fn = ThisWorkbook.FullName
'split off the workbook name if fullname is a url
fn = split(fn, "/")(ubound(split(fn, "/")))
'split off the workbook name if fullname is a local path
fn = Split(fn, "\")(UBound(Split(fn, "\")))
'add the environment var
fn = environ("onedrive") & "\" & fn

'check to see if it exists
if len(dir(fn)) > 0 then
    debug.print fn
end if

Note that there are OneDrive and OneDriveConsumer environment variables. My own are identical but there must be a reason for each.

edited Feb 15 '19 at 06:34

answered Feb 15 '19 at 05:32

please recheck your code it give following output `C:\Users\kakka\OneDrive\C:\Users\kakka\Documents\environ.xlsm ` to me – skkakkar Feb 15 '19 at 06:16
That would depend on whether ThisWorkbook.FullName as a OneDrive URL or a local path. The op has stated that ThisWorkbook.FullName was returning the OneDrive URL and I provided a conversion for that situation. Of course, a simple check could easily be generated. – Feb 15 '19 at 06:20

xyz333 · Answer 3 · 2020-05-06T16:32:30.113

This works, sorry but I can not remember where I found it

Sub GetDocLocalPath_Test()

  MsgBox GetDocLocalPath(ThisWorkbook.FullName)

End Sub

Function GetDocLocalPath(docPath As String) As String
'Gel Local Path NOT URL to 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

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

You probably got this solution from [here](https://social.msdn.microsoft.com/Forums/office/en-US/1331519b-1dd1-4aa0-8f4f-0453e1647f57/how-to-get-physical-path-instead-of-url-onedrive#:~:text=Function%20RegKeyRead(i_RegKey,%C2%A0%20%C2%A0%20GetDocLocalPath%20%3D%20strRetVal "how to get physical path instead of URL (OneDrive) - (https://social.msdn.microsoft.com/Forums/office/...)"). The solution was originally written by [Cooz2](https://social.msdn.microsoft.com/profile/cooz2) and adapted for VBA by [LucasHol](https://social.msdn.microsoft.com/profile/lucashol). — GWD, Jan 28 '23 at 16:55

score 0 · Answer 4 · edited Jun 05 '20 at 20:50

0

I had the same problem and the solution in very easy.

If your file is pending for synchronization on OneDrive you will have the value of ThisWorkBook.Path as an URL address.

Unless your file synchronizes the value of ThisWorkBook.Path will contain the local address of the file.

edited Jun 05 '20 at 20:50

Muhammad Dyas Yaskur

6,914
10
48
73

answered Jun 05 '20 at 04:22

Juan Jose Descailleaux

1

score 0 · Answer 5 · answered Nov 03 '20 at 05:53

Combining a few earlier answers, I went with this function. It makes fewer assumptions about the format of a OneDrive URL and it uses the environment instead of the registry.

Note: it does still make assumptions about the OneDrive URL. Specifically:

Assumed to start with "https:"
Assumed only one URL path-component after the hostname
Assumed that what follows will match the local file-system

Function GetWorkbookDirectory() As String

    Dim sPath As String
    Dim sOneDrive As String
    Dim iPos As Integer
    
    sPath = Application.ActiveWorkbook.Path
    
    ' Is this a OneDrive path?
    If Left(sPath, 6) = "https:" Then
        ' Find the start of the "local part" of the name
        iPos = InStr(sPath, "//")           ' Find start of URL hostname
        iPos = InStr(iPos + 2, sPath, "/")  ' Find end of URL hostname
        iPos = InStr(iPos + 1, sPath, "/")  ' Find start of local part
        
        ' Join that with the local location for OneDrive files
        sPath = Environ("OneDrive") & Mid(sPath, iPos)
        sPath = Replace(sPath, "/", Application.PathSeparator)
    End If
    
    GetWorkbookDirectory = sPath

End Function

score 0 · Answer 6 · answered Apr 25 '21 at 20:14

I solved this using FileSystemObject.

Dim fso as FileSystemObject, localPath as String, localFullFileName as String
localPath = fso.GetParentFolderName(fso.GetAbsolutePathName(Application.ActiveWorkbook.Name))
localFullFileName = fso.GetAbsolutePathName(Application.ActiveWorkbook.Name)

score 0 · Answer 7 · answered Jan 04 '23 at 16:16

0

I solved this using CurDir

Function GetFilePath() As String
    GetFilePath = CurDir
End Function

answered Jan 04 '23 at 16:16

Fabio

1

`CurDir` does not necessarily return the path of the current workbook. See [here](https://stackoverflow.com/a/41274201/12287457) for more information. For a solution for the problem this question is about, see [here](https://stackoverflow.com/a/73577057/12287457). – GWD Jan 26 '23 at 10:51

How to get the path+filename of an excel workbook stored in my local OneDrive folder? (as opposed to its URL!)

7 Answers7

Linked