Excel VBA, SOAP, XML and SharePoint – Verify a File Exists (Updated July 25, 2013)

Update: July 25, 2014:

Note: I am going to publish the code that made this program actually work. Properly. Through Excel. In VB. I just haven’t got around to it yet.

The way to do it: Have the user log in by opening up a browser window and saving a cookie. Then close the window again. Now, every time the user tries to run the program, it will work, until that is, the user clears their cookies.

You’re welcome.

August 4, 2013: Update

I’ve had to extend the scope of this project beyond the level of VBA and into C#/Visual Basic inside VisualStudio.  More to come.

May 30, 2013: Update

I’ve received a notification from my client that Microsoft has updated Sharepoint.  The solution I indicate below no longer works – the SOAP string results in an “Access Denied” error, and quietly fails.  I’m working on a fix for this, and will post it when I figure it out.

Why, Microsoft… WHY????

8<————-

Wow, what a title…

This last week I have been hard at work on a project for a client.  Essentially, the client needed an excel macro which would accomplish this:

1. Open a worksheet found in a predetermined location on a SharePoint Server.
2. Do some stuff with it.
3. Save the worksheet back to the SharePoint Server.

It seems pretty simple, doesn’t it?  SharePoint is Microsoft, Excel is Microsoft, you’d think the two would talk to each other easily; or that Microsoft would build in various classes and methods associated to make linking Excel to SharePoint simple.

They didn’t.

So, breaking Step 1 down, let’s examine what we need to do:

A. Get the file name we want to open.
B. Open the file.

That’s simple enough, and when I used this method

[code]
filename = "http://subdomain.sharepoint.com/path/to/file/filename.xlsx"
workbooks.open(filename)
[/code]

It worked just fine.  I extended it, of course, by adding in the appropriate check out code as well:

[code]
filename = "http://subdomain.sharepoint.com/path/to/file/filename.xlsx"
if workbooks.cancheckout(filename) then
workbooks.checkout filename
set workbookToWorkWith = workbooks.open(filename)
else
‘ Whatever code needs to go here to deal with a non-checkout-able file
end if
[/code]

That’s all fine and good – the file was checked out and opened, and then the program worked with it just fine.

And then I got to thinking: What happens if the file is renamed, or removed from the SharePoint server?

No problem, I thought, Excel VBA has a nice little error catcher thing built into it which can deal with the file-not-found error:

[code]
filename = "http://subdomain.sharepoint.com/path/to/file/filename.xlsx"

On Error goto Fatal
if workbooks.cancheckout(filename) then
workbooks.checkout filename
set workbookToWorkWith = workbooks.open(filename)
else
‘ Whatever code needs to go here to deal with a non-checkout-able file
end if

‘ Do more stuff here

Fatal:
‘ Deal with the situation of a file not found.
‘ If you want to get more in-depth, you can verify the error is actually a file-not-found error as well.
[/code]

Sadly, that didn’t work. I renamed my target file and ran the program to see what would happen, fully expecting the error trap to catch the error and handle it nicely. What actually happened was the program ground to a halt with a Big Ugly Crash and a report of Error #1004.

After searching Google and asking why, exactly, VBA wasn’t catching this particular error, I discovered it was because VBA wasn’t throwing the error in the first place – it was VB, not VBA, so the error trap in VBA doesn’t see the error coming.

Great.

When I searched Google for a solution, using key phrases like “verify a file exists on Sharepoint with Excel VBA”, I ended up in an annoying loop of visiting the same websites over and over again and seeing solutions involving WebDAV or mapping a network drive, and these solutions, when I tried them, didn’t work.

I double-checked with my client, asking if these were an option for him, he explained that no, they were not – he had never been able to get a SharePoint drive mapped with Windows Vista, and Windows 7 was intermittent at best. So, I had to find another solution.

The idea for using the file listing to verify what I’m looking for is there is simple, really:

