I think I'm not getting into edit mode in Excel Topic is solved

[PieLam]

Hi All,

I just started using AHK (v. ???) (just DL'ed & installed it yesterday, Sep 29, 2016) My knowledge of coding/scripting is very basic.

I have a couple of basic questions that I hope you can give me a clue :

1. How would I find out which version of AHK I'm using?
2. How would I script 'Edit Mode' in MS-Excel v. 2010? Just sending F2 doesn't seem to work...

Here's the FULL story of my problem:

I am in the process of creating an index of all my recipe collections (mostly all .TXT files) which is quite extensive and a few years old. In the past, I collected recipes that I've gotten mostly from the web. There are no particular recipes, just any that sounded good to me & also a few that came from my wife...

In this process, I started using MS-Excel 2010 since I thought it would be up to the task and I'm familiar enough with it (though, I'm by no means an expert.) My first hurdle in creating this index was the fact that of the 7 binders, none have numbered pages. OK, cleared that hurdle to my satisfaction. Next, entering all those recipe titles... (sigh) I'm afraid all I know is entering them one-by-one, manually.

I started the long & tedious process of entering the data of my collection in Excel. Yes, Excel has a built-in macro function which I tried, but it wouldn't do what I needed, phooey(sp.) Later, I thought AHK should do what I'm needing. After all, I'd used AHK years ago and was quite pleased with it. The only negative thing about it (to me) was the help (actually,lack of) for novices like me, help did exist, but it left a LOT to be desired. This current version, although not perfect, is vastly improved, IMO!

Anyway, I learned how to create a script in this current version of AHK fairly easily. I'll include it below so that someone can tell me how to get it to do what I'm needing:

I'm needing to copy the original recipe title and paste it into the adjacent cell where I can modify it (by cutting the title's last word, backspacing, Home to the 1st character position of the cell, paste the last word back in at this new positition, then add a comma & a space at the end of the pasted word) (I'm sort of creating an easier-to-find filename here.)

Example:
Spicy Fish & Okra Stew Stew, Spicy Fish & Okra
^^^ original title ^^^^^ ^^^^^^ After ^^^^^^^^
^^^ original cell ^^^^^^ ^^^^ Adjacent cell ^^^^^

The script then just moves Excel's cell pointer to the next recipe title's cell. (The easy part)

Trouble is, I can't seem to get this script to work. I THINK it's because I'm not getting into edit cell mode in Excel (usually invoked by pressing F2.) This is why I'm seeking your help. As far as I know, this script SHOULD work, but it doesn't. Can you tell me what's wrong, please?

Incidentally file=Untitled.html][/code], this script follows the same keystrokes (as far as I know) as I would use manually in Excel... I've poured over this script countless times and though I've modified it several times, nothing I've done has worked.

PS
You'll notice a lot of Msgbox commands in this script. They're there only for debugging purposes. I plan on removing them all when I get the script working. Is there a better/easier command for single stepping of the script's execution?

I forgot to mention: The main reason that I thought that sending F2 isn't working is because I don't see any of the cell's text being highlighted, I SHOULD see this, shouldn't I?

Code: Select all

#NoEnv  ; Recommended for performance and compatibility with future ; AutoHotkey releases.
#Warn  ; Enable warnings to assist with detecting common errors.
SendMode Input  ; Recommended for new scripts due to its superior speed ; and reliability.
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.


