425-882-1032 info@3sharp.com

Update: The first of many bug fixes is in place (changed the namespace declaration for the current form and modified list my column indexers) – Thanks to Michael O’Donovan of MS for pointing out the error of my ways. 

This morning, I spent waaay to long talking about the first demo I gave during my Excel presentation at the conference.  Tonight, mainly because I want to get home, have a glass of wine, and watch whatever movie has come from my favorite company e.v.e.r. (besides 3Sharp, of course), I’m going to make this one quicker.  — At least that’s the goal.

Anyway, If you remember the demos, the plot starts roughly where we left off with our last demo, only now, I’ve published the form up to SharePoint.  I’ll leave that up to you, intrepid reader, to figure out how to do this (hint – you’ll need to modify the PI again).  If I get even a single comment asking for more detail, I’ll give it ;^)

For this post, however, I want to build a list in Excel and programmatically import the XML from my forms hosted in the SharePoint list. 

Step 1: Map schema and create list

For the demo, I had done this already manually instead of with code.  As a bonus, I’ll give you the code to do this as well (so much for keeping this post short and sweet).  Here’s the code to map the schema:

Disclaimer – This code is not tested and could very well send me your credit card numbers along with the last 10 web sites you’ve been to (you decide which would be worse info for me to have).  Take a look at it (as with all sample code) and make sure it’s worthy to run on your machine!

Dim oMap As Excel.XmlMap
Dim filepath, rootElementname as string
filepath = “c:john’s officedev demoFormSchema.xsd”
rootElementname = “Order”
oMap = ThisWorkbook.XmlMaps.Add(filePath, rootElementname)
oMap.Name = “InfoPathMap”

Now that we have our map, we need to create our list and bind it to the map:

Dim SCHEMA_NAMESPACE = “xmlns:my=’http://schemas.microsoft.com/office/infopath/2003/myXSD/2005-02-01T02:42:07’“
Dim xPathPrefix As String
Dim oList As Excel.ListObject
Dim _ActiveWorksheet As Excel.Worksheet = ThisWorkbook.ActiveSheet  ‘(this was a global defined on startup in my demo.)


oList = _ActiveWorksheet.ListObjects.Add

xPathPrefix = “/my:Order/my:group2/my:LineItems/my:Item/”

‘Set XPath for each column
oList.ListColumns(1).XPath.SetValue(oMap, xPathPrefix + “my:Name”, SCHEMA_NAMESPACE, True)
oList.ListColumns(2).XPath.SetValue(oMap, xPathPrefix + “my:Cost”, SCHEMA_NAMESPACE, True)
oList.ListColumns(3).XPath.SetValue(oMap, xPathPrefix + “my:Quantity”, SCHEMA_NAMESPACE, True)
oList.ListColumns(4).XPath.SetValue(oMap, xPathPrefix + “my:Total”, SCHEMA_NAMESPACE, True)
oList.ListColumns(5).XPath.SetValue(oMap, “/my:Order/my:Details/my:Customer”, SCHEMA_NAMESPACE, True)

All we need to do is import our XML into the map!  Of course, when I say “inTO the map,“ what I really mean is “THROUGH the map.“  What we’ll actually be doing is importing through the map and into the lists bound to our map.  The data contained in any elements that haven’t been bound to a list will end up in the same place my socks go when I do the laundry. 

Private Sub ImportForm(ByVal url As String, ByVal map As Excel.XmlMap)
     map.Import(url, False
End Sub

Btw, the boolean simply lets Excel know if I’m importing over the data (True) or appending the data to the stuff already in my list (False).

Up to this point, things have been pretty easy (I’m assuming you didn’t have a hard time debugging my code to figure out what I left out).  They’re going to get a bit more complicated as we have to start playing with SharePoint Web Services.  Because it is 7pm and I realized I have to swing by the grocery store, I’m going to gloss over some stuff, but as always, if requested, I’ll drill in as much as you want :^)

For our case, we have to make two separate calls to the SharePoint web services.  The first call is to discover the GUID for the particular list we are looking for.  The second call is to ask SharePoint for the data for all of the list elements.

