How do I read a secure sharepoint directory with VBA?

Question

I’m trying to write a macro to connect to an https sharepoint directory and read the list of files contained within the directory (so that I can parse the filenames and then open the appropriate one and read some data from it).

So far I’ve tried:

• Get the content of a sharepoint folder with Excel VBA. Set folder = fs.GetFolder seems to work with an http sharepoint, but not an https: sharepoint
• http://blog.itwarlocks.com/2009/04/28/accessing-webdav-in-microsoft-word-visual-basic/ getChildren() doesn’t work here. The directory is returned as a simple record (even though it's actually a complex one with a subfolder and files, that should have Children).
• connecting to an excel document on the sharepoint and then invoking the shared workspace function. This doesn't return any documents in the directory even though there are several other files in the directory.

Has anyone had success with this on a secure https sharepoint directory??

Answer 1

Sharepoint exposes webservices. You can use these to iterate through your libraries.

http://msdn.microsoft.com/en-us/library/ms479390(v=office.12).aspx

Sharepoint 2010 also provides the managed Client Object Model.

This will do what you want: http://msdn.microsoft.com/en-us/library/aa159897(v=office.11).aspx

Answer 2

If the files you want are in a document library, you could use WebDAV to get access to them via something like: \sp.example.com\sites\thesite\doclib\foldername