This is related to a post made earlier, but I do not feel I explained myself
clearly enough.

We use an Excel workbook template to document information about schemes of
work and lesson plans. These workbooks are 'filled' in and then converted to
'web content', i.e. processed by internal (passworded) macros and saved out
as a non interactive workbook in HTML.

Occasionally I need to update a cell in a small number of worksheets
contained within the workbook (changes to hyperlinks to external documents).

If I wish to edit the HTML, I open the files in excel, at which point Excel
seems to go through a process where all cells are (re) calculated.

As these workbooks are fairly large, this takes an age. Is there any way in
which I can edit these files without this recalculation?

regards

John
--
UK John

Hi

I don't know if there is any way to prevent Excel from needing to
undergo a complex transformation to convert from HTML to Excel
WorkBook. Experiment with setting calculation to manual before opening
perhaps.

A couple of ideas:

1) The HTML document is a text document - can you edit it as text? If
you know what you are looking for, it might not be too hard using
either VBA's string manipulation functions or perhaps the VBS regular
expression object (which can be referenced to from VBA). Look at the
raw HTML to see if this would be viable. If possible - it should be
quick.

2) Why not keep 2 copies of the original workbook - the HTML version
and the original .xls file? Make the desired change in the .xls file
and simply replace the HTML versions with a new copy.

HTH

-John Coleman

JohnBury wrote:
This is related to a post made earlier, but I do not feel I explained myself
clearly enough.

We use an Excel workbook template to document information about schemes of
work and lesson plans. These workbooks are 'filled' in and then converted to
'web content', i.e. processed by internal (passworded) macros and saved out
as a non interactive workbook in HTML.

Occasionally I need to update a cell in a small number of worksheets
contained within the workbook (changes to hyperlinks to external documents).

If I wish to edit the HTML, I open the files in excel, at which point Excel
seems to go through a process where all cells are (re) calculated.

As these workbooks are fairly large, this takes an age. Is there any way in
which I can edit these files without this recalculation?

regards

John
--
UK John

John

Thanks for your reply.

Your point 1) I hadn't thought about opening the html as a text file!

I gave it a quick go using notepad. I can find what needs editing, although
notepad would not then let me save this back to the original file. The files
do not appear to be marked as read only so I do not quite understand why I
can not re-save them! - Oops just spotted it - the whole directory is read
only!

Each workbook contains 100 worksheets. These are saved as files named
sheet999.htm (999 being anything from 001 to 120). The sheets I need are
'salted' away in amongst them. Unfortunately there may be a different number
of sheets to be changed for each workbook.

I shall persevere with this!

Your point 2) I do keep the original workbook, but there is a problem in
that opening the workbooks, editing them and then saving and re publishing
also takes an age!!

Mty additional point) The changes I am making are being performed by a VB6
program. It opens a batch of workbooks (serially) edits cells and then
resaves the file. However it takes about 3-4 minutes to open and recalculate
each workbook to make somewhere between 2 and 9 changes per workbook (the
changes themselves take seconds!) hence my frustration.
--
UK John


"John Coleman" wrote:

Hi

I don't know if there is any way to prevent Excel from needing to
undergo a complex transformation to convert from HTML to Excel
WorkBook. Experiment with setting calculation to manual before opening
perhaps.

A couple of ideas:

1) The HTML document is a text document - can you edit it as text? If
you know what you are looking for, it might not be too hard using
either VBA's string manipulation functions or perhaps the VBS regular
expression object (which can be referenced to from VBA). Look at the
raw HTML to see if this would be viable. If possible - it should be
quick.

2) Why not keep 2 copies of the original workbook - the HTML version
and the original .xls file? Make the desired change in the .xls file
and simply replace the HTML versions with a new copy.

HTH

-John Coleman

JohnBury wrote:
This is related to a post made earlier, but I do not feel I explained myself
clearly enough.

We use an Excel workbook template to document information about schemes of
work and lesson plans. These workbooks are 'filled' in and then converted to
'web content', i.e. processed by internal (passworded) macros and saved out
as a non interactive workbook in HTML.

Occasionally I need to update a cell in a small number of worksheets
contained within the workbook (changes to hyperlinks to external documents).

If I wish to edit the HTML, I open the files in excel, at which point Excel
seems to go through a process where all cells are (re) calculated.

As these workbooks are fairly large, this takes an age. Is there any way in
which I can edit these files without this recalculation?

regards

John
--
UK John