 |
AutoHotkey Community
Let's help each other out
|
| View previous topic :: View next topic |
| Author |
Message |
hutch@edge.net
Joined: 16 Sep 2008
Posts: 77
|
 Posted: Sat May 09, 2009 5:23 am Post subject: [SOLVED] AHK Using Various Excel Windows... |
 |
|
Greetings, Gurus.
I'm certain this has been done, but hours of searching didn't turn up the advice I needed.
I have a simple script that utilizes several hotkeys to copy values from an Excel file to another application. The hotkeys have to jump back and forth several times. I can use the name of the other application that we are copying to, because it's always the same, but can't use the name of the Excel file because it is always different.
In a nutshell.., how do you code AHK to use a particular Excel file, (perhaps selected via dropdown, but not necessarily), and continue to use it for all hotkeys in the script?
Here's what I've tried, and it sort of works, but the problem is I'm trying to train several users to use the script, (several times a day), and this only works if they make certain Excel is the active window when they press the hotkey:
| Code: |
^Numpad1:: ;;Copy active cell from Excel and paste into ADS.
xlid:=WinExist("A")
GroupAdd, XLCOPY, ahk_id %xlid%
WinWait, ahk_group XLCOPY,
IfWinNotActive, ahk_group XLCOPY, , WinActivate, ahk_group XLCOPY,
WinWaitActive, ahk_group XLCOPY,
Send, ^c
WinWait, ADS-KEYSTONE.sbc - SBClient,
IfWinNotActive, ADS-KEYSTONE.sbc - SBClient, , WinActivate, ADS-KEYSTONE.sbc - SBClient,
WinWaitActive, ADS-KEYSTONE.sbc - SBClient,
Send, {F9}
Sleep, 200
Send, {ALTDOWN}{SHIFTDOWN}{INS}{SHIFTUP}{ALTUP}
Exit
^Numpad2:: ;;Go back to Excel, down to next part, then repeat step 1.
WinWait, ahk_group XLCOPY,
IfWinNotActive, ahk_group XLCOPY, , WinActivate, ahk_group XLCOPY,
WinWaitActive, ahk_group XLCOPY,
Send, {DOWN}
Exit
|
There are other hotkeys involved, that's just a small sample. Another problem is that none of the other keys work unless hotkey ^Numpad1 was pressed first, with the correct Excel window active, because thats the script that activates the excel file. After the user has used the hotkeys several times to copy and paste values into ADS, they will close the excle workbook and open another one with a different name, (same format), and do it all over again. Ideally, AHK would recognize when one file has closed and a new one has opened and prompt the user for the name of the new file, so they can begin working from the new file.
So, I'm sure some experienced users will know, what's the best way to work with multiple excel files having various names?
Thanks in advance for any help you can offer.
Last edited by hutch@edge.net on Tue May 12, 2009 5:30 pm; edited 1 time in total |
|
| Back to top |
|
 |
