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

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 -

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

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.

--

Vasant

"Joseph Geretz" wrote in message
...
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 -

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

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

--

"Joseph Geretz" wrote in message ...
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 -

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

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 -

--

"Joseph Geretz" wrote in message
...
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 -

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

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

--

"Joseph Geretz" wrote in message ...
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 -

--

"Joseph Geretz" wrote in message
...
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 -