Excel and Sharepoint – Revisited

A few months ago, I wrote this blog entry here where I talk about logging into SharePoint, verifying a particular file exists, checking for a lock on the file, and informing the user of the result.

Well, after much ado, I finished it.  With a lot of swearing, cursing, and hating of SharePoint in general.

What a pain.

To figure this out, I was forced to abandon the VBA approach and enter what was, for me at least, uncharted territory:  .NET

I scoured the internet, looking for what nuggets of information I could find regarding how Sharepoint works, and how to do what I wanted to do.

I ended up finding a program, written in C#, which explained how to open up a login screen, prompt the user to log in if needed, save a cookie, and then close the screen and continue on.  I also found a bunch of stuff, written in VB.NET on how to enumerate file details.

So I put two and two together, and came up with 38.

Ultimately, I managed to get it to work.  This involved taking what I could find in C#, a language that I had written a grand total of nothing in, and translating it to VB.NET.

VB.NET is also a language, that, until then, I had written a grand total of nothing in, which means I was translating a program written in a language I had never used into another language I had never used, which was designed to interface with an incredibly cumbersome and difficult product that I had, until I was working with this client, never used.

And I got it to work.

I’m not going to go into the details of the program right now, as it’s 12:30am on a Sunday morning and I have a busy day ahead of me, except to say that it involves the following:

references to Microsoft.SharePoint.Client.Runtime, Microsoft.SharePoint.Client

Importing Microsoft.SharePoint.Client, System.Windows.Forms, and the appropriate interop services references as well.

Creating a thread to open a forms application on
Saving Cookies
Authenticating the user with a userform/webbrowser
Getting a claims client context
Loading the claims client context using a VB lambda request to enumerate the .Exists and .LockedByUser.Email properties of a SPFIle object
and then returning the information to VBA on request.

So that’s the basic outline on how to do it…

About Steven Britton

Steve is a freelance programmer, partial billionaire, dad, Recovering Atheist, Conservative, and occasionally prolific blogger.