Hi
Am relatively new to AHK but have been working on a script that I have working which goes one by one through a range of cells on an excel spreadsheet, copying each one and pasting them into a citrix app with sendraw %clipboard%.
If you can imagine this is a copy paste copy paste copy paste etc with some tabs and WinWaitActive's thrown in for good measure.
I have found due to the responsiveness of citrix that I have to put in a lot of sleep time.
I am wondering if i could somehow programme a script to copy each cell [19 in total] from a given row as 19 variables, paste them into the relevant location in the citrix app and then clear the variables to start again?
Anyone have any suggestions on this or know if it would be at all possible?
Thanks
pentesh

Storing multiple values with clipboard
Started by
pentesh
, Jan 27 2011 02:22 PM
22 replies to this topic
#1
-
Posted 27 January 2011 - 02:22 PM

If you're using AHK_L, my suggestion would be to do a two-hotkey setup. The first hotkey will extract the values of all of the cells from the spreadsheet and save them to an AHK array, then sets the value of a variable to be used in the second hotkey:
The second hotkey will strictly send each cell's contents from the array, using the cell variable to determine what cell to send. The value of cell will increment by one each time the hotkey is fired:
; using the cell range D1:D19 and F4 as the hotkey, as an example F4:: xl := ComObjActive("Excel.Application") ; connects to the active instance of Excel sArray := xl.Range["D1:D19"].value ; saves the value of the cells in the range to a SafeArray cells := Object() ; creates the AHK array to store the cell values Loop % sArray.MaxIndex(1) ; loops through the cells in the SafeArray cells.Insert(sArray[A_Index,1]) ; passes the value of each cell to the AHK array cell=1 ; sets the cell being acted upon to default return
The second hotkey will strictly send each cell's contents from the array, using the cell variable to determine what cell to send. The value of cell will increment by one each time the hotkey is fired:
F5:: if cell > 19 MsgBox, 48, Warning, The end of the cell list has been reached. ; warning that you have reached the end of the array. else { SendRaw % cells[cell] ; sends the particular value of the cell in the array, if cell=1 then the first cell (cell[1]), etc. cell++ ; increments the number in the variable by one so the next time the hotkey is fired it sends the next cell in the array } return
#2
-
Posted 27 January 2011 - 02:57 PM

You can store the clipboard as a var. e.g.
You should read the help file section on variables and the clipboard:
<!-- m -->http://www.autohotke...l.htm#Variables<!-- m -->
Good code Sinkfaze, but it is rather overwhelming for a n00b, try giving simpler examples to n00bs.
David
MyVar1 := ClipboardThen you can copy the next item and assign that one:
MyVar2 := ClipboardThe you can send the vars:
SendRaw % MyVar1 SendRaw % MyVar2 ;etc
You should read the help file section on variables and the clipboard:
<!-- m -->http://www.autohotke...l.htm#Variables<!-- m -->
Good code Sinkfaze, but it is rather overwhelming for a n00b, try giving simpler examples to n00bs.
David
#3
-
Posted 27 January 2011 - 02:57 PM

What ever happened, happened.
Thanks to both. I have gone with MacroMan!'s solution as it fits my abilities better!! :lol: :lol:
I am experiencing a problem whereby there is a return after the text of the cell with:
This never happened with:
Anyway around this? Many thanks for the help again.
I am experiencing a problem whereby there is a return after the text of the cell with:
SendRaw % MyVar1
This never happened with:
SendRaw %clipboard%
Anyway around this? Many thanks for the help again.
#4
-
Posted 27 January 2011 - 03:29 PM

I think this should fix it:
Coincidentally, I referred my code not to overwhelm you at your skill level but to avoid exactly this problem. This is probably one of the most common errors with copying values out of excel via the Clipboard; using a COM method, while a little more daunting at first, will not only help you avoid some of these intricacies but will also open up to you the possibilities of what you can do with AHK and Excel.
SendRaw % RegExReplace(MyVar1,"\v")
Coincidentally, I referred my code not to overwhelm you at your skill level but to avoid exactly this problem. This is probably one of the most common errors with copying values out of excel via the Clipboard; using a COM method, while a little more daunting at first, will not only help you avoid some of these intricacies but will also open up to you the possibilities of what you can do with AHK and Excel.
#5
-
Posted 27 January 2011 - 03:33 PM

