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.
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.