Basic Macro: Cannot paste .uno result into spreadsheet

I wrote a Basic macro to calculate the Hash code of a given file name. It then pastes the hash code into the active cell of the document. This is my macro:

function GetHash(filename)
	Dim myCommand as String
	Dim fullCommand as String
	myCommand = "/usr/bin/sha256sum " & filename
	fullCommand="-c "" " & myCommand & " | xclip -selection clipboard"" "
	MsgBox("bash " & fullCommand)
	Shell ("bash", 1, fullCommand, true)
	dim document   as object
	dim dispatcher as object
	document   = ThisComponent.CurrentController.Frame
	dispatcher = createUnoService("")
	dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
End function

I just call it with =GETHASH(C2)

My problem: If I execute this, it says that locked cells cannot be changed. However, neither the sheet nor the cell is protected. How can I have this paste the result of my shell call into the cell that is calling the macro?

1 Like

You have Python at hand. Build a useful extension with spreadsheet functions returning all kinds of hashes and publish it to the extension site. Fumbling with Basic and shell commands is too ugly.

In addition to the previous answer.
Functions that are called from cell formulas have additional restrictions. One of them is that they should not change any cells.
In your case, the function should return the value that will be displayed in the cell.
If necessary, you can redirect the output of an external utility to a (temporary) file, then read the contents of the file from your function and return the appropriate value.

1 Like

Thank you, now I understand why it doesn’t accept writing back the value (and thus overwriting the formula).

Indeed, having the function returning the value would be the first choice, but apparently it is not possible to get the stdout output of a Shell() command, unless one pipes it into a file and then reads the file. This is a slow process if you have hundreds of files like in my case. Anyway…

I guess I will do it that way - piping the result of each command into a file and then read the file and have the function return it. Thank you @sokol92 and @Lupp for your answers!

1 Like

You can’t. The cell is containing the formula.
Not discussing any pros and cons: Just for a test if the expected result would be pasted, select an empty cell of a different sheet before ordering full recalc (Shift+Ctrl+F9). .uno:Paste (and many commands) act depending on the CurrentSelection.