22 replies to this topic

[pentesh]

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

[sinkfaze]

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:

; 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

[MacroMan!]

You can store the clipboard as a var. e.g.

MyVar1 := Clipboard

Then you can copy the next item and assign that one:

MyVar2 := Clipboard

The 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

[pentesh]

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:

SendRaw % MyVar1

This never happened with:

SendRaw %clipboard%

Anyway around this? Many thanks for the help again.

[sinkfaze]

I think this should fix it:

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.

[pentesh]

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

[BigMan]

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#

#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.....

[BigMan]

bump...

[BigMan]

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....

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.....

[sinkfaze]

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
}

[BigMan]

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.....????

[BigMan]

I didn't understand Sinfaze's reply but I did manage to get this working;

#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

[sinkfaze]

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]

[BigMan]

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?