[Solved] Macro to Copy Cell Contents
[Solved] Macro to Copy Cell Contents
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.
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.
Last edited by MrProgrammer on Wed Jan 25, 2023 11:02 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved] since the recorded macro worked -- MrProgrammer, forum moderator
Reason: Tagged ✓ [Solved] since the recorded macro worked -- MrProgrammer, forum moderator
Re: Macro to Copy Cell Contents
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.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: Macro to Copy Cell Contents
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. |
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: Macro to Copy Cell Contents
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.
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.
OpenOffice 4.1.7 Windows 10 Home
Re: Macro to Copy Cell Contents
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
Last edited by Zizi64 on Sun Nov 20, 2022 7:28 pm, edited 1 time in total.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: Macro to Copy Cell Contents
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
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
Re: Macro to Copy Cell Contents
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.
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.
OpenOffice 4.1.7 Windows 10 Home
- Hagar Delest
- Moderator
- Posts: 32670
- Joined: Sun Oct 07, 2007 9:07 pm
- Location: France
Re: Macro to Copy Cell Contents
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...
LibreOffice 24.2 on Xubuntu 24.04 and 7.6.4.1 portable on Windows 10
Re: Macro to Copy Cell Contents
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.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
Re: Macro to Copy Cell Contents
You'd get that error if you put
somewhere. You can't do that... you don't declare it...
Code: Select all
dim activecell
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)