First off, I needed to create a web reference to the SharePoint web services.  I gave my webservice a name of SharePointLists.  When you see SharePointLists showing up in my code down below, understand that it is just a reference to this web service.  Here’s the URL I used for my particular SharePoint site which is located at http://fabrikam1/sites/ExcelDemo.  It will, of course, be different for you:


My web reference has been set up, so I’m going to ask SharePoint for data about all of its lists for the site.  It is going to return a big blob of XML that I need to go through to find the list corresponding to the name I’m looking for.  Once I find that list, I just need to get the GUID from the ID property.

Private Function GetSPListGuid(ByVal listName As String) As String
Dim SPList As New
Dim listGUID As
Dim oLists As
Dim oListInfo As

SPList.Credentials = System.Net.CredentialCache.DefaultCredentials
oLists = SPList.GetListCollection
For Each oListInfo In
If oListInfo.Attributes(“Title”).Value = listName
End If
End Function

For those astute XML ppl out there, I certainly didn’t have to loop through the XML I received from my web service call.  I could have just as easily written a single XPATH query, I just didn’t.

At this point, I need to find the URL of each file in the SharePoint list.  The XML that will be returned to me has a node for each list item with a bunch of attributes describing all of its meta data.  The attribute that I’m looking for is called ows_FileRef and it has all of the url for the file except for the servername (in my case http://fabrikam1). 

Note that this function calls the function I just wrote above (GetSPListGuid).  Also note that I’m manipulating the URL I find in the ows_FileRef.  Specifically, I’m stripping out a pound sign (#).  This is because SharePoint actually gives us some info we really don’t care about and I need to clean it out so we have the pure URL.  PLEASE check out the SharePoint SDK (for WSS) if you want to do stuff like this as our SharePoint guys will probably be upset with me for not running this code by them.  In other words, code is probably not the awesomest way to do this :^)

I took the liberty of bolding the interesting stuff:

Private Sub ImportFormsFromSharePoint(ByVal serverName As String, ByVal listName As String)

Dim SPList As New SharePointLists.Lists
Dim listGUID As String = “”
Dim filePath As String = “”
Dim oNode As Xml.XmlNode
Dim oItem As Xml.XmlNode
Dim viewName As String
Dim query As System.Xml.XmlNode
Dim viewFields As System.Xml.XmlNode
Dim rowLimit As String
Dim queryOptions As System.Xml.XmlNode
Dim startLocation As Integer
Dim iLength As Integer
     SPList.Credentials = System.Net.CredentialCache.DefaultCredentials
listGUID = GetSPListGuid(listName)
     If listGUID “” Then
          oNode = SPList.GetListItems(listGUID, viewName, query, viewFields, rowLimit, queryOptions)
          For Each oItem In oNode.SelectNodes(“//*[local-name()=’row’]”)
               startLocation = oItem.Attributes(“ows_FileRef”).Value.IndexOf(“#”)
               filePath = serverName + oItem.Attributes(“ows_FileRef”).Value.Substring(startLocation + 1)
     End If
Catch ex As Exception
     MsgBox(“Error getting files from SharePoint: ” & vbCrLf & ex.ToString)
End Try

oNode = Nothing
oItem = Nothing
End Sub

Btw, the ImportForm function simply calls oMap.Import(filePath, False) as described up above.

To summarize the code up to this point, we are mapping a schema and creating an XML list, finding the appropriate Form Library (list) on SharePoint, and importing all of the forms, one-by-one into Excel.  If you’ve been playing with this stuff, you might have noticed that Excel gives you the ability to right click on an XML List and selecting XML, Refresh XML Data. If you do this, Excel will automatically refresh the data the list is bound to.  In our case, unfortunately, it will clear out our list and reimport the last form we loaded.  Since we did our imports sequentially, Excel has only remembered the last form :^(

I’m out of time tonight, but I’ll give you a hint about what you need to do:
Excel has given us a few events, BeforeXMLImport, AfterXMLImport, BeforeXMLExport, AfterXMLExport.  Do some thinking about what you could do in the BeforeXMLImport event to re-import all of the forms instead of just the last one.

Have fun coding!