Hotstring paste to Excel cell

Get help with using AutoHotkey (v2 or newer) and its commands and hotkeys
B1T
Posts: 9
Joined: 26 May 2023, 13:56

Hotstring paste to Excel cell

Post by B1T » 26 May 2023, 14:55

Hi all.
I’m new to AHK and need some help creating a hotstring for use with excel.

I would like to paste content I have in a variable (clipboard text I re-formatted).
Using MsgBox, I can see the text is exactly as I want it but when I use my hotstring in a cell, it spills to rows below (there are multiple sentences in my variable).

How do I stop it from spilling to multiple cells?

My code:

Code: Select all

#Requires AutoHotkey v2.0
#SingleInstance Force ;Only allow one instance and with Force replaces old instance.

::#sn::
{
    clipsaved := A_Clipboard
    loop
    {
        clipsaved:= StrReplace(clipsaved,  "`r`n`r`n", "`r`n",, &Count)
        if (Count = 0)
            break
    }
    clipsaved := FormatTime(,"dd/M - ") . clipsaved
msgbox clipsaved
    sendtext clipsaved
}

User avatar
mikeyww
Posts: 26591
Joined: 09 Sep 2014, 18:38

Re: Hotstring paste to Excel cell

Post by mikeyww » 26 May 2023, 15:16

Welcome to this AutoHotkey forum!

Does this help?

Code: Select all

#Requires AutoHotkey v2.0

txt := '
(
abc
def
ghi
)'

#HotIf WinActive('ahk_exe EXCEL.exe')
F3::excelPaste(txt)
#HotIf

excelPaste(str) {
 For c in ComObjActive('Excel.Application').Selection
  c.Value := str
}

User avatar
Datapoint
Posts: 294
Joined: 18 Mar 2018, 17:06

Re: Hotstring paste to Excel cell

Post by Datapoint » 26 May 2023, 16:31

Here are two other possible solutions.
You could paste the text instead.

Code: Select all

	...
	clipsaved := FormatTime(,"dd/M - ") . clipsaved
	A_Clipboard := clipsaved
	Send "^v"
Or you could remove the CR characters and replace LF characters with Alt+Enter.

Code: Select all

	...
	clipsaved := FormatTime(,"dd/M - ") . clipsaved
	clipsaved := StrReplace(clipsaved, "`r")
	clipsaved := StrReplace(clipsaved, "`n", "!{Enter}")
	Send clipsaved

User avatar
flyingDman
Posts: 2791
Joined: 29 Sep 2013, 19:01

Re: Hotstring paste to Excel cell

Post by flyingDman » 26 May 2023, 20:00

I would use:

Code: Select all

F3::ComObjActive("Excel.Application").activecell := "abc`ndef`nghi"
14.3 & 1.3.7

B1T
Posts: 9
Joined: 26 May 2023, 13:56

Re: Hotstring paste to Excel cell

Post by B1T » 27 May 2023, 06:57

Thank you all for the quick fantastic response, with different solutions. I tried them all to increase my learning and had mixed results.

Datapoint – Both worked and easy to implement – Thanks.

Mikeyww & flyingDman – Tried these but did not work for me, for both solutions, I got the following error message for the ComObjActive:
Error: (0x80010001) Call was rejected by callee.

