MS Excel 97 SR-2

Question 1) Macro crashes on paste special

I can perform the macro sequence (See 'C2 = 2 below) (copy entire sheet
and paste special {values}) manually within the Excel worksheet but
when I either execute or step through the macro lines it always crashes
with the familiar ESOD message

" Excel.exe has generated errors and will be closed by Windows.
You need to restart the program. "

This happens on the line that performs
Edit - Paste Special {Values}

The only thing that I can think is that there is insufficient time
before the next macro line executes (physically the paste special takes
in the order of seconds as there is a fair amount of content), however
why this should be a problem while stepping through line by line in
debug mode is beyond me.


Question 2) Can the first copy specify an absolute reference to Book1

The macro code is interesting as two lines are generated by an excel
sheet, that step through a whole host of sheets that are copied out to
an external workbook and in each copy all active cells replaced with
their values. All the subsequent copies can refer to Book1 which holds
these copies, but how can I get the first copy to use this as the
target work book?

Question 3) Is this really the best way of achieving an abridged,
tamper-proof, readonly copy of a working excel book?

What I actually want to do is export a whole bunch of result sheets
(which fetch results off a master analysis sheet) but
a) not need to export the big analysis sheet and
b) not allow the end user to overtly, or inadvertently, change the
results in the sheet.

I have tried the option of locking cell contents on each sheet, but
this appears to require a password that must be entered twice
(interactively) for each sheet - too much pain for all the sheets that
must be exported. Is there not a way for the macro to provide a
password when locking the sheet? Would locking cell contents avoid the
problem that cell formulae point to non-existent sheets (if I switched
off auto calculation) (which would then avoid the need to replace
active cells with just the calculated values)?

I have experimented with the lock workbook option, this is quite neat
as it locks the windows, but doesn't seem to have anything to do with
content.


'C2 = 2
Windows("MasterEMSDB.xls:1").Activate
Sheets("ABS Test Bench ").Select '
=INDEX(CopySheetList!C:C,C2,1)
Sheets("ABS Test Bench ").Copy '
=INDEX(CopySheetList!D:D,C2,1)
Cells.Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True

'C12 = 3
Windows("MasterEMSDB.xls:1").Activate
Sheets("ABS").Select
Sheets("ABS").Copy Befo=Workbooks("Book1").Sheets(1) '
=INDEX(CopySheetList!D:D,C12,1)
Cells.Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True

Questions 1 and 2)

Dim bk as Workbook
Workbooks"MasterEMSDB.xls") _
.Sheets("ABS Test Bench ").Copy
set bk = ActiveWorkbook
ActiveSheet.UsedRange.Formula = _
Activesheet.usedRange.Value
ActiveSheet.Protect _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True


Workbooks.("MasterEMSDB.xls:1") _
.Sheets("ABS").Copy Befo=bk.Sheets(1)
ActiveSheet.UsedRange.Formula = _
Activesheet.usedRange.Value
ActiveSheet.Protect _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True

An alternate Method

Dim sh as Worksheet
Workbooks.("MasterEMSDB.xls:1") _
.Worksheets(Array("ABS Test Bench", _
"ABS").copy
set bk = ActiveWorkbook
for each sh in bk.Worksheets
With sh
.UsedRange.Formula = UsedRange.Value
.Protect _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True
end with
Next

--
Regards,
Tom Ogilvy

wrote in message
oups.com...
MS Excel 97 SR-2

Question 1) Macro crashes on paste special

I can perform the macro sequence (See 'C2 = 2 below) (copy entire sheet
and paste special {values}) manually within the Excel worksheet but
when I either execute or step through the macro lines it always crashes
with the familiar ESOD message

" Excel.exe has generated errors and will be closed by Windows.
You need to restart the program. "

This happens on the line that performs
Edit - Paste Special {Values}

The only thing that I can think is that there is insufficient time
before the next macro line executes (physically the paste special takes
in the order of seconds as there is a fair amount of content), however
why this should be a problem while stepping through line by line in
debug mode is beyond me.


Question 2) Can the first copy specify an absolute reference to Book1

The macro code is interesting as two lines are generated by an excel
sheet, that step through a whole host of sheets that are copied out to
an external workbook and in each copy all active cells replaced with
their values. All the subsequent copies can refer to Book1 which holds
these copies, but how can I get the first copy to use this as the
target work book?

Question 3) Is this really the best way of achieving an abridged,
tamper-proof, readonly copy of a working excel book?

