Updating access data from excel
Discarded due to constraints with promoting repeating table fields. Implement Info Path rules to write the form information out to a Share Point list on submit, then automate the list extract to Excel.
Discarded as the form had a repeating table, would require code in the Info Path form and would still require additional code to automate the extraction. Use Share Point workflow to extract the forms to Excel.
That also means, if a worksheet has 2 tables, they each get their own data filters (usually excel wont allow you to add more than one set of filters per sheet, but when it comes to tables, all exceptions are made, just for you) The most important advantage of tables is that, you can write meaningful looking formulas instead of using cell references.
When you create and name the table (you can name the table from design tab), you can write formulas that look like this: The beauty of structured references is that, when you add or remove rows, you dont need to worry about updating the references.
That means you don’t need to use conditional formatting or manually format alternative rows in different color.
Excel tables make having calculated columns very easy.That is where Pivot tables come in to picture [pivot table tutorial]. Just click a button and your table goes to pivot table. If you have a corporate intranet Sharepoint portal, you can easily publish the excel tables as share-point lists. So what we ended up considering was to use the new Client Object Model, run from a remote workstation in the client’s network, extracting the forms into an Excel file using Open XML and updating the status of the form after it was extracted.Sadly, while this ended up being a good choice, there is very little documentation/blogs on accessing Info Path forms using the client object model.