Using an Excel spreadsheet as a datasource

[DarrylGould]

Hello

I have an InfoPath form which requires a lookup to obtain data from an
Excel spreadsheet. The Excel spreadsheet needs to be stored within
the InfoPath form as the data is required offline. Would I be right
in thinking that the best way to handle this would be to create an XML
file from the Excel data, and attach this document to the InfoPath
form as a Resource document? If so, can you please tell me how to do
so?

Thanks
Darryl

 

[Rachel Garrett]

Hello

I have an InfoPath form which requires a lookup to obtain data from an
Excel spreadsheet.  The Excel spreadsheet needs to be stored within
the InfoPath form as the data is required offline.  Would I be right
in thinking that the best way to handle this would be to create an XML
file from the Excel data, and attach this document to the InfoPath
form as a Resource document?  If so, can you please tell me how to do
so?

Darryl,

This is a problem I have been struggling with. I haven't found a way
to make an Excel file directly save as XML, in a way that makes sense
for the data. The XML is all about cell height, font, etc. You're
supposed to be able to export to XML if you create a schema and map
it, but this requires creating a schema, and there isn't a Microsoft
Office product that does that (many people recommend XMLSpy, but I
haven't used it much).

One thing I did was open Access and import the Excel file as a table.
(You have to do it as import table, not linked table, because linked
won't work once you get to InfoPath.) Then you can save the Access
table as XML, and it uses the row headings as element names. Then you
can write an InfoPath form based on that new XML file.

Hope this helps.

--Rachel Garrett

 

[DarrylGould]

Darryl,

This is a problem I have been struggling with. I haven't found a way
to make an Excel file directly save as XML, in a way that makes sense
for the data. The XML is all about cell height, font, etc. You're
supposed to be able to export to XML if you create a schema and map
it, but this requires creating a schema, and there isn't a Microsoft
Office product that does that (many people recommend XMLSpy, but I
haven't used it much).

One thing I did was open Access and import the Excel file as a table.
(You have to do it as import table, not linked table, because linked
won't work once you get to InfoPath.) Then you can save the Access
table as XML, and it uses the row headings as element names. Then you
can write an InfoPath form based on that new XML file.

Hope this helps.

--Rachel Garrett

Thanks Rachel. I will give that a try.