Search code examples
excelvbasharepoint

VBA Check If Sharepoint Folder Exists

I am trying to determine if a Sharepoint folder exists in Excel VBA using the URL path and if not create the folder. I can do this easily if I map the network drive:

 myWorkbookBasePath = "Z:Documents\Reports\2013\"

 If Dir(myWorkbookBasePath, vbDirectory) = "" Then
        MkDir myWorkbookBasePath
 End If

However, I can not figure out how to do it using the URL path. If I use 

myWorkBookBasePath= "http://sharepoint/Documents/Reports/2013/"

I get error code 52. Can anyone tell me how to make it work with the URL path?

Solution

  • Give this a go

        myWorkBookBasePath= "\\sharepoint\Documents\Reports\2013\"

    or 

        myWorkBookBasePath = "http://sharepoint/Documents/Reports/2013/"
    myWorkBookBasePath = Replace(Replace(myWorkBookBasePath, "http:", ""), "/", "\")
    MsgBox (myWorkBookBasePath)

    and in case of a Sharepoint site hosted using https

        myWorkBookBasePath = "https://sharepoint/Documents/Reports/2013/"
    myWorkBookBasePath = Replace(Replace(myWorkBookBasePath, "https:", ""), "/", "\")
    myWorkBookBasePath = Replace(myWorkBookBasePath, Split(myWorkBookBasePath, "\")(2), Split(myWorkBookBasePath, "\")(2) & "@SSL")
    MsgBox (myWorkBookBasePath)

    MkDir in VBA can only access filesystem and does not understand URL's, so anything you can open in Explorer you can access with MkDir.