InfoPath and Excel Services
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 https://<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.
Comments
Anonymous
January 01, 2003
Hi jclawyer 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.Anonymous
January 01, 2003
Hello, I am trying to use the info in this form. I keep getting the following error. The query cannot be run for the following DataObject: OpenWorkbookInfoPath cannot run the specified query.The SOAP response indicates that an error occurred:Unable to process the request.Any help or suggestions would be greatly appreciated. Thanks!!!Anonymous
January 01, 2003
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!!!Anonymous
January 01, 2003
Hello, 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?Anonymous
January 01, 2003
Hi Jessica, thank you for sharing your work, you've done a great job! and would like to thank you for making this process possible. I've followed you guide, unfortunately, I keep getting the following error: 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: The file that you selected could not be found. Check the spelling of the file name and verify that the location is correct. I've check my "copy shortcut" and the "ctrl-v" ...I've pasted the link to the browser and it seems to work. In this case, it seems that the "http://....excelservice.asmx?WSDL" does recognize the link. Any idea to how to troubleshoot the webservice? ps. I did publish excel to excel services Kind Regards, CarlosAnonymous
April 17, 2011
The comment has been removedAnonymous
June 08, 2011
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 ?Anonymous
July 29, 2011
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?Anonymous
August 25, 2011
@InfoPathGrll Did you solve your SOAP error ??? I have the same problem...Anonymous
September 05, 2012
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!Anonymous
March 24, 2013
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.Anonymous
September 06, 2013
Hi Jessica, Thanks for the post, can you suggest in case we would like to export a repeating table in infopath, which method to go for in excelservice.asmx. ThanksAnonymous
February 03, 2014
Has anyone been able to make this work in SharePoint 2013?Anonymous
April 07, 2014
I know this is old, but how do I format a url so that I can show the excel chart in InfoPath based on the above and take account of changing data, i.e. graph updates when data does.Anonymous
May 24, 2014
Pingback from Passing data between excel sheet and infopath 2010 form(web enabled) - FAQs SystemAnonymous
September 11, 2014
Hi, Thanks for your post. I have been able to make this work exactly as you have laid out with one read and one write.
but I have to read values A,B,C and write values D,E,F and for the life of me I cannot figure out how to scale this up.Anonymous
October 02, 2014
Thanks you so much for taking the time to documenting this. Hoping I can use it in SP/IP 2013 along with repeating table data.Anonymous
November 05, 2014
How do I get that book you wrote on infopath?Anonymous
June 09, 2015
Hi,
Thanks for post
I made a template about "Service Registration Form (Copying Data From Page to Ohter Page)" .
in my blog : https://netmerkez.wordpress.com/excel/service-registration-form/