Protect an excel spreasheet by script in LotusNotes

[Georges]

In a LotusNotes dB I Create a spreadsheet from a view with the use
of 'CreateObject("Excel.Application")'
Can someone explain me how it is possible to protect the sheet(s)
created via script?
I need to avoid anyone to change anything in the spreasheet, even the
usage of the Save As if possible.
Thanks, Georges

 

[Jens Seiler]

[...]
Can someone explain me how it is possible to protect the sheet(s)
created via script?
[..]

While I don't know the direct answer to your question you can always
help yourself by simply recording a macro whily you do the action in
question manually yourself and then looking at the code created by the
macro recorder.

Greetings,
Jens Seiler

 

[Harlan Grove]

In a LotusNotes dB I Create a spreadsheet from a view with the use
of  'CreateObject("Excel.Application")'

...

CreateObject is a function call and should return an Excel application
object referring to a running instance of Excel when successful.
Presumably you're assigning its result to a variable of type Object.
If so, and if there were only one workbook open in that Excel
instance, you could protect each worksheet and the workbook using code
similar to the following.


'NOTE: LotusScript code, **NOT** VBA code
Sub foo
Dim xl As Variant, wb As Variant

'possibly other code here

Set xl = createobject("Excel.Application")
Set wb = xl.workbooks.add

'possibly other code here

'note: Lotus was BRAINDEAD when they designed LotusScript
'Forall loop variables must be UNDECLARED when used,
'thus ws wasn't declared above
Forall ws In wb.Worksheets
'different passwords for different worksheets
'left as an exercise
ws.Protect "your worksheet password here"
End Forall

'possibly other code here

wb.Protect "your workbook password here", True, True

wb.SaveAs "your filename here", , _
"your file open password here", _
"your file modify password here", True

wb.close False

'possibly other code here

xl.Quit

'possibly other code here

End Sub

 

[Georges]

Thanks Harlan, I will try
Georges

...

CreateObject is a function call and should return an Excel application
object referring to a running instance of Excel when successful.
Presumably you're assigning its result to a variable of type Object.
If so, and if there were only one workbook open in that Excel
instance, you could protect each worksheet and the workbook using code
similar to the following.

'NOTE: LotusScript code, **NOT** VBA code
Sub foo
        Dim xl As Variant, wb As Variant

        'possibly other code here

        Set xl = createobject("Excel.Application")
        Set wb = xl.workbooks.add

        'possibly other code here

        'note: Lotus was BRAINDEAD when they designed LotusScript
        'Forall loop variables must be UNDECLARED when used,
        'thus ws wasn't declared above
        Forall ws In wb.Worksheets
                'different passwords for different worksheets
                'left as an exercise
                ws.Protect "your worksheet password here"
        End Forall

        'possibly other code here

        wb.Protect "your workbook password here", True, True

        wb.SaveAs "your filename here", , _
          "your file open password here", _
          "your file modify password here", True

        wb.close False

        'possibly other code here

        xl.Quit

        'possibly other code here

End Sub

 

[Georges]

Huuum
the code is not saved by LN.

I receive the message 'Protect is not sub or a function name'

did I miss something?

Georges

 

[GMAN221]

Huuum
the code is not saved by LN.

I receive the message    'Protect is not sub or a function name'

did I miss something?

Georges

George,

I have spent many years writing script to both export and import data
too and from Lotus Notes Notes to Excel. The best piece of advice
that I can give you is launch Excel. Turn on the Macro to record your
actions. Go through the motions to protect the spreadsheet and/or
workbook. Stop the recording and then view what the macro recorded.
Next you will need to take this code into Notes and paste it into your
script. Sure enough you will need to make some modifications, but
they should be minimal. That should do it!

Take care and good luck.