Protect an excel spreasheet by script in LotusNotes

In a LotusNotes Database 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

Georges wrote:
> [...]
> 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
--
man mailt sich
mailto:mail-at-jens-seiler.de - ICQ# 24778881
http://www.jens-seiler.de

Georges wrote...
>In a LotusNotes Database 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

Thanks Harlan, I will try
Georges






On 24 juil, 22:36, Harlan Grove wrote:
> Georges wrote...
> >In a LotusNotes Database 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

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

On 29 juil, 13:11, Georges wrote:
> Thanks Harlan, * I will try
> Georges
>
> On 24 juil, 22:36, Harlan Grove wrote:
>
>
>
> > Georges wrote...
> > >In a LotusNotes Database 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- Masquer le texte des messages Page Rankingécédents -
>
> - Afficher le texte des messages Page Rankingécédents -

On Jul 29, 10:30*am, Georges wrote:
> 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
>
> On 29 juil, 13:11, Georges wrote:
>
> > Thanks Harlan, * I will try
> > Georges
>
> > On 24 juil, 22:36, Harlan Grove wrote:
>
> > > Georges wrote...
> > > >In a LotusNotes Database 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- Masquer le texte des messages Page Rankingécédents -
>
> > - Afficher le texte des messages Page Rankingécédents -

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.