Get the local path to a OneDrive folder
2020-06-22 Files & Folders 3 392
With the function below you can get the local path to a OneDrive folder, either the "Commercial" or "Consumer" type:
Function OneDrive_LocalFolder(Optional blnPersonal As Boolean = False, Optional blnInclLastPathSepChar As Boolean = True) As String ' updated 2020-06-22 by OPE ' returns the path to the local OneDrive folder, business or personal, returns an empty string if the folder doesn't exist Dim strOneDrive As String, OK As Boolean On Error Resume Next If blnPersonal Then strOneDrive = Environ("OneDriveConsumer") ' local path to the OneDrive Personal sync folder Else strOneDrive = Environ("OneDriveCommercial") ' local path to the OneDrive Business sync folder End If 'If Len(strOneDrive) = 0 Then strOneDrive = Environ("OneDrive") ' local path to the OneDrive sync folder On Error GoTo 0 If Len(strOneDrive) = 0 Then Exit Function ' folder information not found OK = False On Error Resume Next OK = Len(Dir(strOneDrive, vbDirectory)) <> 0 ' vbDirectory=16 On Error GoTo 0 If Not OK Then Exit Function ' folder doesn't exist If blnInclLastPathSepChar Then ' add any missing last path separator char If Right(strOneDrive, 1) <> Application.PathSeparator Then strOneDrive = strOneDrive & Application.PathSeparator Else ' remove any existing last path separator char If Right(strOneDrive, 1) = Application.PathSeparator Then strOneDrive = Left(strOneDrive, Len(strOneDrive) - 1) End If OneDrive_LocalFolder = strOneDrive End FunctionWith the function below you can retrieve the local file or folder path to a workbook that is saved in a OneDrive Personal or Business folder. Very useful when wb.FullName or wb.Path returns an URL path.
Note! This doesn't work if the workbook is saved in a shared OneDrive folder.
Function Workbook_LocalPath(wb As Workbook, Optional blnReturnFolderOnly As Boolean = False) As String ' updated 2020-06-23 by OPE ' returns the local file or folder path to a workbook that is saved in a OneDrive Personal or Business folder ' will not work if the workbook is saved in a shared OneDrive folder ' useful when wb.FullName or wb.Path returns an URL path ' example: strFilePath = Workbook_LocalPath(ActiveWorkbook) Const cstrOneDrivePersonal As String = "https://*.docs.live.net/*/*" Const cstrOneDriveBusiness As String = "https://*.sharepoint.com/personal/*/documents/*" Dim strFilePath As String, strLocalPath As String, strOneDrive As String, i As Long If wb Is Nothing Then Exit Function strFilePath = wb.FullName If blnReturnFolderOnly Then Workbook_LocalPath = Left(strFilePath, Len(strFilePath) - Len(wb.Name)) ' folder included the last path separator char Else Workbook_LocalPath = strFilePath ' original file path End If If Not LCase(strFilePath) Like "https://*" Then Exit Function strLocalPath = vbNullString strOneDrive = vbNullString If LCase(strFilePath) Like LCase(cstrOneDrivePersonal) Then ' personal or consumer OneDrive path On Error Resume Next strLocalPath = Split(strFilePath, "/", 5, vbBinaryCompare)(4) ' everything after the 4th path separator strOneDrive = Environ("OneDriveConsumer") ' path to the local OneDrive Personal folder On Error GoTo 0 End If If LCase(strFilePath) Like LCase(cstrOneDriveBusiness) Then ' business or commercial OneDrive path On Error Resume Next strLocalPath = Split(strFilePath, "/", 7, vbBinaryCompare)(6) ' everything after the 6th path separator strOneDrive = Environ("OneDriveCommercial") ' path to the local OneDrive Business folder On Error GoTo 0 End If If Len(strOneDrive) > 0 Then ' the local OneDrive folder path was found strLocalPath = Replace(strLocalPath, "/", Application.PathSeparator) ' convert path separator chars If Right(strOneDrive, 1) <> Application.PathSeparator Then strOneDrive = strOneDrive & Application.PathSeparator strLocalPath = strOneDrive & strLocalPath If Len(Dir(strLocalPath)) > 0 Then ' file exist in the local folder If blnReturnFolderOnly Then Workbook_LocalPath = Left(strLocalPath, Len(strLocalPath) - Len(wb.Name)) ' folder included the last path separator char Else Workbook_LocalPath = strLocalPath ' local file path End If End If End If End Function