Thanks for that.
Have a much cleaner and faster script than my original... Works perfectly.
I will have tackle the COM learning curve because it does seem the potential is huge.
Thanks
Have a much cleaner and faster script than my original... Works perfectly.
I will have tackle the COM learning curve because it does seem the potential is huge.
Thanks
#6
-
Posted 27 January 2011 - 05:37 PM

I am starting my second project using AHK and am doing something similar to this. I have to:
Get cell values from different columns and paste them into our company web app. (not sure exactly how many or which columns yet)
Hit some buttons on the web app for updating.
Go back and go to next ROW and get values again from columns. (some may be blank, so this has to be OK and nothing put into web app)
Keep this loop going until last ROW has nothing in it.
Here is what I kind of thrown together. I am, for now, just writing my values to NotePad to make sure I am creating the correct MyVar#
Question: :?: I need to understand how to create to loop and make it finish one ROW and then go to the next ROW and get values....and so on.
Thanks and I look forward to hearing from the awesome AHK'ers.....
Get cell values from different columns and paste them into our company web app. (not sure exactly how many or which columns yet)
Hit some buttons on the web app for updating.
Go back and go to next ROW and get values again from columns. (some may be blank, so this has to be OK and nothing put into web app)
Keep this loop going until last ROW has nothing in it.
Here is what I kind of thrown together. I am, for now, just writing my values to NotePad to make sure I am creating the correct MyVar#
#NoEnv ;;;RECOMMENDED FOR PERF. AND COMP. WITH FUTURE AutoHotkey RELEASES SendMode Input ;;;RECOMMENDED FOR NEW SCRIPTS DUE TO ITS SUPERIOR SPEED AND RELIABILITY SetBatchLines -1 ;;;NEVER SLEEP (i.e. have the script run at maximum speed). SetWorkingDir %A_ScriptDir% ;;;ENSURES A CONSISTENT STARTING DIRECTORY SetTitleMatchMode 2 ;;;COMPARES TITLES #SingleInstance Force ;;;ALLOWS ONLY ONE SCRIPT TO RUN AT A TIME #Persistent ;;;WILL STAY RUNNING UNTIL TERMINATED MANUALLY OR BY EARLYTERM IN CODE ;;;GENERIC NAME CALLED LATER IN SCRIPT TO USE THE 'ACTIVE' EXCEL SPREADSHEET Path := "ahk_class XLMAIN" ;;;GENERIC NAME CALLED LATER IN SCRIPT AcctData := "Notepad.exe" #E:: Xl := ComObjActive("Excel.Application") ;;Xl.Range("K:L").Copy ;;THIS WILL COPY COLUMNS K THROUGH K Xl.Range("K2").Copy ;;THIS WILL COPY JUST CELL K2 MyVar1 := Clipboard ;;Creates MyVar1 MsgBox % MyVar1 ;;Display to check it for now Xl := ComObjActive("Excel.Application") Xl.Range("L2").Copy ;;THIS WILL COPY JUST CELL L2 MyVar2 := Clipboard ;;Creates MyVar2 MsgBox % MyVar2 ;;Display to check it for now Run %AcctData% ;;;Run ACTIVATES THE FILE FOR DATA TO BE WRITTEN FOR REVIEW AT JOB END WinWait, Untitled - Notepad ;;;WAITS ON NOTEPAD WINDOW Winshow, Untitled - Notepad ;;;SHOWS NOTEPAD WINDOW Winactivate, Untitled - Notepad WinMove, A,, 325, 163, 395, 435 ;;;MAKES IT SMALL AND CENTERS Sleep 2000 ;;;SLOW JUST TO WATCH SendRaw % MyVar1 ;;;SENDS DATA FROM K2 Sleep 2000 ;;;SLOW JUST TO WATCH SendRaw % MyVar2 ;;;SENDS DATA FROM L2 MsgBox, 262148, What What, Something?? ;;;POP-UP WITH OPTIONS OF YES/NO IfMsgBox Yes ;;;POP-UP OPTION FOR USER TO CLICK YES OR HIT ENTER ExitApp else IfMsgBox No ;;;POP-UP OPTION FOR USER TO HIT NO ExitApp
Question: :?: I need to understand how to create to loop and make it finish one ROW and then go to the next ROW and get values....and so on.
Thanks and I look forward to hearing from the awesome AHK'ers.....
#7
-
Posted 13 February 2012 - 06:47 PM

