SharePoint 2010

SharePoint 2010’s Ribbon offers some pre-set styles and mark-up styles for use when editing text. How these styles display is impacted by the Theme which is used. When customizing a theme, be aware of the following relationships:


Text/Background Dark 1 = normal text

Text/Background Dark 2 = Mark-up Styles, Headings 1-4 (the default is a dark blue colour of the default top band in SharePoint 2010)


Accent 1 = Colour Heading 1

Accent 2 = Colour Heading 2

Accent 3 = Colour Heading 3 and Caption style

Accent 4 = no effect

Accent 5 = Colour Heading 4 and Comment

Accent 6 = Highlight



If you look at the folder structure of a standard, out-of-the-box SharePoint site which has not been themed in any way, you will see the following structure, when opened with SharePoint Designer:

Once you customize an existing theme, a new entry will be created in the Themes Gallery, called Themed:

And within this, a folder grouping all the images and CSS files which are used within a custom theme:

Some important aspects of how your site looks can be easily changed by modifying an aspect of a theme. For example, a SharePoint theme allows the colour of hyperlinks used within the content area of pages to be changed. However, if you want to modify the behaviour of links when hovering with a mouse pointer, then this can only be done in the corev4.css file, a copy of which is saved in the Theme Gallery once a theme is customized. So to give a concrete example, if you want link text to be dark blue with no underline and to change to orange when you hover over the link, you would need to do the following:

  1. Customize your theme with the required link colour and followed link colour.
  2. Edit the COREV4.CSS file in the Theme Gallery. The file will be created with a reference number, in this case it was 8A0ABD2F, so the CSS file is called COREV48A0ABD2F.CSS. Search for ms-rtestate and change text-decoration to none and add a colour for the hover as follows:

    .ms-rtestate-field a:hover





  3. Save the file and refresh your page to see the results.

Be aware that if you further change the Custom theme in any way, the COREV4.CSS will be copied again to the Theme Gallery, so you will have to redo any changes to the CSS file.

Reference Screenshots

