Using Infopath / Sharepoint / Excel

[AOB]

Hi all,

Can anybody direct me to some decent reading material on using Infopath / Sharepoint / Excel, in order to :

• collect data from multiple sources (Infopath forms)
• which are saved on a web service (SharePoint site)
• and summarise on a separate dashboard (Excel) which is also contained on the site.

When completed, this process will be performed on a daily basis so I need to be able to dynamically create a new version of the Excel template with each day's data taken from each of the submitted forms.

I am at the stage where I can construct a basic form in Infopath with a submit button which uploads the completed form to a library on the SharePoint site. What I want to do now is change that 'slightly' and instead submit the data on the completed form directly to an Excel spreadsheet which is also housed on the SharePoint site.

I think I need to write specific code for the Submit button in Visual Studio but I don't know how to define the SharePoint site within InfoPath, to subsequently :

• identify the Excel file for that day
• open it
• update the relevant fields
• close & save.

I'm sure this could be done more easily and efficiently with data connections but I haven't a clue where to even start...

Basically, looking for a friendly nudge in the right direction!

Can anybody help?

Thanks

AOB

 

[nordyck]

I was going to set up something similar but due to network restrictions, couldn't do it.

Instead of Excel, I was going to use Access and in SharePoint have a table created for the data repository.
the sharepoint table is linked so any changes made in access automatically updates sharepoint.

Any data that comes in through infopath will update your access database, then sharepoint is automatically updated.

 

[AOB]

Hey nordyck,

Thanks for the response - yes, I had similar thoughts; unfortunately I too am faced with network restrictions which prevent me from housing an Access database on the SharePoint site. I can store the database locally but my users would not have connectivity to it and we do not have a common server to work from (hence the need for a SharePoint which everyone could access via the web)

I don't think there is any way around it other than having users submit completed forms to SharePoint and then have a central co-ordinator pull the data on a daily basis, update the dashboard and either distribute or upload back to the SP (which I was hoping to avoid)

Even then, I've encountered major difficulties importing the form data into the Access database (see separate thread : InfoPath forms on SharePoint >> Local Access DB / Excel WB (Office 2007) - InfoPath Dev)

Were you successful in your attempts?...

Cheers!

AOB

 

[nordyck]

I wasn't successful in my attempt. On local machine, it worked fine, when I transferred the template to SharePoint, it wouldn't work. It didn't matter if I open the template directly or through email. The AF has the network locked down very tight. The only thing SharePoint is good for is storing documents.