1. Get the list of files in the directory
2. Get the Nth filename
3. Is this filename the same as the filename I’m searching for? If yes, return true otherwise, keep looking.
4. If we haven’t found the file after searching the entire contents of the folder, return false.

Yes, I realize that O(n) notation would hate this, because it’s linear in nature – it will keep looking until all the files have been checked or until the file is found; so the more files, the longer it will take. I expect there might be a faster way, but for the purposes of what I’m doing, it works just fine.

In my searching for an answer, I stumbled across a few posts which referenced something called XMLHTTPRequest, and began messing around adding a few references to VBA here and there, reading a few documents in the APIs, and swearing. It seemed that nowhere I looked had an adequate solution for ensuring a file existed on SharePoint before trying to check it out and open it. It appeared impossible.

So, I kept looking.
I also kept swearing.

I don’t swear a lot, actually; but when something simple balloons into a mammoth task which is far more complex and involved than it should be, then I swear. Sometimes I think computer engineers do this to us on purpose – take a simple task, add massive amounts of complexity to it, and then sit back and laugh at the programmers who try to make the computer do what they want it to do, sifting through the layers of complexity they’ve added.

For two days, I searched and re-searched. Finally, I came across Sharepoint’s API information, and discovered that if I built a decent SOAP request, I could use something called EnumerateFolder to extract its contents. Bingo!

But wait a minute… what’s SOAP? I researched some more.

It turns out SOAP isn’t really all that bad; it’s simply an XML Schema to use to build queries and such. So, I did some digging, and referenced a few examples, and came up with this:

[code]
Function fileExists() As Boolean

Dim xmlReq As New MSXML2.XMLHTTP
Dim xmlDoc As New DOMDocument

Dim soapEnvelope As String ‘ The SOAP Request
Dim urlString As String ‘ The URL String we send

‘ The urlString references the API subdirectory of the path we’re wanting to enumerate.
‘ You have to set the site url to the folder immediately ABOVE the folder you want to enumerate in
‘ this example.

urlString = "http://" & HOST & PATH & "_vti_bin/SiteData.asmx" ‘ SiteData.asmx is the class EnumerateFolder is nestled within.

‘ Build the XML Soap Thing (envelope?) here:
soapEnvelope = "<!–?xml version=""1.0"" encoding=""utf-8""?–>" & _
"xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" " & _
" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" " & _
" xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"">" & _
"<soap:Body>" & _
" <EnumerateFolder xmlns=""http://schemas.microsoft.com/sharepoint/soap/"">" & _
" <strFolderUrl>" & Replace(FOLDER, " ", "%20") & "</strFolderUrl>" & _
" </EnumerateFolder>" & _
"</soap:Body>" & _
"</soap:Envelope>"

‘ The tag holds the name (pathname) of the folder you want to enumerate. It has
‘ to be a site-specific path. This means if you’re enumerating
‘ http://subdomain.sharepoint.com/path/to/folder To Enumerate, then you need to set URL string to
‘ urlString = "http://subdomain/sharepoint.com/path/to/_vti_bin/SiteData.asmx
‘ and the strFolderUrl tags should look like this when all is said and done:

