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

Related searches

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?

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.

Get the path of running file (.py) in Python: __file, __file__ gives the relative path from the current working directory. You can also get the absolute path using functions of os.path module. Here, the� I found the following commands will all return the full path of the parent directory of a Python 3.6 script. Python 3.6 Script: #!/usr/bin/env python3.6 # -*- coding: utf-8 -*- from pathlib import Path #Get the absolute path of a Python3.6 script dir1 = Path().resolve() #Make the path absolute, resolving any symlinks. dir2 = Path().absolute() #See @RonKalian answer dir3 = Path(__file__).parent

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

os - Get the absolute path of a file - Python code example, Python code example 'Get the absolute path of a file' for the package os, powered by Kite. This command resolves a Universal Naming Convention (UNC) path and returns the shares in the path. Example 5: Get relative paths PS C:\> Resolve-Path -Path "c:\prog*" -Relative .\Program Files .\Program Files (x86) .\programs.txt. This command returns relative paths for the directories at the root of the C: drive.

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.

How to get the complete path to a file or folder on Windows 10 , Paths are needed when you have to move a Command Prompt or PowerShell window to a Duration: 1:37 Posted: Feb 14, 2020 The Split-Path cmdlet returns only the specified part of a path, such as the parent folder, a subfolder, or a file name. It can also get items that are referenced by the split path and tell whether the path is relative or absolute. You can use this cmdlet to get or submit only a selected part of a path.

How do I get the path to common folders on my computer?, Operating System Path to "the Desktop" . Mac OS X: /Users/username/Desktop; Windows: C:/Users� Method Invoked Returns in the Solaris OS Returns in Microsoft Windows Comment; toString /home/joe/foo: C:\home\joe\foo: Returns the string representation of the Path.If the path was created using Filesystems.getDefault().getPath(String) or Paths.get (the latter is a convenience method for getPath), the method performs minor syntactic cleanup.

To view and set the path in the Windows command line, use the path command. Setting the path and variables in Windows 8. From the desktop, right-click the very bottom-left corner of the screen to get the Power User Task Menu. From the Power User Task Menu, click System. Click the Advanced System Settings link in the left column.

When you type a command into your Linux shell, it doesn't look in every directory to see if there's a program by that name. It only looks to the ones you specify.

Comments
  • you can check ?environ(17) in immediate window
  • please recheck your code it give following output C:\Users\kakka\OneDrive\C:\Users\kakka\Documents\environ.xlsm to me
  • 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.