Getting data from Excel into an InfoPath Template

[Richard MG]

We use an Excel workbook to do a series of calculations on some data. We want
the output from that to go to an InfoPath template which will then be
processed through SharePoint.

If we want to copy data from one Excel sheet into another we can do that
with VBA. Can we do the same from Excel into InfoPath? Would this be done in
Excel or in InfoPath? This is not about exporting a form. It is only the data
I want copied across.

 

[Clay Fox]

Hi Richard.

I think there is probably a way to get data from Excel via code but it makes
the form mush more complicated.

Typical practice it to use a backend database that Excel and Infopath both
connect to. That way Excel users act the same but the data is being saved to
a table. Then in InfoPath you can access and display this data as well.

 

[Richard MG]

Thanks, Clay.

You seem to be suggesting creating a data source that both Excel and
InfoPath react to. I don’t believe this is what we are after. Our Excel
workbook contains elements of data – client name, company, etc. as well as
calculated totals that we want to transfer to an InfoPath template. Each time
this happens the data will be different.

At its simplest level we would want to take, say, the client name from Excel
and enter it in InfoPath. I recall reading in the 2003 version that it is
possible to Copy and Paste data from Excel into InfoPath. Is this not
possible programmatically?

Maybe this is a question for the Excel team!

 

[Clay Fox]

If you have a repeating table or individual fields you can copy and paste
between the two.

What I was describing would be more automated. Say you have a spreadsheet
with set of client information, which you have manipulated in Excel. This
would be stored in a database repository, really the Excel user would be
unaware. You open InfoPath and select the client and all of that data or
whatever you wanted would be imported automatically and eliminate any copy
and paste and formatng etc.

But it all comes down to your specific process and how refined you want it.

I also know of a tool that can convert an Excel sheet to an InfoPath form.
If your data is just for one client, rather than multiple, then you can do a
one for one conversion either as needed or as a batch process.

 

[Richard MG]

While this is different to the way I was thinking, if it works that is great.
However, your description still makes me wonder if that is the solution I am
looking for. My programming skill is VBA only. How would I set this up when
the Excel worksheet and InfoPath Templates are on an outworkers laptop and
possibly not connected to the web while they are working on the forms . Each
instance of the transaction would be a one-off with no need to store the data
anywhere other than to effect the transfer (as in a Copy & Paste). What is
the nature of the database and where would that sit? Can you give me pointers
to the code on both sides?

Is it not possible to set up a Class Module (or whatever) in Excel which
stores the variables needed for the transfer and making that Module available
to InfoPath to fill the fields? Or is that what you mean by a "database
repository"? From the worksheet we want to click a "InfoPath" button and the
Template opens with all the data carried over. One block of data we have is
an array.

Converting forms is not really what we want but seems to be supported in 2007.

 

[Clay Fox]

For offline you could use a local database so that it was always available
and then sync the records back when online to a master enterprise DB.

If I knew what you were really trying to do, the requirments, and the data
it involved I might be better able to propose a solution.

You can send me an email here if you have some documentation or details on
what the solution is for.
http://www.infopathdev.com/members/clayfox.aspx