Copy text from Excel List into web site "input boxes"

[Jamesraywebber85]

I am very new to AutoHotKey & have self taught myself the basics however I need a little help. If found this code on this board & have tired to merge it with the script I already have. Can anyone help me or guide me in the right direction. I wish for this to automatically run so I can step away from my desk & allow this to automatically put the info into the site.

This is the code I found on the board here:

Code: Select all

; This script gets a range of cells in column A and B. (Like selecting cell A2 and then pressing Ctrl+Shift+Down and
; then Shift+Right.) Then each time the Ctrl+F12 hotkey is pressed it sends the next value.

; Usage:
; Press Ctrl+F12 to send the next value.

; Constants
xlDown := -4121

WorkbookPath := A_ScriptDir "\MyWorkbook.xlsx"       ; <-- Change this to the path of your workbook
xlApp := ComObjCreate("Excel.Application")                            ; Create an instance of Excel
xlApp.Visible := true                                                          ; Make Excel visible
MyWorkbook := xlApp.Workbooks.Open(WorkbookPath)                                ; Open the workbook
CellA2 := xlApp.Cells(2, 1)                                          ; Store a reference to cell A2
LastCell := CellA2.End(xlDown).Offset(0, 1)  ; Store the last cell. .End is like pressing Ctrl+Down
MyRange := xlApp.Range(CellA2, LastCell)               ; Store a reference to the Range A2:LastCell
CellNumber := 1                                   ; This variable will store the cell number to use
CellCount := MyRange.Cells.Count                            ; Store the count of cells in the range
return

^F12::                                                                            ; Ctrl+F12 hotkey
    SendRaw % MyRange.Cells(CellNumber).Text      ; Send the current cell specified by 'CellNumber'
    CellNumber++                                                     ; Increase 'CellNumber' by one
    if (CellNumber > CellCount) {    ; If 'CellNumber' is greater than the total amount of cells...
        MsgBox, 64, Info, Finished. No more cells.                                           ; Done
        CellNumber := 1
    }
return

; References
; https://autohotkey.com/boards/viewtopic.php?p=112648#p112648
; https://github.com/ahkon/MS-Office-COM-Basics/blob/master/Examples/Excel/Cells_in_a_column.ahk

This is my code:

Code: Select all

+!a::
FormatTime, CurrentDate, , MM/dd/yy
InputBox, CID, CID, CID Number, , 200, 200
InputBox, Order, Order, Order Number, , 200, 200
if ErrorLevel

   MsgBox, CANCEL was pressed.

; Create IE instance
Run http://projects/PWA/ERP Project/Team Portal/Lists/New Triage/NewForm.aspx
Sleep,500

WinWait, ERP Triage - New Item, 
IfWinNotActive, ERP Triage - New Item, , WinActivate, ERP Triage - New Item, 
WinWaitActive, ERP Triage - New Item, 

Sleep, 2000
Send {t}{t}{t}
Send, {Tab}
Sleep, 100
Send {k}
Sleep, 100
Send, {Tab 4}
Sleep, 100
Send rwebber
Sleep, 100
Send, {Tab 3}
Sleep, 100
Send {4}
Sleep, 100
Send, {Tab}
Sleep, 100
Send {e}{e}{e}{e}{e}{e}{e}
Sleep, 100
Send, {Tab 1}
Sleep, 100
Send {k}
Sleep, 100
Send, {Tab 1}
Sleep, 100
Send {o}{o}
Sleep, 100
Send, {Tab 1}
Sleep, 100
Send {o}{o}
Sleep, 100
Send, {Tab 1}
Sleep, 100
Send {a}{a}{a}{a}{a}
Sleep, 100
Send, {Tab 16}
Sleep, 100
Send %CID%
Sleep, 100
Send, {Tab 2}
SendInput %CurrentDate% 
Send, {Tab 3}
Sleep, 100
Send, 0
Send, {Tab}
Send %Order%
Sleep, 100
Send, {Tab 1}
Sleep, 100
Send Suspended order, please fix
Return

My excel sheet data is as follows:
Sheet name = "Sheet1" (there are a lot of sheets in this workbook)
C2 = Customer #
F2 = Order #

After the 1st ticket is create (after my script sends "Suspended order, please fix") I would like for it to make the next ticket using the Customer # from cell C3 & order # from cell F3, then C4 & F4 and so on till the end of the list. Any help would be greatly apricated. Thank you in advance!

[flyingDman]

What I like to do in this circumstance is to read all the Excel data into an array. In this case you have pairs of numbers. So the main array is an array of arrays. Each sub-array consist of the content of column C and the content of column F, one pair for each row. The input in your web form starts with the pressing of hotkey #a. This hotkey loops through the data pairs until exhausted. Obviously, I cannot test your +!a hotkey but wherever you input your CID Number you would use tmp.1 and wherever you input your Order number you would use tmp.2.

Code: Select all

arr := []
xl := ComObjActive("excel.application")    ; or ComObjCreate to open a file programmatically
lstrw := xl.range("C2").End(-4121).row
for c in xl.range("C2:C" lstrw)
	arr.push([c.text,c.offset(0,3).text]) 	; creates the array 
return

#a::
while (tmp := arr.RemoveAt(1))                 ; the while loop removes the first pair until empty. tmp is an array
	{
	;
	send % tmp.1 " "
	;
	send % tmp.2 "`n"
	;
	}
return

[Jamesraywebber85]

So I would use this script before I have the run command for my web page? Then instead of using a input box when I run the hotkey, I would just use the send % tmp.1 " " for the CID and send % tmp.2 "`n" for the order number? Thank you so much for your help with this!

[flyingDman]

That's the idea, yes.

[Jamesraywebber85]

ok so didn't quite do what I was looking for, I want for the list in excel which has the following data

Customer # in column C
Order # in column F

When I press Alt+Shift+A, I want the site
"http://projects/PWA/ERP Project/Team Portal/Lists/New Triage/NewForm.aspx" to launch and put the Customer # from C2 & the order from F2. Then it would open the same site, however this time it would put customer # from C3 & the order number from F3. Then open the same site again & put data from C4 & F4 and so on. Thank you for your knowledge & help.

[flyingDman]

That's exactly what it does. It takes the numbers in the column C and the numbers in column F (starting in row 2) and puts all those pairs in an array. When you want to paste it, it use a loop such that in each iteration of the loop, tmp.1 and tmp.2 represent successive pairs, one row at a time.
Say you have in column C the following values: C2, C3, C4 and in column F: F2, F3, F4 . The hotkey will send
C2 F2
C3 F3
C4 F4
Obviously since I do not have access to that site, I can not test that part of the script. So you need to adapt it and include it in your code.