[Solved] Protect/Unprotect sheets in macro

[Nocton]

I am moving a spreadsheet application from Excel to LibreOffice and need to replicate some macros. I have recorded a macro to protect a sheet and move around. All OK but I should like to name the sheets for clarity, as VBA does rather than use sheet numbers. Currently I have something like:

Code: Select all

dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "Nr"
args4(0).Value = 2

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args4())

rem ----------------------------------------------------------------------
dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name = "ToPoint"
args5(0).Value = "$K$14"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args5())

rem ----------------------------------------------------------------------
dim args6(0) as new com.sun.star.beans.PropertyValue
args6(0).Name = "StringName"
args6(0).Value = "Here on I&E"

dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args6())

So I want to replace:
args4(0).Name = "Nr"
args4(0).Value = 2
with code using the sheet name

How can I do this?

[RPG]

Maybe when you record the macro a little different it is more easy.

sub Rename
rem ----------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "DezeNaam.B2" ' goto the sheet and the location

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
wait 10 ' Maybe you need a little wait
rem ----------------------------------------------------------------------
rem define variables
rem ----------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Name"
args2(0).Value = thiscomponent.Currentselection.string ' Get the value

dispatcher.executeDispatch(document, ".uno:RenameTable", "", 0, args2())

end sub

[DiGro]

Or take a look here: viewtopic.php?t=87424

[Nocton]

Thank you, RPG, but how could I "record the macro a little different"?

[RPG]

I do not know what must be the difference.
Maybe your first explanation is not clear to me.

[RPG]

I understand now the problem is to concatenate two cell. I do think it is more easy to do it in your spreadsheet. But the code is:

Code: Select all

args3(0).Value = thiscomponent.currentcontroller.getactivesheet.getCellByPosition(4,13).string & thiscomponent.currentcontroller.getactivesheet.getCellByPosition(8,13).string

[RPG]

I did remember that you can select a sheet by munber.

Code: Select all

sub LooksMorelikeExcel
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
'menu -->Edit -->Sheet
rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Tables"
args1(0).Value = Array(0)  

dispatcher.executeDispatch(document, ".uno:SelectTables", "", 0, args1())

rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ToPoint"
args2(0).Value = "K14"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())

dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "StringName"
args3(0).value = thiscomponent.currentcontroller.getactivesheet.getCellByPosition(4,13).string & thiscomponent.currentcontroller.getactivesheet.getCellByPosition(8,13).string
dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args3())

end sub

[Nocton]

Thank for all replies, but my question seems to have not been fully understood. To me, it is quite simple.
I want to create a macro to move to a sheet/page and then Protect or UnProtect it.
In Excel VBA I would have something like:

Code: Select all

Rem Protect Sheet
Sheets("Summary Accounts").Protect DrawingObjects:=False, Contents:=True, Scenarios:=False
Rem Unprotect Sheet
Sheets("Summary Accounts").Unprotect

all quite simple and easy to follow as sheets are named rather than numbered.

In LibreOffice I have recorded for protecting a sheet:

Code: Select all

dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Nr"
args2(0).Value = 2
dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args2())
rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "Protect"
args3(0).Value = true
dispatcher.executeDispatch(document, ".uno:Protect", "", 0, args3())

many more lines and not immediately clear which sheet is being referenced.
So I think I need to replace
args2(0).Name = "Nr"
args2(0).Value = 2
with something indicating "Summary Accounts". Everything I have tried ends up with the code asking me to select the sheet by name when the macro is run.

[DiGro]

Code: Select all

sub PROTECT_SHEET()
oSheet = ThisComponent.Sheets.getByName("Summary Accounts")
oSheet.protect("MY PASSWORD")
end sub

[Nocton]

Many thanks, DiGro. That's simple like VBA. Why does the recorder make such a complex issue of the matter?

[Nocton]

All working nicely now, but just one final tweak needed. I have the following code to protect the whole workbook:

Code: Select all

dim document   as object
dim dispatcher as object
rem Protect workbook
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Protect"
args1(0).Value = true
dispatcher.executeDispatch(document, ".uno:ToolProtectionDocument", "", 0, args1())

AOK except that it always asks for a password. How can I prevent that? In fact I am not using one so leave the field blank

[RPG]

I think there are different ways to select a table. In your case the best way maybe is to select a cell with the tablename as I did in the first post. Then Unprotect is working on that sheet. The dispatch is always working on the selected things.
This is not an answer on your last post. I did see it after posting.

[Lupp]

... That's simple like VBA. Why does the recorder make such a complex issue of the matter?