#E:: Xl := ComObjActive("Excel.Application") [color=red]Loop 2 { [/color] ;;Xl.Range("K:L").Copy ;;THIS WILL COPY COLUMNS K THROUGH K Xl.Range("K" [color=red]A_Index + 1[/color]).Copy ;;THIS WILL COPY JUST CELL K2 MyVar1 := Clipboard ;;Creates MyVar1 MsgBox % MyVar1 ;;Display to check it for now Xl := ComObjActive("Excel.Application") Xl.Range("L2").Copy ;;THIS WILL COPY JUST CELL L2 MyVar2 := Clipboard ;;Creates MyVar2 MsgBox % MyVar2 ;;Display to check it for now Run %AcctData% ;;;Run ACTIVATES THE FILE FOR DATA TO BE WRITTEN FOR REVIEW AT JOB END WinWait, Untitled - Notepad ;;;WAITS ON NOTEPAD WINDOW Winshow, Untitled - Notepad ;;;SHOWS NOTEPAD WINDOW Winactivate, Untitled - Notepad WinMove, A,, 325, 163, 395, 435 ;;;MAKES IT SMALL AND CENTERS Sleep 2000 ;;;SLOW JUST TO WATCH SendRaw % MyVar1 ;;;SENDS DATA FROM K2 Sleep 2000 ;;;SLOW JUST TO WATCH SendRaw % MyVar2 ;;;SENDS DATA FROM L2 [color=red]}[/color] MsgBox, 262148, What What, Something?? ;;;POP-UP WITH OPTIONS OF YES/NO IfMsgBox Yes ;;;POP-UP OPTION FOR USER TO CLICK YES OR HIT ENTER ExitApp else IfMsgBox No ;;;POP-UP OPTION FOR USER TO HIT NO ExitAppUntested and from what I've seem com/excell not a good method as you can "tell" excel to stop copying when it finds an empty cell but I don't know how. Just wanted to show the use of a loop (which probably won't work as you want or at all)
#9
-
Posted 14 February 2012 - 03:05 PM