Text/Background Dark 1 = Bright Green (Default #000000)

Text/Background Light 1 = Bright Green (Default #FFFFFF)

Text/Background Dark 2 = Bright Green (Default #1F497D)

Text/Background Light 2 = Bright Green (Default #EEECE1)


Accent 1 = Bright Green (Default #4F81BD)

Accent 2 = Bright Green (Default #C0504D)


Accent 3 = Bright Green (Default #9BBB59)

Accent 4 = Bright Green (Default #8064A2)


Accent 5 = Bright Green (Default #4BACC6)


Accent 6 = Bright Green (Default #F79646)


Hyperlink = Bright Green (Default #0000FF)

Hyperlink Followed = Bright Green (Default #800080)


SharePoint 2010 is an excellent document management system, which can also be used as intranet. In some cases, organizations may want something simpler to manage content yet need to link to elements of more sophisticated content held in documents, which may well be located in SharePoint. The easiest means to do this at a very basic level is to use the ReST protocol to pick up images, such as Charts in this example, which may probably be most conveniently hosted in Excel files.

The file below is held in a document library in SharePoint 2010 and updated by users via the browser.

Figures can be edited simply and the chart can be kept up to date.

Once the file is saved, (in this case, each cell edit in Excel is a save action), then the Chart will reflect the changed data.


In order to use the chart in an external content management system such as Drupal, the chart needs to be inserted as an image tag in a Drupal page as follows:

<img src=”http://win-sqpsjc4uhjv/sites/drupal/_vti_bin/ExcelRest.aspx/rest/Book1.xlsx/Model/Charts(‘Chart%201&#8217;)”>

The text format in Drupal must be set to Full HTML as follows:

The Chart image will then be displayed directly in the Drupal page. In this simplified version, the SharePoint site hosting the Excel file has been has been set up with read-only Anonymous access enabled. In the real world a more sophisticated configuration would be required to manage authentication between Drupal and SharePoint. This will be the subject of a blog article in the near future.


A soon as the source changes in SharePoint, only a page refresh is required to see the current data, as follows:


In order to connect to an Excel file in a SharePoint 2010 and access or manipulate elements via web services, the file must not contain any unsupported features, in terms of Excel Services.

This can be demonstrated via a simple test program which interrogates SharePoint 2010 via a web service and returns all named ranges in an Excel file.

Where the file contains no unsupported features, the named range is returned:

This web service connection in SharePoint 2010 now functions even if there are macros in the file, and it is saved with an xlsm extension, unlike in SharePoint 2007.


However, once there is an unsupported feature in the file, in this case a Drop-Down List for data validation, the file can no longer be accessed via web services.



To download the test program, click here.

REST can be used to connect to named ranges, charts, tables and pivot tables as well as individually defined cell contents using a simple URI via Excel Services. In the following Excel file, held in a normal document library in SharePoint 2010, each element can be referenced.

A single cell – server/site/_vti_bin/ExcelRest.aspx/xl/1.xlsx/Model/Ranges(‘Sheet1!b2’)

A Named Range – server/site/_vti_bin/ExcelRest.aspx/xl/1.xlsx/Model/Ranges(‘Range1’)

A Table – server/site/_vti_bin/ExcelRest.aspx/xl/1.xlsx/Model/Table(‘Table1’)

A Chart – server/site/_vti_bin/ExcelRest.aspx/xl/1.xlsx/Model/Charts(‘Chart%202’)

A Pivot Table – server/site/_vti_bin/ExcelRest.aspx/xl/1.xlsx/Model/PivotTables(‘PivotTable1’)

However, as soon as an Excel file contains elements which are not compatible with Excel Services such as data validation or password protection, then REST no longer functions for that file and no elements can be accessed.

If no element is specified, and atom feed is returned listing all named elements which can be accessed using REST.


Macro-enabled Excel files, with the extension .xlsm, though not compatible with Excel Services, may still be displayed and may still be accessible via REST.

Unsupported Features in Excel Services

 Information Rights Management (IRM) protection.

 ActiveX controls.

 Embedded smart tags.

 PivotTables report with multiple consolidation ranges.

 External references (workbooks that contain links to other workbooks).

 Workbooks saved in formula view.

 XML expansion packs.

 XML maps.

 Data validation.

 Query tables, SharePoint lists, Web queries, and text queries.

 Workbooks that reference add-ins.

 Workbooks that use the RTD() function.

 Workbooks that use workbook and worksheet protection.

 Embedded pictures or clip art.

 Cell and sheet background pictures.

 AutoShapes and WordArt.

 Ink annotations.

 Organization charts and diagrams.

 DDE links.

Features That Are Not Displayed

Workbooks that contain one or more of the following features will load in Excel Services, but the features will not be displayed. The features won’t be removed from the file, so the next time you open the file in Excel 2007, they will be displayed again.

Split and frozen panes

Headers and footers

Page layout view

Cell patterns


Microsoft SQL Server Analysis Services’ member properties in ScreenTips

Some cell formatting, such as diagonal borders and border types not supported by HTML

Excel Services displays all charts that were created using Excel 2007. However, depending on how the charts are designed, some minor visual differences occur when some charts are displayed using Excel Web Access. For example, Excel Web Access cannot show new chart effects such as shadows, bevels, and reflections.

Features with Limited Interactive Support

Certain features that are interactive in Excel 2007 are not completely interactive in Excel Web Access.

PivotChart reports

PivotChart reports are not interactive when displayed in a browser. You cannot filter a PivotChart report directly, but if you interact with the PivotTable report that supplies data for the PivotChart report, the PivotChart report updates accordingly. This means if you filter the PivotTable report, the PivotChart report updates to show the filtered data set.

PivotTable reports

You can sort, filter, expand, and collapse data in PivotTable reports in a browser, but you cannot use SQL Server Analysis Services actions, add or remove fields, or rearrange fields.

Go To

In a browser, you can use the named range object view or the provided navigation controls to move around a workbook. However, there is no Go To feature.

Zoom, minimize, and maximize

Users cannot zoom, minimize, or maximize worksheets when viewing them in the browser.

Switching to page layout view

Page layout view is a new view in Excel 2007. You cannot switch to this view when viewing a workbook using Excel Web Access. Page layout view is designed to facilitate printing. If you want to print a workbook, it is best to use Excel 2007.

Goal Seek and Scenario Manager

You cannot use Goal Seek or Scenario Manager when viewing a workbook using Excel Web Access.


You cannot audit formulas using trace precedents, trace dependents, show formulas, and so on.

Changing a workbook’s calculation mode

You cannot change a workbook calculation mode using Excel Web Access.

Watch window

You cannot use the Watch window when using Excel Web Access.

From what I have seen so far of the SharePoint 2010 Beta, we are going to have almost limitless possibilities for improving the user experience. The new ribbon will become an important tool for managing user navigation and the new dialog framework is a very neat approach. Master pages now understand when they are being displayed in what seems to be a modal dialog box, and so do not display full navigation in this context. All this is achieved quite simply using CSS and JavaScript (it is stunning in one sense to think that it has taken us so long to get this far, using such standard tools). Some things in the new SharePoint 2010 universe are much trickier to achieve than in the old MOSS environment however. Take creating a link in the Quick Launch Menu to the NewForm.aspx in a simple list. This used to be an easy way of directing users and cut out a click or two in the process. Well, you can still use the same procedure and the result functions, but you do not get the now familiar dialog experience.

The form opens embedded in the page. While it is useful that the site is still able to function, this undermines somewhat the very smooth new user experience. Is there a way to open a new list form in the modal dialog format?

So far, the only means I have found to achieve this is by creating a feature which creates a new button on the Ribbon which can then open NewForm.aspx. I have adapted code described by Jonathan Frost on his blog here.

Whereas he is adding a new button to the Documents tab of a Document Library Ribbon, I want to add a button to the New tab of a Generic List. To do this you will need to change Jonathan’s code such that the RegistrationId is set to 100 for a generic list (not 101 for a Document Library) and you will need to set the Location and ID in the CommandUIDefinition to Ribbon.ListItem.New.Controls._children

As follows:

The result is a button which appears as follows once the feature is implemented:

The NewForm.aspx opens in a modal-type dialog consistent with the SharePoint 2010 user experience:

The disadvantage with this approach is that you will get the New Project button appearing on every Generic list and it will always open the NewForm.aspx for the list referenced in the URL of your code. So this workaround takes us some of the way: it is useful but not yet 100 per cent functional.

The new validation options with SharePoint 2010 will require some pretty nifty formulas. An example: supposing you have a form which looks up client details from an external database, but you also need to be able to enter a new client if it is not already in the list. In this case you need a form validation formula which checks to see that either one or other of the columns are not blank.

You can do this by using a Form Validation formula in SharePoint 2010 as follows:

  1. Create a column called Client which looks up data from an external database.
  2. Create a simple text column called New Client which will be used to enter a new client name, if the client does not already exist in the database.
  3. Use the following Form Validation formula to check that either the Client column or the New Client column has been filled out.

=OR(NOT([New Client]=0),NOT([Client]=0))

In the SharePoint 2010 Beta, if you implement Document Sets within a document library, then create a document set and save the site as a site template, when you create a sub-site from the template, the document set will not be recognized as such but will appear as a folder instead. This looks like a bug in the Beta.

The answer is here:

The template is still there but hidden and the Site Directory links scan still forms a part of Central Administration, you will just have to set up a sub-site to act as your site directory by hand. Remember to activate the Publishing feature if you did not use a Publishing template for the site collection.

« Previous PageNext Page »