How to "freeze" all cells in Excel sheets?

L

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!
 
H

Harald Staff

Hi

Excel can do absolutely everything -except two things: History and multi
user environment. Don't try to fix that, you will not succeed. Use something
else if history or multiple users is what counts.

Best wishes Harald
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top