Thank you all (especially awel20) for your help! I was able to get the script to work exactly as I wanted it. This will save me hours every week. I'll post the full script below, in case anyone wants to take a look at the small changes and adaptations I made.
Code: Select all
; ---------------------------AUTO-EXECUTE FUNCTIONS------------------------
;------------------------------------------------------------------------
; get yesterdays date
EnvAdd, YesterdaysDate, -1, Days
;MsgBox % YesterdaysDate
FormatTime, YesterdaysDate, % YesterdaysDate, yyyyMMdd
;MsgBox % YesterdaysDate
FilePath := "\\svdc03\Temp\NEF_MenuLink_New_Hires\ML Northeast_Foods_" YesterdaysDate ".xls" ; REFERENCE
;oWorkbook := ComObjGet(FilePath) ; use this one if the excel document is already open
oExcel := ComObjCreate("Excel.Application")
;oExcel.Visible := true
oWorkbook := oExcel.Workbooks.Open(FilePath) ; use this one to launch the excel document with the script
nRows := 999 ; the number of rows
nFirstRow := 2 ; the first row
objColumns := ["A", "D", "F", "E", "Q", "P", "H", "J", "K", "L", "G"] ; the columns in order of entry
objData := [] ; objData will store the values
Loop, 99 ; how many fields need to be entered
{
Row := A_Index + nFirstRow - 1 ; current row
for i, Col in objColumns ; for each column
{
if (Col = "Q") ; special case for column Q date format
{
currentCell := oWorkbook.Sheets(1).Range(Col Row).Value
objData.Push(SubStr(currentCell, 1, 2))
objData.Push(SubStr(currentCell, 4, 2))
objData.Push(SubStr(currentCell, 7, 4))
}
else if (Col = "P") ; special case for column P date format
{
currentCell := oWorkbook.Sheets(1).Range(Col Row).Value
objData.Push(SubStr(currentCell, 1, 2))
objData.Push(SubStr(currentCell, 4, 2))
objData.Push(SubStr(currentCell, 7, 4))
}
else
objData.Push( oWorkbook.Sheets(1).Range(Col Row).Value ) ; store the cell value
}
}
nItem := 1 ; this keeps track of which item in objData to paste
nMaxItem := objData.MaxIndex() ; the number of the last item in objData
return ; end of auto-execute section
;----------------------------------HOTKEY FUNCTIONS--------------------------
;----------------------------------------------------------------------------
^j::
Clipboard := objData[nItem++] ; get the current item and increment nItem (Badge Number)
Sleep, 50
Send, ^v
Sleep, 50
Sendinput, {TAB 2}
Clipboard := objData[nItem++] ; get the current item and increment nItem (First name)
Sleep, 50
Send, ^v
Sleep, 50
Sendinput, {TAB}
Clipboard := objData[nItem++] ; get the current item and increment nItem (DOB MM)
Sleep, 50
Send, ^v
Sleep, 50
Sendinput, {TAB}
Clipboard := objData[nItem++] ; get the current item and increment nItem (last name)
Sleep, 50
Send, ^v
Sleep, 50
Sendinput, {TAB 8}
Clipboard := objData[nItem++] ; get the current item and increment nItem (DOB MM)
Sleep, 50
Send, ^v
Sleep, 50
Sendinput, {TAB}
Clipboard := objData[nItem++] ; get the current item and increment nItem (DOB DD)
Sleep, 50
Send, ^v
Sleep, 50
Sendinput, {TAB}
Clipboard := objData[nItem++] ; get the current item and increment nItem (DOB YYYY)
Sleep, 50
Send, ^v
Sleep, 50
Sendinput, {TAB}
Clipboard := objData[nItem++] ; get the current item and increment nItem (HD MM)
Sleep, 50
Send, ^v
Sleep, 50
Sendinput, {TAB}
Clipboard := objData[nItem++] ; get the current item and increment nItem (HD DD)
Sleep, 50
Send, ^v
Sleep, 50
Sendinput, {TAB}
Clipboard := objData[nItem++] ; get the current item and increment nItem (HD YYYY)
Sleep, 50
Send, ^v
Sleep, 50
Sendinput, {TAB 6}
Clipboard := objData[nItem++] ; get the current item and increment nItem (Address Line 1)
Sleep, 50
Send, ^v
Sleep, 50
Sendinput, {TAB 3}
Clipboard := objData[nItem++] ; get the current item and increment nItem (City)
Sleep, 50
Send, ^v
Sleep, 50
Sendinput, {TAB 2}
Clipboard := objData[nItem++] ; get the current item and increment nItem (State)
Sleep, 50
Send, ^v
Sleep, 50
Sendinput, {TAB}
Clipboard := objData[nItem++] ; get the current item and increment nItem (Postal Code)
Sleep, 50
Send, ^v
Sleep, 50
Sendinput, {TAB 2}
Clipboard := objData[nItem++] ; get the current item and increment nItem (SSN)
Sleep, 50
Send, ^v
Sleep, 50
Clipboard :=
if (nItem > nMaxItem) ; if this is the last item
{
MsgBox, Done
nItem := 1
}
return