#IfWinActive Microsoft Excel non-commercial use - Rec Coll Binders tst.xlsx
; Only active in MS Excel "Rec Coll binders tst.xlsx"
;
; Begin hot key Win + z
;
#z:: 
sendplay ,{f2} ; edit cell
msgbox sendinput 'f2' (edit cell)
;
SetKeyDelay 500
sendplay {end} ; ensure we're at the end of the cell's contents
msgbox sendplay {end} (ensure we're at the end of the cell's contents)
;
SetKeyDelay 500
sendplay {shift} & {home} ; Highlight the cell's entire contents
msgbox sendplay {shift} & {home} (Highlight the cell's entire contents)
;
SetKeyDelay 500
sendplay {control} & c ; copy the highlighted text
msgbox sendplay {control} & c (copy the highlighted text)
;
SetKeyDelay 500
sendplay {enter}
msgbox sendplay {enter} (exit edit mode)
;
SetKeyDelay 500
sendplay {right} ; goto the next cell to the right
SetKeyDelay 500
sendplay {up} ; goto the next cell to the right
msgbox sendplay {right}+DELAY+sendplay {up} (goto next cell on the right)
;
; Adjacent cell copy is pasted
;
SetKeyDelay 500
sendplay {control} & v ; paste the clipboard's contents
msgbox sendplay {control} & v (paste the clipboard's contents)
;
SetKeyDelay 500
sendplay ,{f2} ; edit cell
msgbox sendplay <f2> (edit the new cell's contents)
;
SetKeyDelay 500
sendplay {control} & {left} ;move the cursor one word to the left
sendplay {shift} & {end} ; highlight the last word only
msgbox  (highlight the last word only)
;
SetKeyDelay 500
sendplay {control} & x ; delete that word + place it into the clipboard
msgbox sendplay {control} & x (delete last word & place in clipboard)
;
SetKeyDelay 500
sendplay {bs}{home} ; remove the trailing space & position the cursor home
msgbox {bs}{home} (remove the trailing space & position the cursor home)
;
SetKeyDelay 500
sendplay {control} & v,{space} ; Paste clipboard & add comma & space
msgbox {control} & v,{space} (Paste clipboard & add comma & space)
;
SetKeyDelay 500
sendplay {enter}{left} ; position the cell pointer for the next recipe title
msgbox sendplay {enter}{left} (position the cell pointer for the next title)
;
return

[TLM]

1st off to get the current version of AHk you're using there's a built in variable for that: msgbox % A_AhkVersion

2nd, to enter "Edit Mode" or better yet to enter some value, using COM,
you can either enter a value or a formula:

Code: Select all

Value = SomeValue
Forumla = SomeForumla

xlObj := ComExcelConnect_( "ahk_class XLMAIN" ) ; creates an Excel object

xlObj.ActiveCell.value := Value
; xlObj.ActiveCell.FormulaR1C1 := Forumla   ; remove comment to enter a formula into the active cell

ExitApp

ComExcelConnect( WinTitle ) ; connects to an open Excel sheet
{
    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 )
}

Just a thought

[Relayer]

TLM,

That is more complex that what I've been using. I use the following to create an object for an open Excel workbook:

Code: Select all

 xlobj := ComObjActive("Excel.Application")

I never try to operate on Excel by sending GUI type commands. It is much better to use the com object API built into Excel which is very similar to VBA. I use google by typing in "vba excel [subject of what I'm trying to do]". You WILL get a useful response from google.

Relayer

[FanaticGuru]

This is how I would do it using COM and Regex.

Code: Select all

#z::
	Xl := ComObjActive("Excel.Application")
	sRange := Xl.Range("A:A100")	; change to desired range
	;~ sRange := Xl.Selection		; or reverse commenting and use selection
	sRow := sRange.Row
	sCol := sRange.Column
	sCount := sRange.Rows.Count
	x:=0
	Loop 
	{	
		Text := Xl.Cells(sRow+x, sCol).value
		Text := RegExReplace(Text, "U)(.*) (\w+)\s*$", "$2, $1")
		Xl.Cells(sRow+x, sCol+1).value := Text
		x++
	} until x = sCount
return

This will look for an open Excel worksheet, then copy everything in the Range A1:A100 to B1:B100 with the swapping of the last word to first with a comma.

The code could be condensed some but I opted for easy of understanding.

FG

[PieLam]

Thanks for the replies, guys, but although they're somewhat helpful, for the most part, I'm lost!

TLM: I appreciate the tidbit for % A_AhkVersion I'm sure it'll come in handy! I'm sure that using COM over GUI commands are probably better, but I've never used them & am very unfamiliar with them at all, though I know they exist. As I mentioned earlier, my knowledge of coding/scripting is very basic. As a matter of fact, I learned the BASIC programming language long ago (early 1980s) and only dabbled in a few others.

A more detailed explanation of how-to use & exactly what is COM would be very helpful.

Relayer: Although I've used VBA in the past with some success, I've really only dabbled with it. Admittedly, I should've finished what I originally set out to do; finish studying the literature that came with it! But unfortunately, as with many other things, I only started the learning process & let the other things of life get in the way. Oh well.

FanaticGuru : Although your code is helpful, I'm only needing to copy one cell, the current cell, at a time. Sorry if that was unclear.

From a brief look at your code, I'm wondering if a little modification of the same code would work for an individual cell as with a range?

[FanaticGuru]

FanaticGuru : Although your code is helpful, I'm only needing to copy one cell, the current cell, at a time. Sorry if that was unclear.

From a brief look at your code, I'm wondering if a little modification of the same code would work for an individual cell as with a range?

Sure, it is pretty much already in my code. Just uncomment the Selection line like below.

Code: Select all

#z::
	Xl := ComObjActive("Excel.Application")
	sRange := Xl.Selection
	sRow := sRange.Row
	sCol := sRange.Column
	sCount := sRange.Rows.Count
	x:=0
	Loop 
	{	
		Text := Xl.Cells(sRow+x, sCol).value
		Text := RegExReplace(Text, "U)(.*) (\w+)\s*$", "$2, $1")
		Xl.Cells(sRow+x, sCol+1).value := Text
		x++
	} until x = sCount
return

Now go in Excel and be in the cell you want to copy and hit Win-Z. The cell will be copied to the right with the comma inverse of last word. You can also select multiple cells at a time if you like with this method.

FG

[FanaticGuru]

For my own amusement, I made the code smaller and smarter.

Code: Select all

#z::
	for c in ComObjActive("Excel.Application").Selection
		c.Offset(0,1).value := RegExReplace(c.value, "U)(.*) (\w+)\s*$", "$2, $1")
return

Does the same thing as the code above.

FG

[PieLam]

From reading other topics by you (I followed a link in your signature) I'm now pretty sure of your work !

Even though I hardly understand any of it, I'm willing to give it a try THANX!

BTW, how does one get updates? Like for Updated Dictionary on First Page Version: 1.21?

[FanaticGuru]

BTW, how does one get updates? Like for Updated Dictionary on First Page Version: 1.21?

The script on the first page of my threads is always the most up to date version. I just add a post to the thread to let people know I have edited the first post in the thread and updated the script.

That way people that have previously downloaded my script knows that something has changed in the code on the first post and they should download it again.

FG

[FanaticGuru]

Even though I hardly understand any of it, I'm willing to give it a try THANX!

A lot can be done with the core method of your script that basically simulates key strokes.

That is how a lot of automation scripts work with AutoHotkey.

It just happen that you ask how to do stuff with Excel. Microsoft programs have a system called COM that allows other programs like AutoHotkey to communicate with them more directly without using simulated key strokes. With programs like Excel, Word, Outlook etc., AutoHotkey can do a lot of powerful automation with relatively small amounts of code. It is also very fast and reliable compared to simulated key strokes.

Good luck, keep learning.

FG

[PieLam]

Thanx FG for that explanation! It helps a lot! (BTW, I think Fanatic ought to be replaced with Fantastic!)

I JUST now tried the COM/object script you wrote and it works fantastically! I just have a few questions:

How can I add or make the cell pointer move down to the next empty cell after the script is done?
Is it possible to add a GUI command to the script?
Is it possible to do the same operation with other words in the recipe title?
EX: Potato Soup With Shrimp would become Soup, Potato With Shrimp

[FanaticGuru]

How can I add or make the cell pointer move down to the next empty cell after the script is done?

Moving down to the next cell is simple.
c.Offset(1,0).Activate will move down to the next cell.
Moving down to the next empty cell can be done by doing what occurs when you do Ctrl-Arrow Down then going one more cell.
c.End(xlDown:=-4121).Offset(1,0).Activate will move down to the next empty cell.

Code: Select all

#z::
	for c in ComObjActive("Excel.Application").Selection
		c.Offset(0,1).value := RegExReplace(c.value, "U)(.*) (\w+)\s*$", "$2, $1")
	c.End(xlDown:=-4121).Offset(1,0).Activate	
return

This can have problems if you are already at the end of a block of cells.
This is more reliable:

Code: Select all

#z::
	for c in ComObjActive("Excel.Application").Selection
		c.Offset(0,1).value := RegExReplace(c.value, "U)(.*) (\w+)\s*$", "$2, $1")
	if c.Offset(1,0).formula
		c.End(xlDown:=-4121).Offset(1,0).Activate	; Move down to the end of continuous occupied cells then go one more
	else
		c.Offset(1,0).Activate	; Just move down one if already at the end of continuous occupied cells
return

Is it possible to add a GUI command to the script?

I am not sure what you mean or want exactly but sure, a GUI can be added. Creating a GUI is a whole other subject with many examples out there.

Is it possible to do the same operation with other words in the recipe title?
EX: Potato Soup With Shrimp would become Soup, Potato With Shrimp

This opens up another whole subject also. The reversing of the word is done using RegEx in the ReExReplace command. RegEx can do many complex string manipulations. You can read tons of subjects about it on the internet. It is not specific to AutoHotkey.

So basically you can create all kinds of RegEx statements to analyze a string and manipulate it.

Here is an example of looking for the word "with" and if it is found switch the word right before "with" to the first word with a comma. If no "with" is found then switch the last word to first with comma.

Code: Select all

#z::
	for c in ComObjActive("Excel.Application").Selection
		if InStr(c.value,"with")
			c.Offset(0,1).value := RegExReplace(c.value, "i)(.*) (\w*) with (.*)","$2, $1 with $3")
		else
			c.Offset(0,1).value := RegExReplace(c.value, "U)(.*) (\w+) *$", "$2, $1")
	if c.Offset(1,0).formula
		c.End(xlDown:=-4121).Offset(1,0).Activate	; Move down to the end of continuous occupied cells then go one more
	else
		c.Offset(1,0).Activate	; Just move down one if already at the end of continuous occupied cells
return

FG

[PieLam]

I am not sure what you mean or want exactly but sure, a GUI can be added. Creating a GUI is a whole other subject with many examples out there.

I didn't know if adding GUI commands like send would work or not. In theory, it should, IMO.

In using the code you provided me (works great, BTW,) I noticed that if I invoke the script (#z) before I press {enter} (but after I've typed the new title, I get an error and the script halts. I've done this accidentally a couple of times. So I thought if the script would start by sending {enter} & then {up} ; then execute the object code ; and after that {down} + {left} + {left} it would be perfect.

I do this manually now, it's no big deal though. I just thought if I could add this, then I'd have perfection... The script as it is already has sped up the tedious data entry process at least 3 fold! (by my estimates)

I don't want to seem ungrateful as the code you provided has been a great asset!

[FanaticGuru]

I am not sure what you mean or want exactly but sure, a GUI can be added. Creating a GUI is a whole other subject with many examples out there.

I didn't know if adding GUI commands like send would work or not. In theory, it should, IMO.

Hmm, send is not really a GUI command. GUI is a Graphical User Interface. A pretty popup menu is an example of a GUI.
https://autohotkey.com/docs/commands/Gui.htm

In using the code you provided me (works great, BTW,) I noticed that if I invoke the script (#z) before I press {enter} (but after I've typed the new title, I get an error and the script halts. I've done this accidentally a couple of times. So I thought if the script would start by sending {enter} & then {up} ; then execute the object code ; and after that {down} + {left} + {left} it would be perfect.

I do this manually now, it's no big deal though. I just thought if I could add this, then I'd have perfection... The script as it is already has sped up the tedious data entry process at least 3 fold! (by my estimates)

I don't want to seem ungrateful as the code you provided has been a great asset!

If you want to make sure Excel is not in enter or edit mode you can send a Control-Enter at the start. Control-Enter stays in the same cell.

Code: Select all

#z::
	Send ^{Enter}
	for c in ComObjActive("Excel.Application").Selection
		if InStr(c.value,"with")
			c.Offset(0,1).value := RegExReplace(c.value, "i)(.*) (\w*) with (.*)","$2, $1 with $3")
		else
			c.Offset(0,1).value := RegExReplace(c.value, "U)(.*) (\w+) *$", "$2, $1")
	if c.Offset(1,0).formula
		c.End(xlDown:=-4121).Offset(1,0).Activate	; Move down to the end of continuous occupied cells then go one more
	else
		c.Offset(1,0).Activate	; Just move down one if already at the end of continuous occupied cells
return

If you are wanting to move to a different cell at the end of the script you can Send keys or just adjust the Offset values in the last If statement. Offset(1,-2) moves down 1 and to the left 2.

Code: Select all

if c.Offset(1,0).formula
		c.End(xlDown:=-4121).Offset(1,-2).Activate	; Move down to the end of continuous occupied cells then go down 1 and left 2
	else
		c.Offset(1,-2).Activate	; Just down 1 and left 2 if already at the end of continuous occupied cells

FG

[PieLam]

Hmm, send is not really a GUI command. GUI is a Graphical User Interface. A pretty popup menu is an example of a GUI.

Sorry 'bout that, I thought it was since it wasn't an obj oriented cmd.

I knew what a GUI is, just not as it pertained to AHK.

https://autohotkey.com/docs/commands/Gui.htm

Wow, I had no idea. Thanx!

If you are wanting to move to a different cell at the end of the script you can Send keys or just adjust the Offset values in the last If statement.

Just so you know, I ended up just sending the keys I needed since I know how to do that and it worked...