VBA FileExists and Sharepoint

Question

I'm running into issues trying to pull info from files stored in Sharepoint.

Namely, FileExists isn't working and Overwrite file doesn't seem to be working either.

There was a discussion here, but few answers -> posting this question again in hopes some things have changed

My code runs like this:

strFileExists = Dir(Filepath & Filename)

And returns: File path not found -> I checked the path and even opened a file and recorded the macro to make sure it was the same file path without issue, but it appears DIR() is the issue.

The business dept I'm working with is entirely switching over to Sharepoint so hoping there's a straightforward solution without setting up network shares or doing C/personal/OneDrive things

There's no easy (or even only moderately complicated) solution to this problem - VBA just doesn't work with HTTP paths when it comes to finding/listing files etc. This is going to be a big problem for all of us who need to do anything with VBA in a SharePoint/OneDrive environment. — Tim Williams, Sep 28 '22 at 19:12

Claus Maier · Answer 1 · 2022-09-28T20:55:32.190

You can navigate and look for files on OneDrive like this

Sub check_File_Exists()

Dim path As String
Dim strType As String
Dim file As Variant
Dim yourFile As String

'replace uname with your user name
    path = "C:\Users\uname\OneDrive\"
    strType = "*txt"
    yourFile = "test.txt"
    
    file = Dir(path & strType)
    Do While (file <> "")
        If file = yourFile Then
            Debug.Print ("File: " & file & " found!")
            Exit Do
        End If
        file = Dir
    Loop

End Sub

Hope it helps

VBA FileExists and Sharepoint

1 Answers1