Information about Microsoft SharePoint Server 2010, SQL Server 2012, Business Intelligence and Office 2010.
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.
thank you for your awesome post!! Exactly what i needed.
Unfortunately, however, i kept getting the 'openworkbook' error saying that coudn't find the file. I doubled check the excel address in my sharepoint site. I copied it pasted in the web browser and it worked fine. I might not have some add-ins or setups??
Can you help me on this please?
The first thing to do is to check whether or not you published the workbook. Saving an Excel file to SharePoint is not the same as publishing it to Excel Services. Open up the file and select to publish it. For this to work, it has to be an Excel 2007 document.
You don't need to have any add-ins for SharePoint, but it's possible that Excel Services might not be activated on the site collection you're using. That's something you'd need an administrator to do.
When submitting I get the error "Invalid value to argument: sessionId."
When specifying the webservice I use the following syntax, take note it's https.
As far as I can tell this is the only field where I enter any text in the wizard, so I am not sure what else to check.
Any assistance could be appreciated.
Hi .... I followed your article an basically I want Steps 1 and 2. So I have added only 1,2 and 7 actions in the rule. But the thing is rule gets executed but it is not updating the Excel Sheet ...... Any suggestions ?
I have the "OpenWorkbook" and a SOAP error saying that I don't have permissions to the file. Is there somewhere additionally in Excel Services that I need to set permissions?
Did you solve your SOAP error ??? I have the same problem...
Yes, it was an issue with the trusted sites. My helpdesk tried to make just the folder a trusted site instead of the top level. Also, make sure you publish the file to Excel Services after you upload it to the folder.
I got it to work and it works great! This pulls a calculated value into Infopath via Excel Services. It doesn't however write anything permanent out to Excel. Anyone has any info on doing that please send me an email.. :)
Thanks for posting this, helped immensely!!!
This was a very well written post.
I was able to get everything working for one cell as described.
Do you have any updates to this post, where you take a range of cells from an Excel Spreadsheet and pull them into Infopath? I am using InfoPath 2007.
Any help would be great!
hi jessica, Thanks alot for useful info here. i was trying to do the above sample. but i am getting soap exception like "The query cannot be run for the following DataObject: OpenWorkbook. InfoPath cannot run the specified query. The SOAP response indicates that an error occurred on the server:This workbook cannot be opened because it is not stored in an Excel Services Application trusted location". I have configured the excel trust location also. But still why i am facing this problem. Could you please quickly help me regarding this. Thanks in advance... Regards, KV.