[Solved] Macro to Copy Cell Contents

[arc1950]

A friend wants a spreadsheet to store her usernames and passwords, she uses OpenOffice. Rather than having to highlight the cell and then Ctrl+C I want to be able to place the cursor in the cell and then press a button which will copy the contents to the clipboard, she can then go to the website and paste it.

I am not an OpenOffice user so I created it in Excel and then tried to open it OpenOffice, the macro doesn't work, I tried converting the macro on online conversion sites, I've Googled but nothing works.

There is a macro in OpenOffice called AutoCopy2Clip but this doesn't seem to do anything and there is nothing in the Help.

In Excel the macro is:
Sub CopyCell()
ActiveCell.Copy
End Sub

Using https://www.business-spreadsheets.com/vba2oo.asp the converted Excel macro to OpenOffice is:
Sub CopyCell()
ThisComponent.getCurrentSelection.Copy
End Sub

OpenOffice displays an error message:
BASIC runtime error.
Property or method not found
It's highlighting "ThisComponent.getCurrentSelection.Copy"

There must be a similar macro that works in OpenOffice. I have never used OpenOffice so please keep it simple.

The Excel spreadsheet looks like this:



I place the cursor in any cell, click the Copy button and it copies the contents to the clipboard.

Thank you in advance.

[robleyd]

Rather than (poorly?) reinventing the wheel, you might suggest to your friend that there is software dedicated to storing passwords securely - see for example Bitwarden. It will allow you paste stored username and password.

[robleyd]

A quick search of the forum found this topic which seems to be very similar to what you want.

Edit: Of course there is an existing option, requiring two mouse clicks. Right click the cell and select Copy.

[arc1950]

Thank you for your reply.

I have mentioned using a password manager but she doesn't want her information stored online even though most reputable organisations use encryption. I've found one that stores the info locally but she doesn't like it. She wants a spreadsheet.

[Zizi64]

Recorded macro (The Macro Recorder uses the Dispatcher):

Code: Select all

sub MyCopy
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 ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

end sub

[JeJe]

ActiveCell.Copy works but you have to put Option VBASupport 1 (turns on greater compatibility with Excel) at the top of the module.

Code: Select all

Option VBASupport 1

Sub CopyCell()
ActiveCell.Copy
End Sub 

[arc1950]

Many thanks to Zizi64 and JeJe

I tried the JeJe "Option VBASupport 1" but it gave the message:
BASIC runtime error '91'
Object variable not set

I then tried Zizi64 "MyCopy" macro and that worked perfectly.

Thank you to everyone who posted replies.

[Hagar Delest]

I have mentioned using a password manager but she doesn't want her information stored online even though most reputable organisations use encryption. I've found one that stores the info locally but she doesn't like it. She wants a spreadsheet.

KeepassXC is a very good one that integrates very well in Windows or GNU/Linux. Very handy when it fills in the fields automatically when asked to.

I hope that her file is at least password protected. Else, that's a serious breach of security...

[JeJe]

I tried the JeJe "Option VBASupport 1" but it gave the message:
BASIC runtime error '91'
Object variable not set

Works for me on OO 4.1.11 on Windows 10 (not home) - but so long as you've got something you're happy with.

[JeJe]

You'd get that error if you put

Code: Select all

dim activecell

somewhere. You can't do that... you don't declare it...