kworks: Repair sheets with autocalc on strike

About the old autocalc on strike problem

In ClarisWorks/AppleWorks5 and pre AppleWorks 6.2.8/6.2.9 versions, a bug was able to put wrong internal pointers in spreadsheets. Some of these wrong pointers put the autocalc feature on strike. It seems that the bug introducing wrong pointers is killed in 628/629 but this can't repair existing wrong pointers.

That's why we must clean our sheets by ourselves.

Two ways are available:

1 - copy paste into a new sheet.

2 - apply the trailing AppleScript.

-- Merci ? Niel LAMBERT
-- Exécuter ce script sur une feuille de calcul
-- semble rétablir nombre de pointeurs
-- corrigeant ainsi bien des anomalies.
-- Désormais utilisable sur un tableur utilisant la fonction "MACRO("

-- Thanks to Niel LAMBERT
-- Launch this script on an open Spreadsheet
-- reset a lot of pointers, correcting many anomalies.
-- Now usable on spreadsheets using the "MACRO(" function.

set french to false -- true = français, false = english

if french is true then
set msg0 to "Ce script n'est pas compatible" & return & ¬
"avec cette version d‘AppleWorks." & return & ¬
"Veuillez utiliser une version 6.0" & return & ¬
"ou plus récente..."
set msg2 to "Aucun document n'est ouvert."
set msg3 to "Le document au premier plan n'est pas un tableur."
set msg41 to "Termin? !"
set msg42 to " Vu "
set msg99 to " Vu "
else
set msg0 to "This script is not compatible" & return & ¬
"with this version of AppleWorks." & return & ¬
"Please use version 6.0" & return & "or higher..."
set msg2 to "No open document"
set msg3 to "The front document is not a Spreadsheet."
set msg41 to "Done !"
set msg42 to " OK "
set msg99 to " Oops "
end if

try
tell application "AppleWorks 6"
activate

set versAW to version as text
if ("6." is not in versAW) then ¬
error msg0 number 8000

set tDoc to spreadsheet document
if (count each document) < 1 then ¬
error msg2 number 8002
if (document kind of front document is not tDoc) then ¬
error msg3 number 8003

tell front document
set a to count cells

set hasmacro to false
repeat with B from 1 to a
set zeform to (formula of cell B) as text
if (length of zeform > 6) and "MACRO(" is in zeform then
set hasmacro to true
exit repeat
end if
end repeat

set mchecked to checked of (menu item 11 of menu 5)
-- menu "Calculer > Recalcul automatique"
-- menu "Calculate > Auto-calculate"

if mchecked is true and hasmacro is true then ¬
select menu item 11 of menu 5
-- désactive Recalcul automatique (merci Fredo d;o)
-- disable Auto-calculate (thanks Fredo d;o)

repeat with B from 1 to a
set islocked to (lock of cell B)
if islocked is read only then ¬
set (lock of cell B) to read write
set formula of cell B to ¬
(get formula of cell B)
if islocked is read only then ¬
set (lock of cell B) to read only
end repeat

if mchecked is true and hasmacro is true then ¬
select menu item 11 of menu 5
-- r?-active Recalcul automatique (merci Fredo d;o)
-- re-enable Auto-calculate (thanks Fredo d;o)

end tell -- to front document

display dialog msg41 buttons {msg42} giving up after 20

end tell -- to AppleWorks

on error MsgErr number NroErr

if NroErr is not -128 then
beep 2
display dialog "" & NroErr & " : " & MsgErr ¬
with icon 0 buttons {msg99} giving up after 20
end if
return
end try

Yvan KOENIG (from FRANCE)