thanks....it does loop through the fields.
I add the A_Index + 1 to the other COLUMN I want to get data from.
1. I will be needing to get data from row ? and from numerous columns.
2. Then send the 'vars" created, using the classNN information, to different fields on a company application.
3. Click on the "update" button (classNN Button 46)
4. Then "loop" until I find a blank row....
This sure gets me started in the right direction I believe.....
Anyone with other ideas or help is always appreciated
With just using Basic AHK I can't get too fancy, but with AHK, there are plenty of options with coding.....
I add the A_Index + 1 to the other COLUMN I want to get data from.
1. I will be needing to get data from row ? and from numerous columns.
2. Then send the 'vars" created, using the classNN information, to different fields on a company application.
3. Click on the "update" button (classNN Button 46)
4. Then "loop" until I find a blank row....
Xl := ComObjActive("Excel.Application") Loop 2 { Xl.Range("K" A_Index + 1).Copy ;;THIS WILL COPY JUST CELL MyVar1 := Clipboard ;;Creates MyVar1 MsgBox % MyVar1 ;;Display to check it for now Xl := ComObjActive("Excel.Application") Xl.Range("M" A_Index + 1).Copy ;;THIS WILL COPY JUST CELL MyVar2 := Clipboard ;;Creates MyVar2 MsgBox % MyVar2 ;;Display to check it for now
This sure gets me started in the right direction I believe.....
Anyone with other ideas or help is always appreciated

With just using Basic AHK I can't get too fancy, but with AHK, there are plenty of options with coding.....
#10
-
Posted 14 February 2012 - 03:37 PM

The appropriate part could probably be revised in this way:
xl := ComObj("Excel.Application"), [color=red]myRng := xl.Range["K2:K" xl.Range["K2"].End(-4121).Row][/color] [color=red]For c in myRng[/color] { MsgBox % [color=red](MyVar1 := c.Value)[/color] "`n" [color=red](MyVar2 := c.Offset(0,1).Value)[/color] Run %AcctData% ;;;Run ACTIVATES THE FILE FOR DATA TO BE WRITTEN FOR REVIEW AT JOB END WinWait, Untitled - Notepad ;;;WAITS ON NOTEPAD WINDOW Winshow, ;;;SHOWS NOTEPAD WINDOW Winactivate, WinMove, , , 325, 163, 395, 435 ;;;MAKES IT SMALL AND CENTERS Sleep 2000 ;;;SLOW JUST TO WATCH SendRaw % MyVar1 ;;;SENDS DATA FROM K2 Sleep 2000 ;;;SLOW JUST TO WATCH SendRaw % MyVar2 ;;;SENDS DATA FROM L2 }
#11
-
Posted 14 February 2012 - 03:46 PM

thanks.....this is confusing to me for sure, but I have about 7000 rows of data and I have to get information from 18 columns in each of the 7000, then inject to application.
again, this looks confusing, but also looks like I would have to code for each variable created.....????
again, this looks confusing, but also looks like I would have to code for each variable created.....????
#12
-
Posted 14 February 2012 - 05:42 PM

I didn't understand Sinfaze's reply but I did manage to get this working;
can somebody tell me the best way (a simple way) to add in the following;
**I need to understand how to only "click" a box (Button3) if a cell contains a Y for MyVar3. Notice 'dark red' color in code above.
I will have some other copied fields too, but if I get help with this first one, then I hopefully can figure out the rest.
Thanks in advance AHK'ers
#NoEnv ;;;RECOMMENDED FOR PERFORMANCE AND COMPATIBILITY WITH FUTURE AutoHotkey RELEASES SendMode Input ;;;RECOMMENDED FOR NEW SCRIPTS DUE TO ITS SUPERIOR SPEED AND RELIABILITY SetBatchLines -1 ;;;SetBatchLines -1 TO NEVER SLEEP (i.e. have the script run at maximum speed). SetWorkingDir %A_ScriptDir% ;;;ENSURES A CONSISTENT STARTING DIRECTORY SetTitleMatchMode 2 ;;;COMPARES TITLES #SingleInstance Force ;;;ALLOWS ONLY ONE SCRIPT TO RUN AT A TIME #Persistent ;;;WILL STAY RUNNING UNTIL TERMINATED MANUALLY OR BY EARLYTERM IN CODE Hotkey, Pause, EarlyTerm ;;;QUICK KILL COMMAND. *HIT Pause KEY ON KEYBOARD AT ANY TIME TO KILL THIS SCRIPT ;**WHEN COMPLETELY DONE RUNNING, IT WILL CALL EARLYTERM TO EXIT SCRIPT FOR YOU ;;;CAPS - GENERIC NAME CALLED LATER IN SCRIPT FOR WEB APP, WILL USE THE 'ACTIVE' WEB APPLICATION GETPAID webapp := "Customer Profile-CrColl ahk_class IEFWINDOW" ;;;GENERIC NAME CALLED LATER IN SCRIPT TO USE THE 'ACTIVE' EXCEL SPREADSHEET Path := "ahk_class XLMAIN" ;;;GENERIC NAME CALLED LATER IN SCRIPT AcctBad := "Notepad.exe" ;;;GENERIC NAME CALLED LATER IN SCRIPT TO WRITE ERRORS TO THE NOTEPAD AddInfo := "ahk_class Notepad" FormatTime, Time, ,M/dd/yyyy h:mmtt ;;ADDED THIS FOR TIME STAMP TO BE POSTED TO NOTEPAD ERROR LOG AcctNum = ;;;VARIABLE USED FOR THE CUSTOMER NUMBER FROM SPREADSHEET Clipboard = ;;;AREA FOR COPIED DATA PLACED TO CLIPBOARD match_text=found ;;;WHEN CHECKING WEB APP FOR KEYWORD ON SCREEN, FOR INVALID CUSTOMER VERIFICATION TotalCust = 0 ;;;TOTAL NUMBER OF ACCTNUMS READ ON EXCEL SPREADSHEET ValidCust = TotalCust - Invalid ;;;VALID ACCTNUMS FOUND AND PROCESSED InValid = 0 ;;;INVALID ACCTNUMS IN ERROR AND NOT PROCESSED ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; #E:: Run %AcctBad% ;;;Run ACTIVATES THE FILE FOR ERRORS TO BE WRITTEN FOR REVIEW AT JOB END WinWait, Untitled - Notepad ;;;WAITS ON NOTEPAD WINDOW Winshow, Untitled - Notepad ;;;SHOWS NOTEPAD WINDOW Winactivate, Untitled - Notepad ;;;ACTIVATES NOTEPAD WINDO WinMove, A,, 325, 163, 395, 435 ;;;MOVES TO WINDOW SPY COORDINATES WHICH ARE CENTER SCREEN AND SMALL Sleep 500 ;;;PAUSE ;;;FOLLOWING SENDS THE MESSAGE IN BRACKETS TO THE NEWLY OPENED ERROR LOG NOTEPAD Send, ( ERROR LOG READY %Time% IF ANY ERRORS ARE FOUND AND RETURNED WHEN THIS JOB FINISHES PLEASE REVIEW THIS DOCUMENT TO HELP DETERMINE WHAT HAPPENED Account Number Customer Name ) Sleep 100 ;;;PAUSE Send {Enter} ;;;JUST TO GET TO NEWLINE Sleep 100 ;;;PAUSE { ;;;OPEN BRACKET { TO GROUP 'IF' COMMANDS MsgBox, 262148, Start Process?, OK to Start Update Message Process? ;;;POP-UP WITH OPTIONS OF YES/NO IfMsgBox Yes ;;;POP-UP OPTION FOR USER TO CLICK YES OR HIT ENTER Goto StartJob ;;;CONTINUE TO StartJob TO RUN THE SCRIPT else ;;;IF NO, IT WILL GO TO EarlyTerm IfMsgBox No ;;;POP-UP OPTION FOR USER TO HIT NO GoTo EarlyTerm ;;;NORMALLY USE 'break' IN A LOOP, BUT NOT LOOP, WILL KILL APPLICATION } ;;;CLOSE THE BRACKET } FOR 'IF' COMMANDS ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; StartJob: ;;;THIS STARTS THE MAIN PROCESSING PART OF SCRIPT WinActivate %Path% ;;;MAKES SURE FILE IS OPEN WinWaitActive %Path% ;;;MAKES SURE FILE IS OPEN Xl := ComObjActive("Excel.Application") ;;;;;;;;;CHANGE LOOP NUMBER TO HOW MANY ROWS OF DATA Loop 1 { Xl.Range("U" A_Index + 1).Copy ;;THIS WILL COPY JUST CELL MyVar1 := Clipboard ;;Creates MyVar1 MsgBox % MyVar1 ;;Display to check it for now Xl := ComObjActive("Excel.Application") Xl.Range("T" A_Index + 1).Copy ;;THIS WILL COPY JUST CELL MyVar2 := Clipboard ;;Creates MyVar2 MsgBox % MyVar2 ;;Display to check it for now Xl := ComObjActive("Excel.Application") Xl.Range("B" A_Index + 1).Copy ;;THIS WILL COPY JUST CELL ;;;testing risk override MyVar3 := Clipboard ;;Creates MyVar2 MsgBox % MyVar3 ;;Display to check it for now WinActivate %webapp% ;;;ACTIVATES WEB APPLICATION WinWaitActive %webapp% ;;;MAKES SURE WEB APPLICATION IS ACTIVE Sleep 4000 ;;;PAUSE ControlSendRaw, Edit6, %MyVar1% ;;;;;;;;;does seem to work but when loops for next acctnum it appends ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;should be ok though once hitting update is added. Sleep 2000 ControlClick, Button3 [color=darkred];;;***NEED TO ONLY CLICK IF MyVar3 is equal to Y, if not (cell blank) THEN LEAVE ALONE AND GO ON TO NEXT SEND[/color] Sleep 2000 ControlSendRaw, Edit51, %MyVar2% Sleep 200 } MsgBox, 262148, TEST, CHECK?? ;;;POP-UP WITH OPTIONS OF YES/NO IfMsgBox Yes ;;;POP-UP OPTION FOR USER TO CLICK YES OR HIT ENTER ExitApp else IfMsgBox No ;;;POP-UP OPTION FOR USER TO HIT NO ExitApp ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; EarlyTerm: ;;;STEP TO END THE HOTKEY FROM RUNNING WinActivate %AddInfo% ;;;MAKES SURE TXT FILE IS OPEN WinWaitActive %AddInfo% ;;;MAKES SURE TXT FILE IS OPEN WinMove, A,, 325, 69, 395, 435 ;;;MOVES TO WINDOW SPY COORDINATES WHICH ARE CENTER SCREEN AND SMALL MsgBox,, PROCESS FINISHED - EVALUATE INFORMATION, (LTrim EVALUATE TOTALS AND REVIEW ANY ERRORS `n Total Records Processed : %TotalCust% Valid Customer Records : %ValidCust% `n InValid Customer Records: %InValid% **IF NOT ZERO REVIEW ERRORS IN THE TXT FILE `n`n ******** HIT OK WHEN READY TO EXIT APP ******** ) ExitApp ;;;ENDS HOTKEY APPLICATION FROM RUNNING, ITS REMOVED FROM TOOL TRAY ;___________________________________________________________________________________________
can somebody tell me the best way (a simple way) to add in the following;
**I need to understand how to only "click" a box (Button3) if a cell contains a Y for MyVar3. Notice 'dark red' color in code above.
I will have some other copied fields too, but if I get help with this first one, then I hopefully can figure out the rest.
Thanks in advance AHK'ers
#13
-
Posted 15 February 2012 - 05:27 PM

This is how you would simplify down the loop:
[color=darkred]. . . [/color] xl := ComObjActive("Excel.Application") Loop, %rows% ; dummy variable just to express the number of rows { c := xl.Range["B" A_Index+1] ; MsgBox, 64, Data Check, % "Column B:`t" c.Value "`nColumn T:`t" c.Offset(0,18).Value "`nColumn U:`t" c.Offset(0,19).Value WinWait, %webapp% WinActivate, Sleep 4000 ;;;PAUSE ControlSendRaw, Edit6, % c.Offset(0,19).Value ; Offset says same row, 19 columns to the right of Column 'B' (Column 'U') Sleep 2000 if (c.Value="Y") { ControlClick, Button3 Sleep 2000 } ControlSendRaw, Edit51, % c.Offset(0,18).Value ; Offset says same row, 18 columns to the right of Column 'B' (Column 'T') Sleep 200 } [color=darkred]. . . [/color]
#14
-
Posted 15 February 2012 - 05:45 PM

Hey Sinkfaze....awesome.....only issue I had when running just one record through, it pasted my acctnum in but added .000 after the number
999999999.000
It then checked the box OK and went to comment area.
why the extra?
999999999.000
It then checked the box OK and went to comment area.
why the extra?
#15
-
Posted 15 February 2012 - 07:13 PM
