Saving current document with password from macro

English
,
#18

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

#19

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.

image

#20

Thank you very much, I’ll definitely look it that. However, @Lupp’s answer sorta worked, I’m just trying to make it more general.

#21

If you find a way to generate these four arrays of numbers - PackageSHA256UTF8EncryptionKey, PackageSHA1UTF8EncryptionKey, PackageSHA1MS1252EncryptionKey and PackageSHA1CorrectEncryptionKey - with a given password, and post the code here with explanations as an answer to your question, I’ll gladly upvote your answer.
@Lupp’s code should work well and do its job. But you need to check for what types of files it works. I have no doubt that for native Calc files this works well. But will it work for excel files? After all, your task is to generate xlsx, isn’t it?

#22

I looked for the “call macro via command line” myself in AP’s texts and didn’t find much more as already posted - and by that much less than I had expected.
May be kind of a rarely used track.
You surely already looked at soffice.exe -? ?
If you find a way to get the complete commandline from inside any instance created by the call of soffice.exe, please tell me.

#23

So this is my script so far

sub save(inputFile as String, outputFile as String, password as String)
	
	dim doc as Object

	dim inputFileURL as string
	inputFileURL  = "file://" & inputFile
	
	dim outputFileURL as String
	outputFileURL  = "file://" & outputFile

	Dim saveEncryptedArgs(1) As New com.sun.star.beans.PropertyValue

	saveEncryptedArgs(0).Name  = "Password"
	saveEncryptedArgs(0).Value = password
	
	doc = stardesktop.LoadComponentFromURL(inputFileURL, "_blank", 0, Array())
	doc.storeAsURL(outputFileURL, saveEncryptedArgs)
	doc.close(True)

end sub

It mostly works, I can call it (from the terminal/bash) like so

soffice "macro:///Standard.Module1.save(\"[path to input document]\", \"[path to output document]\", \"[password]\")"

  1. Is there any way to call this macro using relative paths?
    instead of using the fully qualified path, do something like “./document.xlsx”?

  2. Is there a way to load a document from disk, without having to bring it up on the screen?
    When I call stardesktop.LoadComponentFromURL it opens the document in a new window, is there a way to load the document without opening it (visibly)?

You can use

	dim loadComponentProperties(0) As New com.sun.star.beans.PropertyValue

	PloadComponentPropertiesrops(0).Name= "Hidden"
	loadComponentProperties(0).Value= True

	doc = stardesktop.LoadComponentFromURL([path to input file], "_blank", 0, loadComponentProperties)

To “hide” the loaded document

#24

I by no means have any idea what I’m really doing, I’ve only started using LibreOffice yesterday.

I don’t exactly understand what you mean by “complete commnadline”.

#25

Seems there was a misunderstanding. Sorry.

#26

I don’t know which article came first: this one, which is tagged 2019-10-24, or this November 15, 2021. But there is a link here and there to Pitonyak’s book - if you are just starting to teach LibreOffice to do useful things with macros, then I highly recommend downloading the book and having it handy. For example, Listing 5.111: Load hidden then setVisible answers your question “how to quietly open a document” - ah, I see, yuo already found this

#27

If you want to do that with Ecel files, a license of MS Office should be affordable. Calc is an alternative to MS Excel but no replacement. This is not an alternative OOXML editor. As a matter of fact, OOXML has been introduced to fight Open Document Format (ODF).

#29

The problem is, is that we have to use LibreOffice since were on Linux.
Using a VM is not an option so LibreOffice is out only option

#34

Please never construct URLs like that, even on Linux. If you have a system path, use ConvertToURL.

Of course, your problem has nothing to do with the incorrect URL. The problem is that the script saves as ODF, regardless of the extension. You need to define the format in the args, as @Villeroy hinted.

    Dim saveEncryptedArgs(1) As New com.sun.star.beans.PropertyValue
    Dim EncryptionData(1) As New com.sun.star.beans.NamedValue

    EncryptionData(0).Name = "CryptoType"
    EncryptionData(0).Value = "Standard"
    EncryptionData(1).Name = "OOXPassword"
    EncryptionData(1).Value = password

    saveEncryptedArgs(0).Name = "FilterName"
    saveEncryptedArgs(0).Value = "Calc MS Excel 2007 XML"
    saveEncryptedArgs(1).Name = "EncryptionData"
    saveEncryptedArgs(1).Value = EncryptionData
#35

Thanks for the reply, I’m off work now but I’ll try that tomorrow.
There’s a strange problem, whenever I tried specifying “Calc MS Excel 2007 XML” as the format, it always creates the document without encryption.
I’ll let you know soon

#36

I’m sure you’ll be fine tomorrow.
I just created this file PleaseDontForgetPassword_NoWar.xlsx (8.5 KB)
using the command line

"C:\Program Files\LibreOffice\program\soffice.exe"  --headless --nologo "macro:///storeToXLSX.storeToXLSX.storeToXLSX(C:\TEMP\DemoPWD.ods,C:\Tests\PleaseDontForgetPassword_NoWar.xlsx,NoWar)

Yes, this is a command for Windows, but for your OS it will differ only in paths and file names.
By the way, how do you check that the resulting XLSX file will not open without a password? And then after all, most of us do not have Excel, Calc is enough for us.
By the way, why don’t you send ODS files to your clients with a recommendation to install LibreOffice?

2 Likes
#37

If you try and open an encrypted document, LibreOffice/MS Word will give you a prompt to enter a password. Plus if you look at the contents of an encrypted documents it’s complete garbage until it’s decrypted.

For the same reason I can’t tell them to decrypt using GPG, it’s just not my call to make.

#38

After all, it may happen that LibreOffice will require a password, and MS will simply open the file … Once upon a time, OpenOffice did this with MS files - it simply opened them, ignoring all protections. :slight_smile:

I liked the position that my house management took:
We work at the expense of residents’ payments, collect and store information about them in our computers. The person who paid us for this work has the right at any time to demand from us a summary - what kind of information we have collected about him (payment history, execution of his applications, etc.). We can give him such a certificate electronically. For example, in the form of several sheets in an Excel workbook. However, to see this information, a person will have to buy Excel. In other words, a person who has already paid money will be forced to pay more due to our data format. We push him to additional costs. On the other hand, if we give him data in the Open Data Format, he will be able to find out the contents of the given file almost for free (well, he will need a computer anyway).
Sorry for making you read so much text. But it seems to me that everyone should consider this position.

#39

Our customers are mostly comprised of businesses/factories with laboratories, not individuals.
Almost all of them use Windows, and by extension MS-word. So me telling them to install another office program just to decrypt their data which we create, is not something I can do. I’m just a developer.

Don’t worry about it, I do appreciate the input

#40

@JosefLintz You mentioned the MSWord a second time. It’s a typo? From the very beginning, it was about XLSX files, Excel workbooks. If the output file is needed in Word format, then the filter name must be different, “MS Word 2007 XML”

#41

We are creating spreadsheets with the “.xlsx” file extension using C++, LibreOffice macro API is used to encrypt them (Unless something else is found).
When I say MS-word, I mean the LibreOffice equivalent for Microsoft/Windows. That’s the program our customers use to open the spreadsheet we send them.

Apologies if something’s unclear I’m fairly new to this whole spreadsheet thing

#42

MS Word is an analog of LibreOffice Writer. For LibreOffice Calc, there is MS Excel. And the whole LibreOffice suite’s MS analog is called MS Office.

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.