‘ <strFolderUrl>folder%20To%20Enumerate</strFolderUrl> (which explains the .replace(FOLDER," ","%20") line above


‘ This SOAP envelope is extremely structured and must be exact in order for SharePoint to respond
‘ to it. It took me hours of tweaking to get every little variable and call right.

‘ HOST, PATH, and FOLDER are constants I defined within another module, so my client can configure
‘ the macro as he chooses, sending the program to look wherever he wants it to.

‘ Open the XML Request here, and query the server:

xmlReq.Open "POST", urlString, False
xmlReq.setRequestHeader "Host", HOST
xmlReq.setRequestHeader "Content-Type", "text/xml; charset=utf-8"
xmlReq.setRequestHeader "soapAction", "http://schemas.microsoft.com/sharepoint/soap/EnumerateFolder"

xmlReq.send soapEnvelope

‘ Load the returned XML into a DOMDocument variable for parsing:
xmlDoc.LoadXML xmlReq.responseText

fileExists = parseXML(xmlDoc.FirstChild, False)

End Function
[/code]

Okay, I’m sure some of you are cringing and screaming that I didn’t use the onReadyStateChange callback event thing. You’re right. I probably could have, and doing so may have made the program much more elegant and more “event handler” friendly. However, the XML returned so quickly and simply (once I got the envelope correct) that it was just as easy to take the responseText and plop it into xmlDoc through LoadXML.

So, now, I had a bunch of XML within which was the entire file listing of the directory. From there, it was really easy to write the following recursive traversal function and find (or not find) the filename I’m looking for:

[code]
Function parseXML(xmlDoc As IXMLDOMNode, foundYet As Boolean) As Boolean

‘ Parses the XML data returned from the SOAP request made in the fileExists
‘ Function. This will recursively traverse the XML structure and look for
‘ fields entitled "URL", at which point, it will check to see if the details
‘ file is there.

‘ Once the file is found, this kicks out.

Dim nodeText As String
Dim childNode As IXMLDOMNode

‘ If We haven’t found what we’re looking for, look for it.
‘ Otherwise, skip over everything and kick out.
If foundYet = False Then
If Not xmlDoc Is Nothing Then
If xmlDoc.nodeName <> "Url" Then

‘ Recursively traverse the tree looking for the file.

If xmlDoc.ChildNodes.Length > 0 Then

For Each childNode In xmlDoc.ChildNodes

parseXML = parseXML(childNode, foundYet)
Next childNode
End If

‘ If there are no children, jump to the next sibling and
‘ traverse.

parseXML = parseXML(xmlDoc.NextSibling, foundYet)
Else

‘ AHA! We’ve found a URL Let’s do something about it
‘ Because of the way we loaded the XML, we know it will be
‘ in this format:
‘ FOLDER/FILENAME
‘ So we can erase the FOLDER portion and compare against
‘ FILENAME like this:
nodeText = Replace(xmlDoc.Text, FOLDER & "/", "")

‘ And now, the magic moment, if we’ve found the file, we can set
‘ foundYet to TRUE
If nodeText = FILENAME Then

foundYet = True
End If
End If
End If
End If

‘ Once the tree traversal is complete, set parseXML to foundYet and return.
parseXML = foundYet
End Function
[/code]

I really like recursion. [Why, you ask? Because recursion [Why, you ask? Because recursion [why, you ask? Because recursion [why, you ask? Because recursion [^C]]]]] (sorry) is an elegant way to break a block of stuff, like XML, up into smaller and smaller bits to work with. It reduces the amount of code I need to write and avoids messy things like WHILE, DO, and FOR loops.

So, in any event, what I have just showed you is a way to do The Impossible – grab a file list from SharePoint, without using WebDav or mapping a network drive, and then verify that a file you’re looking for is within that list. From there, you can continue on and finish your open-the-file code like this:

[code]
filename = "http://subdomain.sharepoint.com/path/to/file/filename.xlsx"
if fileExists(filename) then
if workbooks.cancheckout(filename) then
workbooks.checkout filename
set workbookToWorkWith = workbooks.open(filename)
else
‘ Whatever code needs to go here to deal with a non-checkout-able file
end if
else
‘ Whatever code you need to tell the user the file wasn’t found and close down the program in a
‘ friendly and safe way.
end if
[/code]

About Steven Britton

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

    Hi Steven, is there anyway to find out the upload date and the name of the user who uploaded a file on to Sharepoint folder? I want to achieve this using VBA. Thanks a ton.

    • Steven Britton

      VBA is quite difficult to do, because Sharepoint has been made very SOAP-unfriendly. Best approach is C#.