There is a macro shipped with the office suite. You find it under Tools>Macros>Organize>Basic…
Container “LibreOffice Macros”
Library Tools
Module Misc
Sub ProtectSheets and UnprotectSheets
This is a completely different task.
I see what you mean now, I don’t (exactly) want the cells themselves being protected against modification.
I want the entire file to be password protected, as if you saved with a password
I just need a VB script which can password protect a spreadsheet.
Specifically a script which can be later called by a separate program (Not libreOffice)
Use an external encription program such as pgp.
That’s what I did initially, the problem is that LibreOffice cannot parse pgp-encrypted documents properly.
And I don’t think the customers would appreciate me telling them to use the terminal to decrypt their spreadsheets
Pgp decrypts all of your pgp-encrypted files. One program to encrypt and decrypt your files properly regardless of file types.
Right, but the customer already has a spreadsheet program which can decrypt files.
I’m just not in a position to tell the customers to install new software just to decrypt their data, which we create and encrypt
You don’t have to use that feature. Better use pgp.
We were very close to a solution, but I just discovered a fairly serious problem.
This “solution” only works for LibreOffice, if I try to open it from Microsoft Word, or Word-online, it just doesn’t work.
I also tried this suggestion, it creates the file without password, no matter what.
Any help is very appreciated, I’m out of options
Why should MS run our code? They hardly support our document format.
Simply use an encryption program for encryption and use your office suite to generate office documents in the native file format of the program you are using. Generating MS Office documents with LibreOffice makes no sense.
I’ll try to explain the problem in more detail.
We’re generating “.xlsx” documents from C++ using a library called QXlsx.
The problem is, is that library doesn’t allow saving documents with passwords, I tried using Xlnt, but their encryption scheme isn’t supported by LibreOffice or MS office, meaning that neither of those program can decode the document after encrypting.
The only way I found to encrypt a document from C++ is to use this macro in LibreOffice, and call soffice with the “macro” arguments .
Now the problem is, is that macro does work, it generates encrypted documents, but only LibreOffice can decode them. I tried MS office, and word-online, this is where I am at right now
This means that your problem has absolutely nothing to do with LibreOffice.
Well, I am generating the encrypted documents through LibreOffice’s macros.
Which only LibreOffice can open(the documents), it might not be at fault, maybe my script is incorrect.
That’s the I need help with
Stroring to encrypted files by unexperienced users is a frequent cause of data loss. No remedy then!
If you feel sure to know what you are doing, you can use the code below to start. (LibreOffice Basic isn’t “VBA”.)
Sub saveEncrypted(Optional pPwd As String)
If IsMissing(pPwd) Then pPwd = "encrypted"
doc = ThisComponent
cURL = doc.URL
If (cURL="") OR NOT (FileExists(cURL)) Then
Print "A complete existing URL is needed in the example."
REM Of course, the URL to store to can be any URL the calling
REM user has the right to write to.
Exit Sub
End If
Dim saveEncryptedArgs(0) As New com.sun.star.beans.PropertyValue
saveEncryptedArgs(0).Name = "Password"
saveEncryptedArgs(0).Value = pPwd
doc.storeAsURL(cURL, saveEncryptedArgs)
doc.close(True)
End Sub
My extra problem is to understand for what reason this should be done by a “macro”.
This seems promising, one question.
If I save this file somewhere on disk, can I use LibreOffice’s CIL to run this script with arguments?
I’m fairly new to LibreOffice, maybe “Macro” isn’t the right term.
I need a script which can be called by LibreOffice or some other program and given the right arguments
It was not about terms. “Macro” or “script” both are good enough here. My idea was that somebody creating files can save them one or another way manually.
You surely can’t simply run “this script” to complete your task. It only demonstrates the API method and the encryption-related argument you need for every single case of application.
If your question is about a kind of batch processing like “Read all the files from a given folder, filter them basded on their names, and then conditionally store them to an encrypted version.” the requirements can be very different.
In principle a LibO document (spreadsheets most likely) can be used as such a “batch processor” when called with a command line giving the macro to start …
I would expect complications, but first of all I’m not the one with a lot of experience concerning the usage of soffice.exe with commandline options.
soffice.exe macro:///standard.module1.macro1 is the “template” for the case.
Read Andrew Pitonyak’s famous texts and you will find lots of information and examples…
Thanks for the suggestion, if you can actually run scripts from the terminal it’s a game changer.
Also, I discovered you can record macros, so I recorded the “save-as-with-password” macro, and got this.
dim document as object
dim dispatcher as object
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim args1(2) as new com.sun.star.beans.PropertyValue
args1(0).Name = "URL"
args1(0).Value = "file://[location of output file]"
args1(1).Name = "FilterName"
args1(1).Value = "Calc MS Excel 2007 XML"
args1(2).Name = "EncryptionData"
args1(2).Value = Array(Array("OOXPassword","[password]"))
dispatcher.executeDispatch(document, ".uno:SaveAs", "", 0, args1())
The problem is, is that it just doesn’t work. It creates the new document, but without password
You probably wanted to phrase it differently: It works great if used correctly. Unfortunately, to do this correctly, I’ll have to study a lot of material related to information encryption.
One single "OOXPassword" is not enough. Actually the macro should contain something like this:
args1(1).Name = "FilterName"
args1(1).Value = "Calc MS Excel 2007 XML"
args1(2).Name = "EncryptionData"
args1(2).Value = Array(Array("OOXPassword","Sophia981"), _
Array("CryptoType","Standard"), _
Array("PackageSHA256UTF8EncryptionKey", _
Array(-57,-100,-82,43,71,-90,-102,-87,-76,110,109,-17,38,-23,-102,39,-128,-67,-96,-20,-4,29,-108,54,86,61,-29,-89,-25,-33,-97,-28)), _
Array("PackageSHA1UTF8EncryptionKey", _
Array(-23,-76,75,-66,32,-92,14,104,72,63,109,2,-84,127,-74,-29,-63,122,119,124)), _
Array("PackageSHA1MS1252EncryptionKey", _
Array(-23,-76,75,-66,32,-92,14,104,72,63,109,2,-84,127,-74,-29,-63,122,119,124)), _
Array("PackageSHA1CorrectEncryptionKey", _
Array(-23,-76,75,-66,32,-92,14,104,72,63,109,2,-84,127,-74,-29,-63,122,119,124)))
LibreOffice perfectly generates all these obscure numbers on its own. It remains to understand HOW EXACTLY this happens and the macro will create Excel workbooks with password protection.
