How to "freeze" all cells in Excel sheets?

[LunaMoon]

Hi all,

I currently save copy of my Excel sheets every day, for historical
records.

However because some of the cells are connected to live data sources,
and other cells are linked to other time-varying sheets, the copying
of Excel sheets as record doesn't really work as it is supposed to.

The reason is that every time when you open the old record file,
things are changing and are no longer their original form.

Therefore, I could think of one way to remedy this: I should save a
"frozen" copy of the Excel sheets as snapshots and keep these
snapshots as historical records.

The easiest way is probably to "freeze" all cells, i.e. "copy and
paste special as values"... for all sheets everywhere...

How to do this programmatically? I hope I can do it thru Macro and
then then call the Macro from within my centralized automation
program, which is written in Matlab and/or Perl/ and/or Windows
Scripting, etc.

Thanks a lot!

 

[Don Guillett Excel MVP]

Hi all,

I currently save copy of my Excel sheets every day, for historical
records.

However because some of the cells are connected to live data sources,
and other cells are linked to other time-varying sheets, the copying
of Excel sheets as record doesn't really work as it is supposed to.

The reason is that every time when you open the old record file,
things are changing and are no longer their original form.

Therefore, I could think of one way to remedy this: I should save a
"frozen" copy of the Excel sheets as snapshots and keep these
snapshots as historical records.

The easiest way is probably to "freeze" all cells, i.e. "copy and
paste special as values"... for all sheets everywhere...

How to do this programmatically? I hope I can do it thru Macro and
then then call the Macro from within my centralized automation
program, which is written in Matlab and/or Perl/ and/or Windows
Scripting, etc.

Thanks a lot!

Why not a before_save macro in the ThisWorkbook module something like
this.

for each ws in worksheets
ws.usedrange.value=ws.usedrange.value
next