The goal is to have an InfoPath form on a SharePoint site that is able to send and receive values with an Excel workbook template. Then when the form is submitted the excel workbook will be saved with the data from the form.
The attached WB sits in a excel document library on the SharePoint site and is the template. I have an InfoPath form that submits to its own library and a workflow that runs when a form is submitted. The workflow creates a new list item in the excel document library with the data from the form. The VBA in the WB sets the named ranges equal to the value of the columns (for that item) in the excel document library. The issue I am having is with retrieving data from the created excel WB and displaying it in the InfoPath form. When I use a web query SOAP/REST to try and retrieve data from the WB, before the WB has been open and closed, it returns blank values.
This is because the event trigger for the WB is On_Open and the values are not set until it is opened and closed. I have tried using the SOAP call OpenWorkbook and OpenWorkbookForEditing and they error along the lines of “ the workbook cannot be opened format not supported’. I believe this is due to the fact that it is a .xlsm file and excel services doesn’t support them, but I’m not sure.
I have the business process working now using individual SOAP calls for each field but I need to move away from that. It is unnecessarily complex and does not work reliably.
The job is to get the code in the excel WB to work. That is having the values set as the workbook is created so that I can receive values back into my form. The IP form will be sending DebitRate and MarginDebit to the excel wb and recieveing the calculated value TotalPL back