Open text file from OneDrive folder

[ErikJan]

In VBA, how to I open a file that is located in a OneDrive folder?

As in

Code: Select all

Open <File> For Input As #1

Normally I'd simply put the file-path in front, with OneDrive that's now a URL...

[ErikJan]

Sorry... think I found something already

Code: Select all

With CreateObject("Scripting.FileSystemObject")
    Debug.Print "Scripting.fso:         "; .GetAbsolutePathName("File")
End With

Update... but not quite... I seem to miss a folder in the path

[ErikJan]

Heavily adopted and simplified from something I found on the Internet... but this one works for me:

Code: Select all

Private Function Local_File_Name(ByRef FileName As String) As String
    'Returns local file path, empty if local path not found
    Dim i As Integer, ShortName As String
    '
    If InStr(FileName, "https://") = 0 Then Local_File_Name = FileName: Exit Function 'Check if path is a OneDrive location
    ShortName = Replace(FileName, "/", "\")
    'Remove first four backslashes
    For i = 1 To 4
        ShortName = Mid(ShortName, InStr(ShortName, "\") + 1)
    Next
    'Double check if file found
    ShortName = Environ("OneDrive") & "\" & ShortName
    If Dir(ShortName) <> "" Then Local_File_Name = ShortName
End Function