Would love to make this work, do I need something else set up?
Code (altering last line for either excelPaste1 or excelPaste2:

Code: Select all

excelPaste1(str)
{
    For c in ComObjActive('Excel.Application').Selection
       c.Value := str
}

excelPaste2(str)
{
    ComObjActive("Excel.Application").activecell := str
}

#HotIf WinActive('ahk_exe EXCEL.exe')
::#sn1::
{
    clipsaved := A_Clipboard

    loop
    {
        clipsaved:= StrReplace(clipsaved,  "`r`n`r`n", "`r`n",, &Count)
        if (Count = 0)
            break
    }
    clipsaved := FormatTime(,"dd/M - ") . clipsaved
    excelPaste1(clipsaved)
}
#HotIf

User avatar
boiler
Posts: 16767
Joined: 21 Dec 2014, 02:44

Re: Hotstring paste to Excel cell

Post by boiler » 27 May 2023, 07:12

B1T wrote: Mikeyww & flyingDman – Tried these but did not work for me, for both solutions, I got the following error message for the ComObjActive:
Error: (0x80010001) Call was rejected by callee.
One potential issue is that if your installation of Excel is not registered with Microsoft, then COM doesn't work, according to some.

User avatar
mikeyww
Posts: 26591
Joined: 09 Sep 2014, 18:38

Re: Hotstring paste to Excel cell

Post by mikeyww » 27 May 2023, 07:14

I think the issue is that the cell is being edited.

Code: Select all

excelPaste1(str) {
 Send '{Esc}'
 ComObjActive('Excel.Application').ActiveCell := str
}
In any case, if pasting or sending keys works, then the choice of approach might not matter much.

B1T
Posts: 9
Joined: 26 May 2023, 13:56

Re: Hotstring paste to Excel cell

Post by B1T » 28 May 2023, 14:26

Thanks for your continued support.

boiler – Using work laptop with Microsoft® Excel® for Microsoft 365 MSO (Version 2208 Build 16.0.15601.20660) 32-bit, fully licenced and all patched applied automatically.

mikeyww – Slight improvement after implementing your changes in that when I reload the script, it works first time but never again until I reload the script again. Very odd behaviour!

User avatar
mikeyww
Posts: 26591
Joined: 09 Sep 2014, 18:38

Re: Hotstring paste to Excel cell

Post by mikeyww » 28 May 2023, 16:58

You can paste your revised script in a new reply below. Indicate what happens when you run it, and what should happen instead.

B1T
Posts: 9
Joined: 26 May 2023, 13:56

Re: Hotstring paste to Excel cell

Post by B1T » 29 May 2023, 06:29

The revised script:

Code: Select all

#Requires AutoHotkey v2.0
#SingleInstance Force ;Only allow one instance and with Force replaces old instance.

excelPaste1(str)
{
    Send '{Esc}'
    ComObjActive('Excel.Application').ActiveCell := str
}

#HotIf WinActive('ahk_exe EXCEL.exe')
::#sn1::
{
    clipsaved := A_Clipboard
    loop
    {
        clipsaved:= StrReplace(clipsaved,  "`r`n`r`n", "`r`n",, &Count)
        if (Count = 0)
            break
    }
    clipsaved := FormatTime(,"dd/M - ") . clipsaved
    excelPaste1(clipsaved)
}
#HotIf
Several lines of text copied to clipboard.

If AHK script is reloaded, then when I typed #sn1 into any excel cell, my clipboard text is correctly copied to the cell with no issues.

If #sn1 is then typed into another cell (does not matter whether clipboard is reloaded with new text) I get the following message:

Error: (0x80010001) Call was rejected by callee.

Specifically: ActiveCell

005: {
006: Send('{Esc}')
▶ 007: ComObjActive('Excel.Application').ActiveCell := str
008: }
010: {

Call stack:
C:\...\AutoHotkey\AHK_Test_Excel.ahk (7) : [excelPaste1] ComObjActive('Excel.Application').ActiveCell := str
C:\...\AutoHotkey\AHK_Test_Excel.ahk (21) : [<Hotkey>] excelPaste1(clipsaved)
> ::#sn1

If AHK script is once again reloaded, then all is good again, but never works again unless AHK script is reloaded.

User avatar
mikeyww
Posts: 26591
Joined: 09 Sep 2014, 18:38

Re: Hotstring paste to Excel cell

Post by mikeyww » 29 May 2023, 06:59

I reproduced your problem. I think it still relates to the fact that the cell is being edited. A Sleep seemed to fix it.

Code: Select all

excelPaste1(str) {
 Send '{Esc}'
 Sleep 100
 ComObjActive('Excel.Application').ActiveCell := str
}
An alternative is to use a hotkey instead of a hotstring.

The function below also worked, as SendEvent includes a 10 ms key delay.

Code: Select all

excelPaste1(str) {
 SendEvent '{Esc}'
 ComObjActive('Excel.Application').ActiveCell := str
}

User avatar
boiler
Posts: 16767
Joined: 21 Dec 2014, 02:44

Re: Hotstring paste to Excel cell

Post by boiler » 29 May 2023, 08:17

It might be worth noting to @B1T that you only need to select the cell — not edit it — for the script to work, in case that’s not clear. In fact, it’s better if you don’t edit it.

B1T
Posts: 9
Joined: 26 May 2023, 13:56

Re: Hotstring paste to Excel cell

Post by B1T » 30 May 2023, 16:29

Thank you for all the responses.
Adding a delay does indeed work but not sure exactly why. Its been great learning experience and this will help me create more complex excel AHKs.

For this particular case, I'll be using Datapoint's solution using Send "^v" as this append to the cell content which is what I need here.

Thanks again

User avatar
flyingDman
Posts: 2791
Joined: 29 Sep 2013, 19:01

Re: Hotstring paste to Excel cell

Post by flyingDman » 30 May 2023, 17:02

If you want to
create more complex excel AHK
scripts, you will not be able to avoid COM ...
14.3 & 1.3.7

User avatar
boiler
Posts: 16767
Joined: 21 Dec 2014, 02:44

Re: Hotstring paste to Excel cell

Post by boiler » 30 May 2023, 17:52

B1T wrote: ...append to the cell content which is what I need here.
COM can also do this and more reliably, and again you wouldn't have to edit the cell. It would get the current contents of the cell and replace them with the old contents with the new contents appended to it. You would save steps (not having to edit the cell) in addition to it being more reliable.

B1T
Posts: 9
Joined: 26 May 2023, 13:56

Re: Hotstring paste to Excel cell

Post by B1T » 31 May 2023, 14:17

Thanks for your comments on COM, I’ve managed to get both versions working.

Is there any info on how to use COM related functions (not just Excel) as so far, it’s been trial and error and sifting through other code to pick up things.

Currently using Notepad - Is there an editor that does not need installing (difficult to get software loaded onto work laptop).

Both my working versions are here if anyone needs to use/crib sections:

Code: Select all

#Requires AutoHotkey v2.0
#SingleInstance Force ;Only allow one instance and with Force replaces old instance.

::#sn:: ;Add processed clipboard content (add date and remove blank lines) to Excel cell.
{
    clipsaved := A_Clipboard
    loop ;Remove blank lines
    {
        clipsaved:= StrReplace(clipsaved,  "`r`n`r`n", "`r`n",, &Count)
        if (Count = 0)
            break
    }

    clipsaved := FormatTime(,"dd/M - ") . clipsaved ;Add date
    A_Clipboard := clipsaved
    send "^v"
}

;COM version below
excelPaste(str)
{
    Send '{Esc}'
    Sleep 100
    Cell := ComObjActive('Excel.Application').ActiveCell
    ComObjActive('Excel.Application').ActiveCell := Cell.value . str
}

#HotIf WinActive('ahk_exe EXCEL.exe')
::#snx::
{
    clipsaved := A_Clipboard
    loop
    {
        clipsaved:= StrReplace(clipsaved,  "`r`n`r`n", "`r`n",, &Count)
        if (Count = 0)
            break
    }
    clipsaved := FormatTime(,"dd/M - ") . clipsaved
    excelPaste(clipsaved)
}
#HotIf

User avatar
Datapoint
Posts: 294
Joined: 18 Mar 2018, 17:06

Re: Hotstring paste to Excel cell

Post by Datapoint » 31 May 2023, 16:19

B1T wrote:
31 May 2023, 14:17
Is there any info on how to use COM related functions (not just Excel) as so far, it’s been trial and error and sifting through other code to pick up things.
VBA is the language that you can write macros with in MS Office programs. If you google VBA there will be a TON of results. It's usually pretty easy to port VBA code to AHK because "COM" is just shorthand for connecting to the same objects that VBA uses.

Most of the AHK tutorials are still written for v1.1. I would still suggest looking at them because "expressions" are largely the same in v1 and v2, and COM works exclusively with expressions. Some of the AHK functions may have changed from v1 to v2 though.

Basic Ahk_L COM Tutorial for Excel This is the tutorial many recommend for Excel basics.
MS Office COM Basics Explains COM in general. It also has links to other resources.
B1T wrote:
31 May 2023, 14:17
Currently using Notepad - Is there an editor that does not need installing (difficult to get software loaded onto work laptop).
The Editors section of the forum has some to choose from. viewforum.php?f=60

Also, the AHK v2 installer gives you the option to install to the A_Appdata folder, which does not require admin rights. Installing programs in the Windows Program Files folder requires admin rights, but I imagine you may be able to install things to other folders like A_Appdata. Some installers may request admin rights anyway, so you would need a "portable" version for those and unzip them instead of installing them.

The editor I am most familiar with is SciTE4Autohotkey. It's one of the easier editors to get working with AHK right out of the box. For a copy you don't need to install you would download the "portable (.zip)" version from the website. viewtopic.php?f=61&t=104086 You need to unzip S4AHK in the correct folder relative to where AHK is installed (or unzipped for portable versions of AHK). If you do it properly you can switch between AHK versions within S4AHK, and when you press F1 it will open the AHK docs for the version you are using (you need to restart S4AHK if you switch versions). The way that works for me is to install (or unzip) v1, then install (or unzip) v2 to a folder named "v2" in the folder that v1 is installed. Then install (or unzip) S4AHK in the same folder that v1 is installed too.

You can look in the editors section of the forum to see if other editors have a portable version too. VS Code seems to have a .zip download.

Some editors are written in AHK, so I think you may be able to run them without installing but they are only for v1 at the time of this post as far as I know.


Quick edit and sidenote: I don't think you necessarily need COM for the task that is the subject of this thread. Comments that COM "is more reliable" are generally true, but for this task just pasting the clipboard will probably be more reliable than dealing with all the "what ifs" that COM introduces to an otherwise simple task.

B1T
Posts: 9
Joined: 26 May 2023, 13:56

Re: Hotstring paste to Excel cell

Post by B1T » 05 Jun 2023, 11:43

Very useful info on COM and Editors - Many thanks Datapoint!

Post Reply

Return to “Ask for Help (v2)”