What I actually want to do is export a whole bunch of result sheets
(which fetch results off a master analysis sheet) but
a) not need to export the big analysis sheet and
b) not allow the end user to overtly, or inadvertently, change the
results in the sheet.

I have tried the option of locking cell contents on each sheet, but
this appears to require a password that must be entered twice
(interactively) for each sheet - too much pain for all the sheets that
must be exported. Is there not a way for the macro to provide a
password when locking the sheet? Would locking cell contents avoid the
problem that cell formulae point to non-existent sheets (if I switched
off auto calculation) (which would then avoid the need to replace
active cells with just the calculated values)?

I have experimented with the lock workbook option, this is quite neat
as it locks the windows, but doesn't seem to have anything to do with
content.


'C2 = 2
Windows("MasterEMSDB.xls:1").Activate
Sheets("ABS Test Bench ").Select '
=INDEX(CopySheetList!C:C,C2,1)
Sheets("ABS Test Bench ").Copy '
=INDEX(CopySheetList!D:D,C2,1)
Cells.Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True

'C12 = 3
Windows("MasterEMSDB.xls:1").Activate
Sheets("ABS").Select
Sheets("ABS").Copy Befo=Workbooks("Book1").Sheets(1) '
=INDEX(CopySheetList!D:D,C12,1)
Cells.Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True

Tom,

Thanks for your reply on 8-Feb. Sorry to be so long in responding, I
am invariably intterupt and deadline driven and the need for that read
only export macro has only now risen above other priorities.

I tried both your macros, there seemed to be a few typos that needed
fixing (as below in renditions ExportReadOnly1 and ExportReadOnly2).
Then I tried a couple more variations. All of them seem to falter for
the following reasons :

Examples quoted from ExportReadOnly2

Set bk = ActiveWorkbook() ' Debugger shows bk has no value
For Each sh In bk.Worksheets ' Debugger shows sh has no value
so
.UsedRange.Formula = UsedRange.Value ' Macro aborts with "object not
found" or something to that effect.

It does this with both Excel versions I have access to :
MS Excel 97 SR-2
MS-Excel 2000 9.0.2720


Regards,
Fred
2006-02-23(Thu)


Sub ExportReadOnly1()
'
' ExportReadOnly macro created by Fred on 2006-02-23(Thu)
Dim bk As Workbook

Workbooks("MasterEMSDB.xls").Sheets("ABS ").Copy
Set bk = ActiveWorkbook
ActiveSheet.UsedRange.Formula = ActiveSheet.UsedRange.Value
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True

Workbooks("MasterEMSDB.xls:1").Sheets("ABS Test Bench ").Copy
Befo=bk.Sheets(1)
ActiveSheet.UsedRange.Formula = ActiveSheet.UsedRange.Value
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
End Sub


Sub ExportReadOnly2()
'
' ExportReadOnly macro created by Fred on 2006-02-23(Thu)
Dim bk As Workbook

Workbooks("MasterEMSDB.xls").Worksheets(Array("ABS Test Bench ",
"ABS")).Copy
Set bk = ActiveWorkbook()
For Each sh In bk.Worksheets
With sh
.UsedRange.Formula = UsedRange.Value
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End With
Next
End Sub

Sub ExportReadOnly3()
'
' ExportReadOnly macro created by Fred on 2006-02-23(Thu)
Workbooks("MasterEMSDB.xls").Worksheets(Array("ABS Test Bench ",
"ABS")).Copy

ActiveWorkbook.SaveAs FileName:="C:\makeReadOnly.Xls",
FileFormat:=xlNormal _
, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, _
CreateBackup:=False

For Each wSheet In ActiveWorkbook.Worksheets
With wSheet
.UsedRange.Formula = UsedRange.Value
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End With
Next wSheet
End Sub

Sub ExportReadOnly4()
'
' ExportReadOnly macro created by Fred on 2006-02-23(Thu)
Dim wSheet As Worksheet

Workbooks("MasterEMSDB.xls").Worksheets(Array("ABS Test Bench ",
"ABS")).Copy

ActiveWorkbook.SaveAs FileName:="C:\makeReadOnly.Xls",
FileFormat:=xlNormal _
, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, _
CreateBackup:=False

For Each wSheet In Workbook.Worksheets
With wSheet
.UsedRange.Formula = UsedRange.Value
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End With
Next wSheet
End Sub