17 replies to this topic

[Bristin]

Hi and thanks for your help in advance.

 

I currently get data in an excel file, it is in 3 columns and when broken out looks like this. there are usually 30-50 rows at a time

     A              B          C

Toyota      Prius     2004

Honda       Accord  2007

Ford          fiesta    1995

etc...

 

I need to enter this data into a program where there are 3 separate fields

field A     Field B    Field C   (then press {enter} 5 times

 

So I need an ahk script that will enter my data in fields A,B,C then press {enter} 5 times and go to the next row.

 

So here is my thought process when I started to try and write this.   

open the excel data file

launch the data entry program

copy the data from cell A1 into the clipboard

have the mouse drive to position #1 548,126 and left click to enter the data box--paste the data-press {enter} 5 times

Go back to the spreadsheet and copy the data from B1 into the clipboard

have the mouse drive to position #2 548, 423 and left click to enter the data box- paste the data press {enter} 5 times

Go back to the spreadsheet and copy the data from C1 into the clipboard

have the mouse drive to position #3 548, 627 and left click to enter the data box- paste the data press {enter} 5 times

then iterate and go to row 2 so copy data from A2, B2,C2,A3,B3,C3,etc...

 

While I think I understand what is needed here I have not had any success in generating a script for it. I think its probably simple for someone with real experience in this is beyond me at this time. Although I am very interested in improving

 

 

 

 

[Jackie Sztuk _Blackholyman]

That is all doable

When working with excel COM is the way to get the values from excel

And if you can please trie with the WindowSpy that come's with the ahk 1.1 installer

When running the spy hold your mouse over the input fields you need to fill with the values from A1, B1, C1

Look at the window title, class and the controls classNN

The we Can help you make ar realy relayable script

Hope to help

[Bristin]

Wow thanks for the prompt response!

the window Title and class is

B32PTS102.entry.com-Remote Desktop Connection

AHK_class TscShellContainerClass

 

The controls class is

ClassNN: OpWindowClass2

[Jackie Sztuk _Blackholyman]

okay a Remote Desktop may not be easy working with but lets see how it go's

 

Im low on time, but here's a start example:

 

#NoEnv
#SingleInstance Force
FileSelectFile, FilePath
 
oExcel := ComObjCreate("Excel.Application")     ; create Excel Application object
oExcel.Visible := True ;by default excel sheets are invisible
oWorkbook := oExcel.Workbooks.Open(FilePath)
 
var_A1 := oWorkbook.ActiveSheet.range("A1").value
var_B1 := oWorkbook.ActiveSheet.range("B1").value
var_C1 := oWorkbook.ActiveSheet.range("C1").value
 
Msgbox, Now the hotkeys`n`nCtrl+1, 2 or 3 will send the value of A1, B1 or C1
return
 
^1:: ; Ctrl+1
send %var_A1%
return
 
^2:: ; Ctrl+1
send %var_B1%
return
 
^3:: ; Ctrl+1
send %var_C1%
return

 

that will ask for the excel file then store the values of A1, B1 and C1

 

you can then test with Ctrl+1 , 2 or 3 and see if the script paste the value in to the input fields

 

Hope it works

 

Note: if you can give more info please do (links, examples or images help to)

[Cobalt]

Best Answer

Here you go.

 

You are lucky I am wrapping things up with my own little excel project and I happen to know everything needed to do just what you are looking for. I left comments in that explains practically every part. Change what you want, but I've added everything you wanted. I've also added a way to choose which row to start on, and it defaults to 2 since the first row is usually a column title. It will loop until it finds a row where the A column cell block is a blank value. I have made most parts of the code as understandable as I can through comments.

 

Change the sleep timers if it is too fast/slow. Ctr+1 is the hotkey to start it. I have made Escape into a hotkey while the script is running to immediately stop the script in case there is a failure of some sort. Enjoy

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
#Warn  ; Enable warnings to assist with detecting common errors.
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.

$^1::
Setkeydelay, 35

; SELECT THE EXCEL FILE TO OPEN AND MAKE A VARIABLE CONTROL FOR IT CALLED Xl
FileSelectFile, Path, 3, , Open Excel File, Excel File (*.xls; *.xlsx)
if Path =
	ExitApp
Xl := ComObjCreate("Excel.Application")
Xl.Workbooks.Open(Path) ;open an existing file
Xl.Visible := True
if Errorlevel
{
	MsgBox, % "Error opening excel file! Exiting..."
	ExitApp
}

; Ask user to choose the sheet to open
Sheet_Titles =
global Sheet_To_Calculate := 0 
loop, % xl.Sheets.Count
	Sheet_Titles .= A_Index . ": " . xl.Sheets(A_Index).Name . "`n"
While Sheet_To_Calculate < 1 || Sheet_To_Calculate > xl.Sheets.Count || !IF_Integer(Sheet_To_Calculate)
	inputbox, Sheet_To_Calculate, % Xl.ActiveWorkbook.Name, %Sheet_Titles%, , , , , , , , 1
if ErrorLevel
	ExitApp


; ASK WHICH ROW TO START ON, DEFAULT TO 2 SINCE 1 IS USUALLY THE COLUMN TITLES
global Current_Row := -1
While Current_Row < 1 || !IF_Integer(Current_Row)
	inputbox, Current_Row, % Xl.ActiveWorkbook.Name, % "Enter the row to start on.", , , , , , , , 2
if ErrorLevel
	ExitApp

; THE REAL MEAT :) while the current row's A column is not blank, continue the loop
while Xl.Sheets(Sheet_To_Calculate).Range("A" . Current_Row).Value != ""
{
	
	; Store the values into variables
	A_stored := Xl.Sheets(Sheet_To_Calculate).Range("A" . Current_Row).Value
	B_stored := Xl.Sheets(Sheet_To_Calculate).Range("B" . Current_Row).Value
	C_stored := floor(Xl.Sheets(Sheet_To_Calculate).Range("C" . Current_Row).Value)
	
	; THIS MAY NEED CHANGING NOT SURE... it will bring the window labeled B32PTS102.entry.com-Remote Desktop Connection to the front
	ControlFocus,, B32PTS102.entry.com-Remote Desktop Connection
	Sleep 550
	
	; Enter A and enter five times
	MouseClick, Left, 548, 126
	Sleep 150
	Send %A_stored%
	Send {Enter 5}
	Sleep 100
	
	; Enter B and enter five times
	MouseClick, Left, 548, 423
	Sleep 150
	Send %B_stored%
	Send {Enter 5}
	Sleep 100
	
	; Enter C and enter five times
	MouseClick, Left, 548, 627
	Sleep 150
	Send %C_stored%
	Send {Enter 5}
	Sleep 100
	
	; Move to the next row
	Current_Row++
}

