VBA - Checking Folder/File exist in SharePoint

Question

I wanted to copy a local file to sharepoint library using VBA by clicking an image. Right now seems like I'm unable to check for Folder & Files on SharePoint.

As every time I ran the code(by clicking an image in excel), it returns unable to find the file in SharePoint. And stops at returning the MsgBox Sorry there's no such Folder......

I tried mapping drive, it works perfectly fine, but not an options because end-user need to map the drive by themselves. So now I'm looking to connecting to SharePoint using the link.

If I copy the SharePointLink to IE & Chrome using \, it works fine. But if I uses /, IE is unable to find the link.

UPDATE

If I uses \ after few tries, IE, will open up the file path in NetWork. Chrome will show the file path on chrome page. Why is this happening?????

The authentication is using windows authentication, so not an issue.

This is my code

Sub imgClicked()

Dim SharePointLib As String
Dim MyPath As String
Dim folderPath As String
Dim objNet As Object
Dim FSO As Object
Dim copyPath As String
Dim copyFilePath As String

folderPath = Application.ThisWorkbook.path
MyPath = Application.ThisWorkbook.FullName

SharePointLib = "//company.com/sites/MS/10%20Mg%20Review/"
' create new folder to store the file
copyPath = folderPath + "\copyPath\" 

If Not FolderExists(copyPath) Then
    FolderCreate (copyPath)
ElseIf Not FolderExists(SharePointLib) Then
    MsgBox "Sorry there's no such folder. Folder Path: " & vbNewLine & vbNewLine & SharePointLib & ""
    Exit Sub
End If

fileName = "hello.xlsm"
'Copy current excel file and save at the new folder created
ThisWorkbook.SaveCopyAs copyPath & fileName
MsgBox "Save Copy As: " + copyPath & filseName & vbNewLine & vbNewLine & "The file will be uploaded to this address: " + SharePointLib & fileName

' Check whether the file exist in the directory
' If exist error message
' else copy the file from copyPath then paste at the SharePoint directory
If Not Dir(SharePointLib & fileName, vbDirectory) = nbNullString Then
    MsgBox "Sorry file already exist!"
Else
    Call FileCopy(copyPath & fileName, SharePointLib & fileName)
    MsgBox "File has being successfuly created in SharePoint!"
End If

Set FSO = CreateObject("scripting.filesystemobject")
If Right(copyPath, 1) = "\" Then
    copyPath = Left(copyPath, Len(copyPath) - 1)
End If
If FSO.FolderExists(copyPath) = False Then
    MsgBox copyPath & " doesn't exist"
    Exit Sub
End If
FSO.DeleteFolder copyPath
MsgBox "Folder has being deleted successfully!"

End Sub

Function for checking if folder exists

Function FolderExists(ByVal path As String) As Boolean

FolderExists = False
Dim FSO As New FileSystemObject

If FSO.FolderExists(path) Then FolderExists = True

End Function

Function for creating Folder

Function FolderCreate(ByVal path As String) As Boolean

FolderCreate = True
Dim FSO As New FileSystemObject

try:
If FSO.FolderExists(path) Then
    Exit Function
Else
    On Error GoTo catch
    FSO.CreateFolder path
    Debug.Print "FolderCreate: " & vbTab & path
    Exit Function
End If

catch:
MsgBox "A folder could not be created for the following path: " & path & ". Check the path name and try again."
FolderCreate = False
Exit Function

End Function

Any help and suggestions are appreciated. Let me know if more info is needed. Thanks in advance.

Try SharePointLib = "\\company.com\sites\MS\10 Mg Review\" — Tim Williams
@TimWilliams Yes, I'm using SharePointLib = "\\company.com\sites\MS\10 Mg Review\" Now. It works fine now, but in the morning it is unable to find the path, do you have any idea why it works suddenly after some time? I did not change the code at all. — aaa
I've seen that kind of thing occasionally - typically opening up Windows Explorer and navigating to the path in question will fix it. — Tim Williams
@TimWilliams perhaps you're right. I was testing using chrome in the morning but unable to get the path. In the afternoon, i test using IE, it works fine. After testing using IE, Chrome also works fine. — aaa
Accessing via UNC path relies on webclient service. Explorer handles this and connects to the WebDAV share on the server automatically. Once that is done the system will be able to connect via the UNC — Chris Meurer

Chris Meurer Chris Meurer · Accepted Answer · 2017-11-28T17:51:36

Ensure the WebClient service is running. You can start the WebClient service through code, or you could set the startup type to automatic.

With the WebClient service running, your folder/file tests will work as expected.

Edit: Additionally, if you map the sharepoint url to a drive letter, Windows will start the WebClient service.

Sub mapPath(str_drive as string, str_path as string)
  If Not Len(str_drive) = 1 Then Exit Sub
  Dim wso As Object
  Set wso = CreateObject("WScript.Network")
  wso.MapNetworkDrive str_drive & ":", str_path, False
End Sub

VBA - Checking Folder/File exist in SharePoint

1 Answers