Simple numpad remapping problem in excel.

[cheddarhawk]

I just bought a USB Numerical Keypad and was hoping to use it to enter times in excel. I'm planning to remap either the '00' or '*' button on the external kepad to input ":" in order to quickly enter times (hh:mm:ss), but i'm coming across an issue in excel. If there isn't anything entered into a cell, either button will be replaced with the desired ":", which is almost useful. But if there is already something entered in the cell, like the first two digits in a time , the original value of '00' or '*' will be entered instead of the desired ":". Is there anyway to make my simple hotkey script work with entering times in excel?

:*:00:::

:*:*:::

Thanks!

[TLM]

Sorry for the slow response my plane came as I was about to reply.

In Excel almost nothing is simple
While this seems a little overkill, it does work for me using COM.
The upside is it's more reliable and also works in the background

Code: Select all

xlObj					:= ComExcelInit()
date 					:= "01/01/2019"

:*:00::

ControlSend,, {esc}, ahk_class XLMAIN

CellObj 				:= xlObj.Selection
CellObj.NumberFormat 	:= "dd:mm:yy"

xlObj.Cells( CellObj.row, CellObj.column ).value := date

return

; ###############
; ## functions ##
; ###############

ComExcelInit()
{
	try
	{
		return ComExcelConnect( "ahk_class XLMAIN" ).Application
	}
	catch
	{
	    msgbox, 0x10, whoops!, Error connecting to Excel!
	    ExitApp
	}	
}

ComExcelConnect( WinTitle )
{
	ControlSend,, {esc}, ahk_class XLMAIN
    objID    := "-16", objID &= 0xFFFFFFFF
    refID    := -VarSetCapacity( iid, 16 )
    iid_type := NumPut( 0x46000000000000C0
             ,  NumPut( 0x20400, iid, "Int64" )
             ,  "Int64")

    ControlGet, hwnd, hwnd, , EXCEL71, % WinTitle
    DllCall( "oleacc\AccessibleObjectFromWindow", ptr, hwnd, uInt, objID, ptr, refID+iid_type, "ptr*", pObj )

    return ComObjEnwrap( 9, pObj )
}

Please don't let the functions scare you off and ask if you have any questions.

[Tigerlily]

I wasn't successful making the 00 into a hotstring because the first 0 erases the contents of the cell. WIth more work I could probably figure it out but I'll leave that to you, an alternative may be to see if the "00" key registers as a special key, and assign it as a hotkey instead of a hotstring.

AHK Special Keys Assignments

A little slimmer of an answer using Excel COM and Edit controls using * as an auto-hotstring:

Code: Select all

#SingleInstance, force
SendMode, Input

:?*:*::											;	Sets "*" as hotstring - executes upon initial keypress even if preceded by an alphanumeric character (aka inside of another word or string of numbers)
try Xl := ComObjActive("Excel.Application") 	;	Try to connect to Active Excel instance
try Xl_Hwnd := Xl.HWND							; 	Try to set unique Window Handle to a variable
ControlSend, EXCEL71, {F2}, ahk_id %Xl_Hwnd% 	; 	Move Edit Control Focus to Excel's formula bar (aka editing inside the cell)
Send, :											;	Send a colon
return

It will work whether you are actively editing inside the cell or not passively selecting the cell.

Let me know how this works for you!

[Thoughtfu1Tux]

Why not do a direct remap of the key to the key you want it to output?

Code: Select all

NumpadMult::send,{:}

[Tigerlily]

Why not do a direct remap of the key to the key you want it to output?

Code: Select all

NumpadMult::send,{:}

Because the OP stated that they need to do this within Excel and Excel has it's quirks that you must work around / work with. Also, they said wanted to use a hotstring.

Your solution would not be reliable because the OP needs the ":" character each time to be entered in each cell without resetting/clearing the active contents of the cell, instead appending the ":" character to the end of what is currently there (if anything).

Test out our different solutions, and you will see the differences.

[Thoughtfu1Tux]

Excel does have its quirks, but it doesn't have any issues with a key remap like i mentioned in the post above. I do some bookkeeping and time card management for my company, and i remap keys to help put in the colon using the keypad all the time.
I tested the function you posted and my function, and they work equally well, I think mine is just simpler and easier to use. And if you use #ifwinactive it will only activate within Excel.

OP only has 1 post in his history, so he's probably very new to AHK and maybe didn't realize that a key remap would be simpler to do than a hotstring.

Code: Select all

#IfWinActive, ahk_class XLMAIN 	; Only activates if excel is open
	
NumpadMult::send,{:}	; When you press the Nupad Multiply key, AHK will catch command and send a {:} instead. 

#IfWinActive

[Tigerlily]

Hi Thoughtfu1Tux,

I like the simplicity of your solution. However, my only qualm is that if I have contents in my cell, programming the hotkey in such a way will discard the contents instead of appending it to the end of the cell's contents.

Code: Select all

active cell		-->	[these are the cell contents]
press hotkey	-->	[:]

vs.

Code: Select all

active cell		-->	[these are the cell contents]
press hotkey	-->	[these are the cell contents:]

My qualm with my method is that if using two instances of Excel simultaneously, could potentially cause issues with editing the active Excel window due to COM.

We could combine our approaches to make something that is potentially a little more flexible without using COM:

Code: Select all

SendMode, Input
#IfWinActive, ahk_class XLMAIN 					; 	Only activates if Excel window is in Active Focus

NumpadMult::
ControlSend, EXCEL71, {F2}, ahk_class XLMAIN 	; 	Move Edit Control Focus to Excel's formula bar (aka editing inside the cell without erasing contents)	
Send : 											;	Send a colon
return