MsgBox, % "Done! :)"
ExitApp

IF_Integer(check_this)
{
	if check_this is not integer
		return false
	else
		return true
}

$Esc::ExitApp ;EMERGENCY EXIT HOTKEY

[Bristin]

You guys are awesome thanks so much. I have gotten stuck with other projects but should get a chance to give this a whirl tomorrow and can report back!!

[Cobalt]

Please do. I would like to know if it worked. If you need any more help let me know, I am very knowledgeable about accessing excel data and having ahk type for me.

 

 

You guys are awesome thanks so much. I have gotten stuck with other projects but should get a chance to give this a whirl tomorrow and can report back!!

[robert_ilbrink]

What I did in the past was to ask the user to select the range in Excel, then copy that and store it in a 2 dimensional Pseudo array.

This way I "read" the data once in the beginning and I could loop through all the entries from my array.

 

Here is an example from several years ago;

Send, ^c ; Copy the selected data to clipBoard
ClipWait,2
MyClipBoard=%ClipBoard% ; Store the ClipBoard data in variable MyClipBoard
Send, {Esc} ; Cancel the copy command in Excel.
Loop, parse, MyClipBoard, `n,`r ; Place Excel content from clipboard in 2 dimentional Array
{ ; Start-Loop Read through each Line (record) in the ClipBoard data from Excel
	FullRecord:=A_LoopField ; Put a whole row in the variable FullRecord
	RecordNumber:=A_Index ; Store the current record (row) number.
	Loop, parse, FullRecord, %A_Tab% ; Parse the row content (FullRecord) in separate fields
	{ ; Start-Loop Read through each Field in the ClipBoard data from Excel
		FieldNumber:=A_Index ; Store the current Field number.
		Array%RecordNumber%_%FieldNumber% := A_LoopField ; Store the current Field data in 2 dimentional ArrayRow_Column. Array1_1 is Excel Row 1 column A
	} ; End-Loop Read through each Field in the ClipBoard data from Excel
} ; End-Loop Read through each Line (record) in the ClipBoard data from Excel
TotalRecordNumber:=(RecordNumber-1) ; Record number from loop ends-up one too high, so we correct his here

[Bristin]

Ok cobalt I took your code and dropped it into the script editor and saved it and had a few errors. I do wonder if perhaps I am missing something as a couple of the commands I don't see listed in autohotkey so maybe I need an add on or something? For example I got the below error

 

Error at line 2

line text: #Warn                

Error: this line does not contain a recognized action 

 

I figured maybe it was  simple syntax error so I tried to to look up the command #Warn and did not see it listed in the autohotkey help. Am I missing something here?

 

Since you had documented everything so well I thought I could just remove the error reporting for the moment to try and get it to work for the time being. 

So I removed line 2 and loaded the script again.

 

This time I got and error in line 13

"The following variable name contains an illegal character

Xl.Visible := True

 

Not usre what its issue it

[robert_ilbrink]

Which version are you using?

[Jackie Sztuk _Blackholyman]

Sounds like you dont have the right version of autohotkey

http://l.autohotkey....mands/_Warn.htm

Try this and tell os the output

Msgbox % A_ahkversion

Hope it helps

[garry]

script with commands Warn and ComObjCreate needs ahk_L

( maybe can use also a csv-file (text-file)  )

;- http://l.autohotkey.net/v/               ; ahk_L
;- http://www.autohotkey.net/#archives      ; ahk_basic

info :=      "AHK Version:`t" A_AhkVersion
   . "`nUnicode:`t`t" (A_IsUnicode ? "Yes " ((A_PtrSize=8) ? "(64-bit)" : "(32-bit)") : "No")
   . "`nOperating System:`t" (!A_OSVersion ? A_OSType : A_OSVersion)
   . "`nAdmin Rights:`t" (A_IsAdmin ? "Yes" : "No")
msgbox,%info%
return

[Bristin]

I am using verison 1.0.48.05

[Jackie Sztuk _Blackholyman]

Then you'ed need to download the new version ahk > 1.1

Get it from the front page or here http://l.autohotkey.net/

note: robert_ilbrink's Script will work i Think but still i Think its Best to use the new version

[Bristin]

I bow down before the collective genius of this board!

Thanks everyone so much for your help this will help save 1000's of keystrokes per day.

I also feel I have gained a much better understanding of your exceptionally well documented code that I can use pieces and parts of in future projects.