AutoHotkey Community

Let's help each other out
https://www.autohotkey.com/boards/

Hotstring paste to Excel cell

https://www.autohotkey.com/boards/viewtopic.php?f=82&t=117697

Page 1 of 1

Hotstring paste to Excel cell

Posted: 26 May 2023, 14:55
by B1T
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
}

Re: Hotstring paste to Excel cell

Posted: 26 May 2023, 15:16
by mikeyww
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
}

Re: Hotstring paste to Excel cell

Posted: 26 May 2023, 16:31
by Datapoint
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

Re: Hotstring paste to Excel cell

Posted: 26 May 2023, 20:00
by flyingDman
I would use:

Code: Select all

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

Re: Hotstring paste to Excel cell

Posted: 27 May 2023, 06:57
by B1T
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

Re: Hotstring paste to Excel cell

Posted: 27 May 2023, 07:12
by boiler
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.

Re: Hotstring paste to Excel cell

Posted: 27 May 2023, 07:14
by mikeyww
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.

Re: Hotstring paste to Excel cell

Posted: 28 May 2023, 14:26
by B1T
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!

Re: Hotstring paste to Excel cell

Posted: 28 May 2023, 16:58
by mikeyww
You can paste your revised script in a new reply below. Indicate what happens when you run it, and what should happen instead.

Re: Hotstring paste to Excel cell

Posted: 29 May 2023, 06:29
by B1T
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.

Re: Hotstring paste to Excel cell

Posted: 29 May 2023, 06:59
by mikeyww
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
}

Re: Hotstring paste to Excel cell

Posted: 29 May 2023, 08:17
by boiler
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.

Re: Hotstring paste to Excel cell

Posted: 30 May 2023, 16:29
by B1T
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

Re: Hotstring paste to Excel cell

Posted: 30 May 2023, 17:02
by flyingDman
If you want to
create more complex excel AHK
scripts, you will not be able to avoid COM ...

Re: Hotstring paste to Excel cell

Posted: 30 May 2023, 17:52
by boiler
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.

Re: Hotstring paste to Excel cell

Posted: 31 May 2023, 14:17
by B1T
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

Re: Hotstring paste to Excel cell

Posted: 31 May 2023, 16:19
by Datapoint
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.

Re: Hotstring paste to Excel cell

Posted: 05 Jun 2023, 11:43
by B1T
Very useful info on COM and Editors - Many thanks Datapoint!
All times are UTC-05:00
Page 1 of 1
Powered by phpBB® Forum Software © phpBB Limited
https://www.phpbb.com/