sinkfaze
Joined: 18 Mar 2008
Posts: 5044
Location: the tunnel(?=light)
|
| Posted: Sat May 09, 2009 5:57 am Post subject: |
|
|
Perhaps we can just mod it slightly and exclude the group:
| Code: | ^Numpad1::
xlid:=WinExist("A")
WinWait, ahk_id %xlid%,
IfWinNotActive, ahk_id %xlid%, , WinActivate, ahk_id %xlid%,
WinWaitActive, ahk_id %xlid%,
Send, ^c
WinWait, ADS-KEYSTONE.sbc - SBClient,
IfWinNotActive, ADS-KEYSTONE.sbc - SBClient, , WinActivate, ADS-KEYSTONE.sbc - SBClient,
WinWaitActive, ADS-KEYSTONE.sbc - SBClient,
Send, {F9}
Sleep, 200
Send, {ALTDOWN}{SHIFTDOWN}{INS}{SHIFTUP}{ALTUP}
Exit
^Numpad2::
WinWait, ahk_id %xlid%,
IfWinNotActive, ahk_id %xlid%, , WinActivate, ahk_id %xlid%,
WinWaitActive, ahk_id %xlid%,
Send, {DOWN}
Exit |
As long as ^Numpad1 is the first hotkey run it will set xlid to the unique ID of the active instance of Excel, so if they switch to another spreadsheet and run the ^Numpad1 hotkey it will then recognize that as now being the active instance of Excel for its purposes.
Just curious though, are you using #IfWinActive to limit where the hotkey can be run? That will definitely cut down on the chances of unintended error. You could also potentially cut not only the first set of window identifiers since an Excel window is already active, but you could also cut the unique ID requirement as well:
| Code: | #IfWinActive ahk_class XLMAIN ; XLMAIN is the general class for instances of Excel
^Numpad1::
Send, ^c
WinWait, ADS-KEYSTONE.sbc - SBClient,
IfWinNotActive, ADS-KEYSTONE.sbc - SBClient, , WinActivate, ADS-KEYSTONE.sbc - SBClient,
WinWaitActive, ADS-KEYSTONE.sbc - SBClient,
Send, {F9}
Sleep, 200
Send, {ALTDOWN}{SHIFTDOWN}{INS}{SHIFTUP}{ALTUP}
return
^Numpad2::
Send, {DOWN}
return |
Although I can't tell if ^Numpad2 is meant to be executed from Excel or to return you from the other window back to Excel.
You'll also notice that I changed your Exit commands to return, using Exit in that way is a little unusual although it's as harmless as return; using return would ultimately be a better practice.
_________________
Try Quick Search for Autohotkey or see the tutorial for newbies. |
|
| Back to top |
|
|
hutch@edge.net
Joined: 16 Sep 2008
Posts: 77
|
| Posted: Sat May 09, 2009 6:41 am Post subject: |
|
|
Thanks for the reply, sinkfaze.
I will definitely incorporate the #IfWinActive command, that will help if the user forgets to make sure Excel is active before starting the script.
Hotkey 2 was designed to move from ADS, back to excel, and down to the next cell. Thats the reason for setting the active window as xlid, that gives me a way to refer back to that sheet from within another program. I don't know if this is the best way to do it or not. |
|
| Back to top |
|
|
sinkfaze
Joined: 18 Mar 2008
Posts: 5044
Location: the tunnel(?=light)
|
| Posted: Sat May 09, 2009 7:09 am Post subject: |
|
|
| hutch@edge.net wrote: | | Hotkey 2 was designed to move from ADS back to excel and down to the next cell. |
That can still work without much modification, you might even be able to condense two hotkeys into one if you like:
| Code: | #IfWinActive ahk_class XLMAIN
^Numpad1::
xlid:=WinExist("A")
Send, ^c
WinWait, ADS-KEYSTONE.sbc - SBClient,
IfWinNotActive, ADS-KEYSTONE.sbc - SBClient, , WinActivate, ADS-KEYSTONE.sbc - SBClient,
WinWaitActive, ADS-KEYSTONE.sbc - SBClient,
Send, {F9}
Sleep, 200
Send, {ALTDOWN}{SHIFTDOWN}{INS}{SHIFTUP}{ALTUP}
WinWait, ahk_id %xlid%,
IfWinNotActive, ahk_id %xlid%, , WinActivate, ahk_id %xlid%,
WinWaitActive, ahk_id %xlid%,
Send, {DOWN}
return |
And as I found out in a discussion with Lexicos, using all four of the window commands(WinWait/IfWinNotActive/WinActivate/WinWaitActive) is typically not necessary and you may actually lose time unnecessarily when using all four window commands, not to mention all of the extra code. As Lexicos pointed out to me, you typically won't need more than the WinWait and WinActivate commands so you could trim that in your code:
| Code: | #IfWinActive ahk_class XLMAIN
^Numpad1::
xlid:=WinExist("A")
Send, ^c
WinWait, ADS-KEYSTONE.sbc - SBClient
WinActivate, ADS-KEYSTONE.sbc - SBClient
Send, {F9}
Sleep, 200
Send, {ALTDOWN}{SHIFTDOWN}{INS}{SHIFTUP}{ALTUP}
WinWait, ahk_id %xlid%
WinActivate, ahk_id %xlid%
Send, {DOWN}
return |
_________________
Try Quick Search for Autohotkey or see the tutorial for newbies. |
|
| Back to top |
|
|
Guest
|
| Posted: Sat May 09, 2009 2:27 pm Post subject: |
|
|
Hello again, sinkfaze.
I won't be able to combine the two keys, because there are vraibales that may come into play once the first key is pressed which will require further action from the user. Example: ^1 copied the part number from Excel, and paste it into a search feature in ADS, which causes ADS to find the part. Now the part will need eiher a price or quantity change, and a box MAY pop up asking the user if they want to transfer excess inventory from another location, or telling the user the price is out of approved range, or that they must order in package quantity. The user would make necessary changes, then press ^2 to go back to excel and down to the next part.
I have addressed most of the variables using different hotkeys, which only need to work in ADS, but ^2 always needs to go back to the same excel sheet to get the next part number.
Regarding minimizing the code by removing IfWinNotActive, ADS-KEYSTONE.sbc - SBClient, , WinActivate, ADS-KEYSTONE.sbc - SBClient, the fear there is that if AHK gets ahead of ADS, or works too fast, it will cause issues with pasting the data in the wrong location. I figured the extra lines were minimal insurance that the ADS window as active before pasting the data. |
|
| Back to top |
|
|
hutch@edge.net
Joined: 16 Sep 2008
Posts: 77
|
| Posted: Sat May 09, 2009 2:29 pm Post subject: |
|
|
| Sorry. I didn't realize I wasn't logged in for my last reply. |
|
| Back to top |
|
|
sinkfaze
Joined: 18 Mar 2008
Posts: 5044
Location: the tunnel(?=light)
|
| Posted: Sat May 09, 2009 9:10 pm Post subject: |
|
|
| hutch@edge.net wrote: | | Regarding minimizing the code by removing IfWinNotActive, ADS-KEYSTONE.sbc - SBClient, , WinActivate, ADS-KEYSTONE.sbc - SBClient, the fear there is that if AHK gets ahead of ADS, or works too fast, it will cause issues with pasting the data in the wrong location. I figured the extra lines were minimal insurance that the ADS window as active before pasting the data. |
I can understand the hesitation but you can always comment out the old method and test with the revised method:
| Code: | #IfWinActive ahk_class XLMAIN
^Numpad1::
xlid:=WinExist("A")
Send, ^c
;WinWait, ADS-KEYSTONE.sbc - SBClient,
;IfWinNotActive, ADS-KEYSTONE.sbc - SBClient, , WinActivate, ADS-KEYSTONE.sbc - SBClient,
;WinWaitActive, ADS-KEYSTONE.sbc - SBClient,
WinWait, ADS-KEYSTONE.sbc - SBClient
WinActivate, ADS-KEYSTONE.sbc - SBClient
Send, {F9}
Sleep, 200
Send, {ALTDOWN}{SHIFTDOWN}{INS}{SHIFTUP}{ALTUP}
return |
If it doesn't work you can always remove the new code and un-comment the old code.
As I had stated in the other thread I linked to, trimming my code to that is saving anywhere from 100ms to 200ms each time it occurs, which doesn't seem like much but if you encounter that same sequence of code 300 times you'll have saved 30 seconds to a minute's worth of work overall. After a while you can tap into these finer details to find some more time to save. 
_________________
Try Quick Search for Autohotkey or see the tutorial for newbies. |
|
| Back to top |
|
|
hutch@edge.net
Joined: 16 Sep 2008
Posts: 77
|
| Posted: Tue May 12, 2009 5:30 pm Post subject: |
|
|
Sorry, skinfaze. I got busy and wasn't able to get back to you. You're right, there's no harm in testing it with those extra lines removed. I'll give it a try.
Thanks for all you help.
Have a good one. |
|
| Back to top |
|
|
|
|
You can post new topics in this forum
You can reply to topics in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
FAQ
Search
Memberlist
Register
Profile
Log in to check your private messages
Log in
