Given that I spent a ridiculously long time trying to get an InfoPath form to interact with a spreadsheet published through Excel Services, I thought this was a suitable subject for a blog post. If I stop someone else feeling like they want to beat themselves to death with their own keyboard, I will consider it a job well done.
Assume the following, much simplified, scenario.
Step 1: A user fills out a field on an InfoPath form. Let’s call this Field_A.
Step 2: InfoPath sends this value to an Excel spreadsheet and puts it in a cell. Let’s call this CellA.
Step 3: Excel does some calculation based on this value and puts the result in another cell. Let’s call this CellB.
Step 4: Excel then sends this value back to InfoPath, which puts it in a field. Let’s call this Field_B.
You’ll probably want to call your fields and cells something much more meaningful based on exactly what it is you’re trying to do. Depending on your actual situation, you may want to do multiple lots of either steps 1 and 2, or steps 3 and 4, or both. This post is just to help with the principles.
Preparing the Fields
The first thing you need to do is to set named ranges in Excel. In your spreadsheet, click on CellA and in the name box, enter whatever it is you wish to call this cell. Then do the same with CellB. Make sure every cell you want to either put data into or read data out of through Excel Services is a named range and remember what you’ve called them all.
Once you’ve named your ranges, publish the spreadsheet to Excel Services, saving the file in a SharePoint library. Go to the library and right click on the name of your spreadsheet. Click “copy shortcut.”
Now go to your InfoPath form. I’m going to assume you’ve already laid up the form and inserted whatever fields you want. You’re going to have to add a few more fields. Add a field for range_name_A and set the default value to be whatever you’ve called CellA. Add another for range_name_B and set the default value to be whatever you’ve called CellB. Add a field for sheet_name and set to default value to “Sheet1” or whatever the sheet is called that contains your CellA and CellB (if the two cells are in different sheets, you will need a different sheet_name value for each of them).
Creating the Data Connections
You need to create a new data connection. Using the wizard, say you want to create a new data connection to receive data and click the option for a web service. You will be asked for the location of the webservice. Enter http://<servername>/<sitename>/_vti_bin/excelservice.asmx?WSDL where <servername> is the name of your server and <sitename> is the name of the site where your spreadsheet is.
On the next page of the wizard, you’ll be asked to choose an operation from a list. Choose OpenWorkbook.
On the next page, you’ll be asked to set parameters. Click on the one called tns:workbookPath and then click the “Set Value” button. Click into the “sample value” field and then click ctrl-v. This should copy into the field the exact url of your spreadsheet. If it doesn’t, you can manually type in the address but be careful not to make any typing errors.
Click next a couple of times until you get to the last screen of the wizard. Uncheck the box next to “automatically retireve data…” and then click “finish.”
That’s data connection number one. This will open up the spreadsheet you will be using. Now you need to create the data connection to send data to Excel. Choose to create a new data connection, this time to submit data.
Enter the same address for Excel Services. This time, choose the operation SetCellA1. In the next screen, you will be asked to set some parameters. Double click on sessionId. A window will open up to allow you to set this value to equal the value of one of your fields. From the drop-down list, choose the data connection you created to open the workbook, expand the folders and select OpenWorkbookResult. Next, double click on sheetName and choose the sheet_name field you created. Similarly, for rangeName, choose range_name_A. For cellValue, you will want to choose your Field_A. Now complete the wizard.
This data connection will take the value entered into Field_A and submit it via Excel Services, setting the value of CellA in your spreadsheet to be the same value.
Now we need to create the data connection to bring in the calculated value from CellB. Choose to create another new data connection. This time choose to receive data from a webservice. Enter the same webservice address. The operation this time is GetCellA1. Ignore all the parameters and just click “next” until you reach the final screen. Uncheck the box to automatically retrieve data and then finish the wizard.
There’s only one more data connection to go. Create a webservice connection to submit data and enter the same webservice address. Chose the operation CloseWorkbook. On the parameters screen, set the sessionId to equal the field OpenWorkbookResponse from your open workbook data connection. Click next and then finish the wizard.
You now have four data connections. One opens the workbook. One submits the value of Field_A to CellA. One retrieves the value from CellB. The final one closes the workbook again.
Querying the Data
You need to attach a rule to Field_A. Create a rule with no conditions so that it will activate when the value in the field changes (you could also do this using a button). This rule should consist of several actions.
Action 1: query using a data connection. Run the Open Workbook data connection.
Action 2: submit using a data connection. Run the SetCellA1 data connection.
Action 3: set a field’s value. In the fields for your GetCellA1 data connection, set the query field sessionId to equal the OpenWorkbookResponse field from your Open Workbook data connection.
Action 4: set a field’s value. In the fields for your GetCellA1 data connection, set the query field sheetName to equal your sheet_name field.
Action 5: set a field’s value. In the fields for your GetCellA1 data connection, set the query field rangeName to equal your range_name_B field.
Action 6: query using a data connection. Run the GetCellA1 data connection.
Action 7: submit using a data connection. Run the Close Workbook data connection.
So now the form is set up to send and retrieve the appropriate fields.
Finishing Off
This is the simple part. Go to Field_B and set the default value to equal GetCellA1Result from your GetCellA1 data connection.
Now your form should take the value entered in Field_A, send it via the data connections and display the CellB value in Field_B.
Just to check it all works, preview your form. With so many fiddly parameters to set, it’s easy to mis-type something. Fortunately, InfoPath’s errors will tell you which data connection is causing the problem and, often, even where the problem lies. For example, if it says that the sessionId is invalid, you’ve mistyped something entering the sessionId for that data connection.
I hope this post is a clear guide on how to get InfoPath and Excel to talk to each other. Feel free to leave me a comment either if you find this useful or if you’re still bewildered by the process.
First in a new series of demo videos introducing InfoPath 2010. In this demo, I show the creation and publication of a simple electronic form.
It’s time to have a play.
The public beta has launched and now you can get hold of Office 2010 and SharePoint 2010. You can download it from www.microsoft.com/2010 and try out some of the new features such as:
· Conversation threading in Outlook
· Improved rules management in InfoPath
· The ribbon UI in SharePoint
· InfoPath “quick” publish functionality
· The Business Connectivity Services for SharePoint (a much improved version of the BDC)
· The new SharePoint Workspace
· Tighter integration between SharePoint and Office (including InfoPath list editing)
· New InfoPath controls
· Slicers in Excel
· The Office “backstage” tab
Personally, I’m really excited about some of the major improvements in InfoPath 2010. I think this means I’ve spent way too much time dealing with forms in the past if I get this excited about a piece of forms creation software!
There are spaces available on courses Microsoft are putting on for partners around SharePoint for web content management. The training is a four-day bootcamp for developers, aimed at IT professionals who currently work on MOSS internal deployments who want to learn web content management.
The course costs only £150 and is run in London from 15th to 18th December, 9am-5:30pm.
Click here to register.
There are also two one-day pre-sales courses on the features, benefits and capabilities of MOSS WCM for technical sales people. These courses are in London on 19th and 20th November, 9:30-5pm.
Click here to register.
There are some new web content management features that allow you to track what content is being accessed by users in Microsoft SharePoint Server 2010. This information can be accessed and analysed by the SharePoint administrators, but it can also be surfaced to the end users using the out of the box web analytics web part. This web part can be inserted as easily as any other web part. It will then display to users the most frequently accessed content from a list, site or library that you choose.
If you buy MOSS 2007 with Software Assurance now, you’ll get the upgrade rights to 2010 when it’s released. There’s no better time to buy!
Microsoft SharePoint Server 2010 includes the ribbon user interface, giving a much more consistent experience when used alongside Office. There are several advantages to the ribbon UI. One is that it makes it a lot clearer whether or not a user has certain capabilities. In MOSS 2007, if a user doesn’t have the permissions to edit a page, they won’t see the option to edit it. If they can’t see the edit option, they can’t be sure whether it’s because they’re not allowed to or because they just don’t know where the option is. In MSS 2010, they will see controls greyed out so it will be clear that they cannot use those features.
The ribbon is contextual. When you click on an item, new tabs appear related to that item so you see the relevant controls and options. You're not confused by controls that aren't relevant. The administrator can disable tabs of the ribbon, or remove the ribbon entirely where appropriate.
The ribbon is customisable and extensible, so Microsoft partners can add their own code and capabilities and use the standard UI to display them.
If you buy MOSS 2007 with Software Assurance now, you’ll get the upgrade rights to 2010 when it’s released. There’s no better time to buy!
With MOSS 2007, you can use metadata and official taxonomy to organise your documents. In Microsoft SharePoint Sever 2010, you still have all those capabilities, but you also get the ability to be less formal and have users define their own tags for SharePoint content. Users can quite simply type in new tags for documents and other content. There is also the facility for prompts and suggested tags, to help keep the terminology consistent. Administrators can track the tags that are being used and, if they so wish, promote particular tags from the folksonomy to the taxonomy.
So SharePoint 2010 offers the capabilities of being both structured and unstructured in its approach to content management.
If you are interested in a particular topic, you can subscribe to a tag and be alerted when new content is added. You can even use tags in order to find people who are experts on a subject using some of the new social networking capabilities.
If you buy MOSS 2007 with Software Assurance now, you’ll get the upgrade rights to 2010 when it’s released. There’s no better time to buy!
There are several short-comings with designing workflows in the current version of SharePoint Designer (but there’s always Visual Studio and some excellent third-party tools to combat those short-comings). One of the problems with SharePoint Designer workflows for MOSS 2007 is that you can’t reuse them. If I were to design a workflow on a document library and wanted to use that exact same workflow on a different document library, I would have to start all over again.
With the new SharePoint Designer for Microsoft SharePoint Server 2010, you just choose the option to create a reusable workflow in your initial design. Then you can attach the same no-code workflow to any list, document library or content type where it’s appropriate. You can also take an existing reusable workflow, use it as a template and make any necessary changes. This should cut the time needed to design multiple workflows considerably.
If you buy MOSS 2007 with Software Assurance now, you’ll get the upgrade rights to 2010 when it’s released. There’s no better time to buy!
A lot of people, when they first get SharePoint, instantly want to make it not look like the out of the box SharePoint. In Microsoft SharePoint Server 2010, there are a lot more themes to choose from.
When you create a team site, there is a “getting started” web part with some useful links, including one for changing the site theme. Click on this link and you get taken to a long list of the out of the box themes, with a nice display to let you know what you’re getting. Below this in the settings page, is a section for customising the theme. If you want, you can use the out of the box theme except for one or two colours which you set yourself. Similarly, you can change the font of headings or body text with just the click of a button. You don’t have to go into SharePoint Designer or create new style sheets.
There is also a little preview button, so you can check what you’re getting before you make up your mind.
As well as this, it’s now much easier to change the logo associated with a site or site collection. When you edit a site, you get given the option to choose a logo. It takes just a few seconds to put a new logo on your site and its subsites.
If you buy MOSS 2007 with Software Assurance now, you’ll get the upgrade rights to 2010 when it’s released. There’s no better time to buy!
MOSS 2007 came with a few out of the box workflows to do tasks which are common in a huge range of organisations. One of these was the approval workflow, provided out of the box because the majority of companies have an approval procedure of some form. This is a very easy to use workflow that can be run on any content type allowing someone to approve or reject a document for publication. The problem was that no two companies do business in exactly the same way.
The out of the box approval workflow is very generic. Companies that wanted something slightly different would have to recreate the entire thing. With Microsoft SharePoint Server 2010, you have the ability to use the out of the box workflows as a template and customise them to get the precise workflow needed for your company policies. These changes can be made quite simply in SharePoint Designer 2010 with no need for custom code.
You can now make the out of the box workflows adapt to your needs without any coding and without having to start from scratch.
If you buy MOSS 2007 with Software Assurance now, you’ll get the upgrade rights to 2010 when it’s released. There’s no better time to buy!
Microsoft are making some changes to the naming of things in SharePoint 2010, the most obvious being the dropping of the word “Office” from the main product name. A rather larger change of name is Windows SharePoint Services becoming SharePoint Foundation.
Confusing though this change may be, I think it’s a good one. I’ve seen a lot of people get confused over the differences between WSS and MOSS. Without seriously digging into the features, it’s hard to understand why they should pay for MOSS, rather than just go with the licenses for WSS they already have. The new name makes it clear from the outset that SharePoint Foundation is the foundation level for a SharePoint solution. It’s there to be built on top of by the main product.
As with WSS and MOSS, SharePoint Foundation will provide the basic functionality necessary for a SharePoint deployment and Microsoft SharePoint Server 2010 will build on top of it to provide rich and valuable solutions to business problems.
SharePoint Foundation provides the ground-level capabilities for sites, communities and content management. It also gives a small amount of features for search and composites. It may be enough to solve business problems for some companies but for others, the depth of features provided by Microsoft SharePoint Server 2010 standard or enterprise will be required.
If you invest in Microsoft Office SharePoint Server 2007 with Software Assurance now, you’ll get the upgrade rights to Microsoft SharePoint Server 2010 when it comes out.
I'm continuing my series of posts highlighting some of the new features in SharePoint 2010 by talking about one of the new document management features.
In Microsoft SharePoint Server 2010, it’s a lot easier to have document management policies that are broken into stages. In MOSS 2007, if you wanted to have a policy that had several different steps, you would have to write the custom workflows to handle it. With SharePoint 2010, when defining your policy, you just have to click to add new stages and set the policy rules for each one.
You can then use a combination of the out of the box policy actions and your own custom workflows. Simple!
If you buy MOSS 2007 with Software Assurance now, you’ll get the upgrade rights to 2010 when it’s released. There’s no better time to buy!
I'm continuing my series of posts highlighting some of the (many) new features which are going to be coming in Microsoft SharePoint Server 2010.
The content management capabilities of SharePoint have been extended, allowing you to group documents together as a document set. In MOSS 2007, there are quite a lot of useful document management features around metadata, policies, workflows and so on. In Microsoft SharePoint Server 2010, those features have been extended to also apply to groups of documents. So you can gather together, for example, a Word document, Excel spreadsheet and a PDF and define metadata on all three at once, have workflows act upon the entire set and define policies that work on the three files together.
Document sets can contain any type of file and all the usual document management features can be applied to the set as a whole, or to the individual files within the set. So you can have some metadata fields which apply to the whole set, but have others filled out for the specific documents within them. You can do the same with security, permissions and content management policies.
This makes it so much easier to group related documents together.
If you buy MOSS 2007 with Software Assurance now, you’ll get the upgrade rights to 2010 when it’s released. There’s no better time to buy!
Continuing my series of posts about new features in Microsoft SharePoint 2010, I’d like to talk about content rating. This is a feature that was much demanded in MOSS 2007. So many people wanted it, that the code for content rating was included in the Microsoft Office SharePoint Server 2007 community kit.
Microsoft listened to the feedback about MOSS 2007 and have included this feature out of the box in SharePoint 2010. Now you can quickly and easily rate SharePoint content, discussions, documents and list items out of five and see the results of other people’s ratings displayed as a simple graphic.
It’s a simple click to provide your rating and now everyone can see what content is liked, as well as sorting and filtering by the average rating.
If you buy MOSS 2007 with Software Assurance now, you’ll get the upgrade rights to 2010 when it’s released. There’s no better time to buy!
Microsoft SharePoint Server 2010 builds on the already strong foundations of MOSS 2007. The capabilities and value of 2007 are carried forwards, but with additional functionality and value added on top. If I were to explain all the things you can do with SharePoint out of the box, I would probably be spending my entire waking life writing blog posts and still be typing this time next year.
So, instead, I’m going to highlight some of the new features that are available in SharePoint 2010 that make it a little different from the previous version. These features are chosen because I think they’re useful, cool, valuable, interesting or all of the above.
The first feature I’d like to highlight is phonetic search. How many times has someone introduced themselves to you and you’ve got the spelling of their name completely wrong? The English language is notoriously bad for having different collections of letters potentially spelling the same sound. (If you don’t believe this, look up the joke about fish being spelt ghoti).
You can now do people search in SharePoint based on the sound of someone’s name, not just the spelling. Gone are the days of typing in half a dozen spelling variations to try and find one person.
If you buy MOSS 2007 with Software Assurance now, you’ll get the upgrade rights to 2010 when it’s released. There’s no better time to buy!
