Summing Excel Range Intersects

Ever wanted to add up the value of the Intersecting Sets or Ranges.

I had a couple of Ranges as rowns,
eg Dept1 = Rows A1:N4 , Dept2 = Rows (A5:N6, A12:N16) etc

and then Columns Ranges for Jan, Feb
eg Jan = Column B1:B12, Feb = C1:C12 etc.

I wanted to get the sum of the Intersection between Ranges Dept1 for Jan. To do this use a Space as an operator. eg =Sum(Dept1 Jan)

Whilist everyone knows about the comma as a Union operator not many people have heard of space as an Intersect operator - see "Reference operators" in Excel help for more info

Binding Excel 2007 values using (DIP) Server Document Properties to Sharepoint- Not Simple or Intuitive

We had a requirement to link values entered in Excel 2007 cells so that when they were saved to a Sharepoint Document library those values were seemlessly displayed Document library as fields.

This functionality is handled much more easily by Word 2007 - see these excellent articles (DIP Metadata and Word 2007 Custom Doc Properties) - but Excel 2007 doesnt seem to share this functionality out of the box.

It turns out that there are 3 types of Metadata Properties in Excel 2007 (where WB is the ThisWorkbook object in this example):

• WB.BuiltinDocumentProperties
• WB.CustomDocumentProperties
• WB.ContentTypeProperties

To access this Mata data visually use the Excel "Office button" -> Prepaire -> Properties menu. Choosing "advanced properties" gives you access to both add new custom properties and the built in properties. You can assign fixed text values to them or link them to named ranges within the sheet, however you cannot get to the Server Document Properties from this dialog.

ie the XL-> Custom Properties GUI only gives you access to the following Property sets (and of course these aren't what you need for Sharepoint)

• WB.BuiltinDocumentPropertiesSet
• WB.CustomDocumentPropertiesSet

To update the data which will feed back to Sharepoint fields you need to use code to get to the WB.ContentTypeProperties object which has a type of "MetaProperties".

The WB.ContentTypeProperties object is a collection which can be iterated through and each field has an ID and Name and a Value. The ID is an internal value and seems to Map to the same internal name as that of the Sharepoint field it is connected to.

{Note: the internal field names can lead to some confusion such as where a field with a Name of "col1" seems to create an internal ID name of "_x0063_ol1" and a field with a Name of "Project Lead Finance" has an internal ID name of "Project_x0020_Lead_x0020_Finance". This is normal Sharepoint practice!}

Using macro code to set the values of a MetaProperty in this way seems to trigger an event in the Server Document Properties DIP (Document information Panel) to update the field value to the new value you've set. {Note: The DIP is an InfoPath based piece of XML which displays fields in Office 2007 applications. It may be possible to code against it to achieve a more automated link to native Excel custom properties but I haven't had a chance to look at that yet}

Below is a very simple piece of code to set a Server Document Property to the value of cell A1:

Sub SetServerProperties()
Dim WB As Workbook
Set WB = ThisWorkbook
For Each Prop In WB.ContentTypeProperties
If Prop.Name = "Project Lead Finance" Then
Prop.Value = Range("A1").Value
End If
Next Prop
End Sub

Using macro code has the disadvantage of Excel documents having to be saved as XLSM documents and I am sure there must be a way to trap an update event of an standard Excel Custom property using something like VSTO, however that is for another post.

{Note: for Excel 2003 just updating the fields in WB.CustomDocumentPropertiesSet before a save does seem to pass them back to Sharepoint ok}

My thanks to an article published by Sadalit Van Buren who pointed me in the right direction with this article A Matter Of Degree: XML Mapping of Document Properties with Excel and SharePoint - Not Simple or Intuitive

Finding the last data cell in Excel 2007

Using VBA use the Range.SpecialCells Method such as

RCount = ActiveCell.SpecialCells(xlLastCell).Row
CCount = ActiveCell.SpecialCells(xlLastCell).Column
ActiveCell.SpecialCells(xlLastCell).Select