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 -

 

[mudraker]

You can manually copy or move 1, 2 3 or all worksheets to a new workboo
by right clicking on the sheet name tab and following the promts. Thi
will copy any macro codes that are on those sheet but will not copy an
code in Forms or Modules

I hope this is what you are after and I did not misunderstand you
quer

 

[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.

--

 

[Homer]

It sounds like you shuld make your code into an Add-In. Then your customer
installs the Add-In and your code is always accessible (it will load when
Excel loads). You update the code in the add-in and release one file to the
customer.

The code will need to be modified slightly: you will have to change any
references to correctly refer to either ThisWorkbook for items in the sheets
of your add-in file (should be static data), or ActiveWorkbook for data in
sheets in the customer's files. Pop-up context menus are not availble from
an add-in, so these functions will need to be put into regular menus or
toolbars.

The add-in could contain code to check for the macro in existing files and
copy the data to new files, as they are accessed.




------------8<--------------