sharepoint - accessing excel files from VBA

Question

I'm trying to access SharePoint directory from Excel VBA and loop over all files at this directory. But when I try to refer to the directory, then I get this error in return:

Path not found

I tried:

Set folder = fs.GetFolder("//mojsharepoint/xx")

and I tried this:

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("//mysharepoint/xx")

But when referring to a single file on the directory it works perfect

LoadFilePath = "//mojsharepoint/xx/file.xlsm"
Set WB = Workbooks.Open(LoadFilePath)

Answer 1

I just finished a tool that loops through the files in a SP directory using FileSystemObject. But for FSO to work I found it necessary to map the folder to a free drive letter. If this'll help you, I went about it this way:

1. Wrote a quick class (call it "Networks") that fires up Wscript.Network and then provides a few properties and methods for this and future programs. If you want to talk about that in more detail, let me know.

2. Used the Networks object to find an available drive letter and map it to the SharePoint folder. I used the URL for the folder; I didn't know how to get the UNC designation.

3. Used the drive letter and FileSystemObject to get a list of files and looped through it. In my case I writing in Excel; the Excel program opens each file just long enough to check the content and ascertain whether the workbooks is ready for processing, and if it is uses the FSO's Move method to transfer the file out of SharePoint to another location.

4. Use the Networks object to un-map the drive letter.

Is this of any interest to you? (Probably too late by now.)

Answer 2

Try to use the WebDav path like: \Sharepointurl.com@SSL\DavWWWRoot\sitename\folder\ or \Sharepointurl.com@SSL\DavWWWRoot\sitename\folder

BR