If you develop custom code for document automation in AOO or in LibO, and are coming with experiences from VBA you need to undertsand first of all that VBA (here Excel-VBA) is a programming language and the ApplicationPropgrammingInterface (API) for the respective MS-Office application at the same time. The successors of StarOffice (OO.o, AOO, LibO) have a completely different concept:
The included API is something like a collection of "services" and related "interfaces" for using them. Every programming language (C++, C#, Java, Javascript, Python, ???) for which somebody created a sufficient "bridge" to the API can be used to create custom code. The included BASIC is a rather simple (poorly featured) programming language. Its main purpose just is to call API services. And at this point it's OK, and sometimes preferrable: The bridge is short and wide, and some frequently needed elements are represented using predefined names.
The specialised BASIC has partly experimental Options

Code: Select all

Option Compatible
Option VBAsupport 1

trying to support the execution of BASIC code the way any VBA would do, respectively even as if the Excel API were avaiable.
Compare the sizes of an Excel .exe and of the executable of "our" software (including all the components!) and you will see that this can't be perfect.
But if you want to try the Option VBAsupport 1 with running your VBA code directly, you should try a recent LibreOffice (a portable version without installation would do). The LibO branch of development did more for compatibility.
One point more: The macro recorder of "our" software is an additional feature. It doesn't create BASIC code actually equivalent to written code, but just creates some lines allowing to execute dispatcher commands (.uno:command). Trying to do so it often has to create much more lines than hoped for and may end up with non-satisfying code needing manual reworking. In specific the recorder is not a logger for key-pressings and mouse-clicks.

If you consider to develop user code for "our" software more often, you should not start with recorded macros. They are misleading, and written macros very rarely have a reason to use the dispatcher.

Study the basics of BASIC usage in OpenOffice based on the famous texts by Andrew Pitonyak. They describe the (rather simple) BASIC features always regarding the special needs of API usage, and this way even teach the API usage more thoroughly than the BASIC.
See https://www.pitonyak.org/oo.php .

[Nocton]

Thank you for your extensive explanation of the matter, Lupp. And for reminding me of Andrew Pitonyak's work. It is quite some years since I did any extensive coding for OO Base applications, so I am just now reminding myself.

In answer my last question, to protect the workbook without asking for a password, the solution is:

Code: Select all

oDoc = ThisComponent
oDoc.protect("")

[Lupp]

All working nicely now, but just one final tweak needed. I have the following code to protect the whole ...

...spreadsheet document. ("Worksheet" and Workbook" are special Excel speak.)
As already suggested: Omit the dispatcher.

AOK except that it always asks for a password. How can I prevent that? In fact I am not using one so leave the field blank.

Protecting a sheet "without a password" is done by using the empty string as the password.
"Empty string" isn't the same thing as "blank" or "leave out".

Suggested code:

Code: Select all

Sub testing()
REM Remove the "outcommenting" apostrope  from the line you want to execute.
'tryUnProtectAllSheetsWithEmptyPassword
'tryProtectAllSheetsWithEmptyPassword
End Sub

Sub tryProtectAllSheetsWithEmptyPassword()
calcDoc = ThisComponent
sheets = calcDoc.Sheets
For Each sheet In sheets
 tryProtectSingleSheetWithGivenPassword(sheet, "")
Next sheet
End Sub

Sub tryUnProtectAllSheetsWithEmptyPassword()
calcDoc = ThisComponent
sheets = calcDoc.Sheets
For Each sheet In sheets
 tryUnProtectSingleSheetWithGivenPassword(sheet, "")
Next sheet
End Sub

Sub tryProtectSingleSheetWithGivenPassword(pSheet As Object, pPwd As String)
On Local Error Goto fail
pSheet.protect(pPwd)
fail:
End Sub

Sub tryUnProtectSingleSheetWithGivenPassword(pSheet As Object, pPwd As String)
On Local Error Goto fail
pSheet.unprotect(pPwd)
fail:
End Sub

BTW: Please consider to edit the subkject line of your first post here. "Naming" is generally used for "giving a n ame" and therefore misleading if you want to get help with "Protect / unprotect sheets {identified by name or ...]."
In fact the sheet names are completely irrelevant concerning what you actually want to achieve for all the sheets as you told in your last post.

See also the attached example containing the code.

aoo110920ProtectUnprotectSheetsWithUserCode.ods

(9.08 KiB) Downloaded 572 times

Our recent posts crossed.
An addiotional remark: To protect the document is a different thing as compared to protecting all its sheets.

[Nocton]

Thank you again, Lupp. I've changed the thread heading as you suggested.