software

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

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

This happens on the line that performs
Edit -gt; 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(quot;MasterEMSDB.xls:1quot;).Activate
Sheets(quot;ABS Test Bench quot;).Select '
=INDEX(CopySheetList!C:C,C2,1)
Sheets(quot;ABS Test Bench quot;).Copy '
=INDEX(CopySheetList!D,C2,1)
Cells.Select
Selection.Copy
Range(quot;A1quot;).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range(quot;A1quot;).Select
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True

'C12 = 3
Windows(quot;MasterEMSDB.xls:1quot;).Activate
Sheets(quot;ABSquot;).Select
Sheets(quot;ABSquot;).Copy Befo=Workbooks(quot;Book1quot;).Sheets(1) '
=INDEX(CopySheetList!D,C12,1)
Cells.Select
Selection.Copy
Range(quot;A1quot;).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range(quot;A1quot;).Select
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=TrueQuestions 1 and 2)

Dim bk as Workbook
Workbooksquot;MasterEMSDB.xlsquot;) _
.Sheets(quot;ABS Test Bench quot;).Copy
set bk = ActiveWorkbook
ActiveSheet.UsedRange.Formula = _
Activesheet.usedRange.Value
ActiveSheet.Protect _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=TrueWorkbooks.(quot;MasterEMSDB.xls:1quot;) _
.Sheets(quot;ABSquot;).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.(quot;MasterEMSDB.xls:1quot;) _
.Worksheets(Array(quot;ABS Test Benchquot;, _
quot;ABSquot;).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

gt; wrote in message oups.com...
gt; MS Excel 97 SR-2
gt;
gt; Question 1) Macro crashes on paste special
gt;
gt; I can perform the macro sequence (See 'C2 = 2 below) (copy entire sheet
gt; and paste special {values}) manually within the Excel worksheet but
gt; when I either execute or step through the macro lines it always crashes
gt; with the familiar ESOD message
gt;
gt; quot; Excel.exe has generated errors and will be closed by Windows.
gt; You need to restart the program. quot;
gt;
gt; This happens on the line that performs
gt; Edit -gt; Paste Special {Values}
gt;
gt; The only thing that I can think is that there is insufficient time
gt; before the next macro line executes (physically the paste special takes
gt; in the order of seconds as there is a fair amount of content), however
gt; why this should be a problem while stepping through line by line in
gt; debug mode is beyond me.
gt;
gt;
gt; Question 2) Can the first copy specify an absolute reference to Book1
gt;
gt; The macro code is interesting as two lines are generated by an excel
gt; sheet, that step through a whole host of sheets that are copied out to
gt; an external workbook and in each copy all active cells replaced with
gt; their values. All the subsequent copies can refer to Book1 which holds
gt; these copies, but how can I get the first copy to use this as the
gt; target work book?
gt;
gt; Question 3) Is this really the best way of achieving an abridged,
gt; tamper-proof, readonly copy of a working excel book?
gt;
gt; What I actually want to do is export a whole bunch of result sheets
gt; (which fetch results off a master analysis sheet) but
gt; a) not need to export the big analysis sheet and
gt; b) not allow the end user to overtly, or inadvertently, change the
gt; results in the sheet.
gt;
gt; I have tried the option of locking cell contents on each sheet, but
gt; this appears to require a password that must be entered twice
gt; (interactively) for each sheet - too much pain for all the sheets that
gt; must be exported. Is there not a way for the macro to provide a
gt; password when locking the sheet? Would locking cell contents avoid the
gt; problem that cell formulae point to non-existent sheets (if I switched
gt; off auto calculation) (which would then avoid the need to replace
gt; active cells with just the calculated values)?
gt;
gt; I have experimented with the lock workbook option, this is quite neat
gt; as it locks the windows, but doesn't seem to have anything to do with
gt; content.
gt;
gt;
gt; 'C2 = 2
gt; Windows(quot;MasterEMSDB.xls:1quot;).Activate
gt; Sheets(quot;ABS Test Bench quot;).Select '
gt; =INDEX(CopySheetList!C:C,C2,1)
gt; Sheets(quot;ABS Test Bench quot;).Copy '
gt; =INDEX(CopySheetList!D,C2,1)
gt; Cells.Select
gt; Selection.Copy
gt; Range(quot;A1quot;).Select
gt; Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
gt; SkipBlanks:= _
gt; False, Transpose:=False
gt; Range(quot;A1quot;).Select
gt; Application.CutCopyMode = False
gt; ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
gt; Scenarios:=True
gt;
gt; 'C12 = 3
gt; Windows(quot;MasterEMSDB.xls:1quot;).Activate
gt; Sheets(quot;ABSquot;).Select
gt; Sheets(quot;ABSquot;).Copy Befo=Workbooks(quot;Book1quot;).Sheets(1) '
gt; =INDEX(CopySheetList!D,C12,1)
gt; Cells.Select
gt; Selection.Copy
gt; Range(quot;A1quot;).Select
gt; Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
gt; SkipBlanks:= _
gt; False, Transpose:=False
gt; Range(quot;A1quot;).Select
gt; Application.CutCopyMode = False
gt; ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
gt; Scenarios:=True
gt;
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 quot;object not
foundquot; 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.2720Regards,
Fred
2006-02-23(Thu)Sub ExportReadOnly1()
'
' ExportReadOnly macro created by Fred on 2006-02-23(Thu)
Dim bk As Workbook

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

Workbooks(quot;MasterEMSDB.xls:1quot;).Sheets(quot;ABS Test Bench quot;).Copy
Befo=bk.Sheets(1)
ActiveSheet.UsedRange.Formula = ActiveSheet.UsedRange.Value
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
End SubSub ExportReadOnly2()
'
' ExportReadOnly macro created by Fred on 2006-02-23(Thu)
Dim bk As Workbook

Workbooks(quot;MasterEMSDB.xlsquot;).Worksheets(Array(quot;ABS Test Bench quot;,
quot;ABSquot;)).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(quot;MasterEMSDB.xlsquot;).Worksheets(Array(quot;ABS Test Bench quot;,
quot;ABSquot;)).Copy

ActiveWorkbook.SaveAs FileName:=quot;C:\makeReadOnly.Xlsquot;,
FileFormat:=xlNormal _
, Password:=quot;quot;, WriteResPassword:=quot;quot;,
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(quot;MasterEMSDB.xlsquot;).Worksheets(Array(quot;ABS Test Bench quot;,
quot;ABSquot;)).Copy

ActiveWorkbook.SaveAs FileName:=quot;C:\makeReadOnly.Xlsquot;,
FileFormat:=xlNormal _
, Password:=quot;quot;, WriteResPassword:=quot;quot;,
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