Hi all,
Newly introduced to the wonders of InfoPath and hoping it can form part of a solution for me - apologies in advance if I am posting inappropriately...
I'm hoping to use InfoPath to collaborate data by publishing form templates on a SharePoint site. Users will populate & submit forms on the SharePoint on a daily basis. Submissions are directed to a separate library on the same site. My plan is to import the submitted data contained in these forms either into a locally stored Access database, or to a locally stored Excel workbook. From there, I hope to generate a daily dashboard/report for general distribution or publication back to the SharePoint.
However, I'm running into difficulties transferring the data on the submitted forms (xml files) back to either Access or Excel.
With Access, I have set up tables with identical field names to those defined within the form, in the hope that a simple XML import will detect the fields and insert the appropriate record. But whenever I try to do this, Access can't identify, or has no visibility to, any of the fields contained in the submitted forms. The 'Import XML' screen only provides what appear to be HTML fields ('head', 'div', 'span' etc.), not the fields I defined when designing the form.
With Excel, I'm trying to use VBA to construct XML maps referencing each of the submitted files, so that a macro can be run each day to pull each set of information into the appropriate cells and produce the report. The problem here is, the VBA does not pick up the necessary schema for the form, instead providing only HTML tags again. Strangely, when I set up the XML maps manually, it has no problem identifying the schema & fields, and I can drag & drop as I please - only when I try to do this via VBA does it encounter problems.
(Have already posted threads relating to this elsewhere : http://www.mrexcel.com/forum/excel-questions/657705-adding-xml-mapping-via-visual-basic-applications-xl2007.html)
What's really puzzling me is, I can't imagine I'm the first to try to use InfoPath to create / submit forms to a SharePoint, for subsequent import to Access or Excel. Yet having spent several days trawling the internet trying to find solutions to either of these problems, I can't seem to find the answer to my problems anywhere.
Can anybody on this forum point me in a direction to learn how to share information between submitted InfoPath forms on a SharePoint site and locally stored Access databases / Excel workbooks?
Thanks in advance and apologies once again if this post is not in line with what is expected of forum members...
Al