Can worksheet data be exported/imported to/from flat file?

[Joseph Geretz]

We have a software product which does structured data storage and retrieval
of application data to and from the database. (Like almost any other
application - nothing new here.) To allow customers to do ad-hoc extensions,
we have a module which allows them to integrate their own spreadsheets.
Alternatively, the client may contract with us to develop custom
spreadsheets for them. This allows us to quickly integrate custom
'interfaces' with our app, according to each customer's needs.

The problem with this, is that the 'code' (in our context VBA) is stored
repetitively with each document saved. With the number of documents
typically approaching the thousands at most customers, correcting a bug, or
evolving the VBA becomes a real problem. Only new documents will benefit fom
bug fixes / code evolution. Existing documents will continue to run exactly
as they always have.

So I have this idea, whereby I'd like to continue using Excel in the current
manner, but instead of storing the entire worksheet (data and code) I'd like
to somehow separate the data and save only the data. Like an export
function. opening a document would involve opening the relevant worksheet
(which is actually just a code template), and merging in the data which was
exported at the end of the last editing session. That way, code changes
would impact all documents, including legacy documents. (Of course new
functions would need to account for backward compatibility, but that's
nothing new, it's as it should be.)

So is there any defined way to simply export / import all spreadsheet data
without actually knowing anything about the details of the spreadsheet? Or
will I have to write a custom import / export function for every
spreadsheet?

Thanks for your help,

- Joe Geretz -

 

[Vasant Nanavati]

The command:

Worksheets.Copy

will copy all the worksheets in the active workbook to a new, uinnamed
workbook, without the code. Of course, any code in worksheet modules will be
copied, but standard modules will not.

 

[onedaywhen]

As a suggestion, look at saving the workbook as XML Data or an XML Spreadsheet.

 

[Joseph Geretz]

Hi Guys,

Thanks for your suggestions.

As a suggestion, look at saving the workbook as XML Data or an XML

Spreadsheet.

Sounds interesting, I've never heard of this. Got a link where I can find
out more?

Thanks,

- Joe Geretz -

 

[onedaywhen]

File, Save As, Save as type = XML Spreadsheet (XML Data option is
xl2003 only). Then open the .xml file in a HTML editor etc.

--