Excel Spreadsheet automation...please help! Topic is solved

Get help with using AutoHotkey (v1.1 and older) and its commands and hotkeys
holycrapamelon
Posts: 3
Joined: 04 Oct 2018, 09:46

Excel Spreadsheet automation...please help!

04 Oct 2018, 10:14

Hey guys. First time posting to the forums and third day using autohotkey! Be gentle, yea? I'm here to learn from all of you.

Here is my issue. I have a spreadsheet put into the same directory every day that is named ML NORTHEAST_FOODS_*.xls where the * references an 8 digit date. Today, it was 20181003. I need to take data from cells and paste them into a web application. I wrote a quick script to copy and paste specific cells and it works (it's simple, but it does the job). My issue is twofold. I cannot figure out how to get the script to progress down through the excel spreadsheet without manually typing every single cell reference. I also cannot get the script to intelligently pull the filename from the directory since the filename changes every day, but always according to the same conventions.

Here's what I have so far.

Code: Select all

^j::
FilePath := "\\svdc03\Temp\NEF_MenuLink_New_Hires\ML NORTHEAST_FOODS_20181003.xls" ;  FILEPATH
oWorkbook := ComObjGet(FilePath) ; access Workbook object
clipboard := oWorkbook.Sheets(1).Range("A3").Value ; get value from A3 cell in first sheet
Sendinput ^v ; send paste command	 
Sleep, 50 ; Give some time for the text to be pasted.
clipboard = ; clear the clipboard
Sendinput {TAB 2} ; proceed to next field


clipboard := oWorkbook.Sheets(1).Range("D3").Value
Sendinput ^v ; send paste command	
Sleep, 50
Clipboard = ; clear
Sendinput {TAB} 

clipboard := oWorkbook.Sheets(1).Range("F3").Value
Sendinput ^v ; send paste command
Sleep, 50
Clipboard = ; clear	
Sendinput {TAB}
AND SO ON, all through the document. I reference 18 cells in all. As you'll notice, I have them all referencing row 3 right now. I have a full script for row 3, but want to use the same hotkey to proceed automatically to row 4, then to row 5, and so on.

Again, first time posting on the boards. Let me know if I need to clarify anything! Thanks in advance for any suggestions!
wbm1113
Posts: 18
Joined: 28 Aug 2018, 11:19

Re: Excel Spreadsheet automation...please help!

04 Oct 2018, 11:09

Code: Select all

CounterSetVal := 3
TransferVal := ""

^j::
DateSplitter := SubStr(A_Now, 1, 8)
FilePath := "\\svdc03\Temp\NEF_MenuLink_New_Hires\ML NORTHEAST_FOODS_" DateSplitter ".xls"
x1 := ComObjGet(FilePath)

TransferVal := x1.Sheets(1).Range("A" CounterSetVal)
SendInput, %TransferVal%
SendInput, {TAB 2}
TransferVal := ""

TransferVal := x1.Sheets(1).Range("D" CounterSetVal)
SendInput, %TransferVal%
SendInput, {TAB}
TransferVal := ""

TransferVal := x1.Sheets(1).Range("F" CounterSetVal)
SendInput, %TransferVal%
SendInput, {TAB}
TransferVal := ""

CounterSetVal++
return
Edit: to automate the letters rather than type those out individual as well:

Code: Select all



AlphaVar := "abcdefghijklmnopqrstuvwxyz" ;Initialize alphabet array
AlphaArray := []
Loop, 26 {
	CurLetter := SubStr(AlphaVar, A_Index, 1)
	AlphaArray.Push(CurLetter)
}

CounterSetVal := 3 ;Initialize variables
TransferVal := ""

DateSplitter := SubStr(A_Now, 1, 8) ;Get the date format you need
FilePath := "\\svdc03\Temp\NEF_MenuLink_New_Hires\ML NORTHEAST_FOODS_" DateSplitter ".xls"
x1 := ComObjGet(FilePath)

^j::

Loop, 18 {
	If(A_Index=1) {
		B_Index := A_Index
	} else {
		B_Index := A_Index + 2 ; skips letters "B" and "C" per your example
	}
	CurLetter := AlphaArray[B_Index]
	TransferVal := x1.Sheets(1).Range(CurLetter CounterSetVal) ;
	SendInput, %TransferVal%
	If(A_Index=1) {
		SendInput, {TAB 2} ; tabs twice on the first entry, then once on subsequent entries per your example
	} else {
		SendInput {TAB}
	}
	TransferVal := ""
}

CounterSetVal++ ; starts at 3, then each subsequent press brings it to 4, 5, 6, etc.
return
awel20
Posts: 211
Joined: 19 Mar 2018, 14:09

Re: Excel Spreadsheet automation...please help!

04 Oct 2018, 11:48

Code: Select all

; this part of the script runs when it is first launched (see auto-execute section in the docs)

; get yesterdays date
EnvAdd, YesterdaysDate, -1, Days 
;MsgBox % YesterdaysDate
FormatTime, YesterdaysDate, % YesterdaysDate, yyyyMMdd
;MsgBox % YesterdaysDate

FilePath := "C:\Test\Name_" YesterdaysDate ".xlsx"
oExcel := ComObjCreate("Excel.Application")
;oExcel.Visible := true
oWorkbook := oExcel.Workbooks.Open(FilePath)
nRows := 3 ; the number of rows
nFirstRow := 3 ; the first row
objColumns := ["A", "D", "F"] ; the columns
objData := [] ; objData will store the values
Loop, % nRows
{
    Row := A_Index + nFirstRow - 1 ; current row
    for i, Col in objColumns ; for each column...
        objData.Push( oWorkbook.Sheets(1).Range(Col Row).Value ) ; store the cell value
}
oWorkbook.Close(0) ; close the workbook
oExcel.Quit ; quit excel
oExcel := oWorkbook := "" ; clear COM objects (not required for local vars)
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

^j::
Clipboard := objData[nItem++] ; get the current item and increment nItem
Send, ^v
if (nItem > nMaxItem) ; if this is the last item
{
    MsgBox, Done
    nItem := 1
}
return
holycrapamelon
Posts: 3
Joined: 04 Oct 2018, 09:46

Re: Excel Spreadsheet automation...please help!

04 Oct 2018, 14:05

awel20, you're the man. Quick question off of that.... some of my values include a substring. I have MM/DD/YYYY dates in the excel spreadsheet that I have to paste into 3 separate text fields in my web application. I have them referenced as substrings now:

Code: Select all

String := oWorkbook.Sheets(1).Range("Q3").Value
clipboard := SubStr(String, 1, 2) ; get value from Q3 cell in first sheet
Sendinput ^v ; send paste command	 
Sleep, 50 ; Give some time for the text to be pasted.
clipboard = ; clear the clipboard
Sendinput {TAB} ; proceed to next field

String := oWorkbook.Sheets(1).Range("Q3").Value
clipboard := SubStr(String, 4, 2) ; get value from Q3 cell in first sheet
Sendinput ^v ; send paste command	 
Sleep, 50 ; Give some time for the text to be pasted.
clipboard = ; clear the clipboard
Sendinput {TAB} ; proceed to next field

String := oWorkbook.Sheets(1).Range("Q3").Value
clipboard := SubStr(String, 7, 4) ; get value from Q3 cell in first sheet
Sendinput ^v ; send paste command	 
Sleep, 50 ; Give some time for the text to be pasted.
clipboard = ; clear the clipboard
Sendinput {TAB} ; proceed to next field
Each of those code blocks corresponds to the "MM" "DD" and "YYYY" of each date. How would I work that into your code?
awel20
Posts: 211
Joined: 19 Mar 2018, 14:09

Re: Excel Spreadsheet automation...please help!  Topic is solved

04 Oct 2018, 14:17

Maybe something like this

Code: Select all

Loop, % nRows
{
    Row := A_Index + nFirstRow - 1 ; current row
    for i, Col in objColumns ; for each column...
    {
        if (Col = "Q") ; special case for column Q
        {
            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 = "X") ; special case for column X
        {
            ;...
        }
        else
            objData.Push( oWorkbook.Sheets(1).Range(Col Row).Value ) ; store the cell value
    }
}
User avatar
FanaticGuru
Posts: 1906
Joined: 30 Sep 2013, 22:25

Re: Excel Spreadsheet automation...please help!

04 Oct 2018, 14:27

holycrapamelon wrote:Here is my issue. I have a spreadsheet put into the same directory every day that is named ML NORTHEAST_FOODS_*.xls where the * references an 8 digit date. Today, it was 20181003. I also cannot get the script to intelligently pull the filename from the directory since the filename changes every day, but always according to the same conventions.
I have a similar file situation where I need to open the most current file. I also purposely name the files very much like you do with the date formatted like 20181003 at the end. This causes my files to appear in alphabetical order in my file directory.

This script get the last alphabetical match of a file name:

Code: Select all

; Open last Summary file
Loop, Files, D:\Users\FG\Documents\Summary - All - *.xlsx
	if (A_LoopFilePath>File) or !File
		File := A_LoopFilePath

try
	xlApp := ComObjActive("Excel.Application")
catch
	xlApp := ComObjCreate("Excel.Application")

xlApp.Visible := true
xlApp.Workbooks.Open(File)

This is important because I do not have a file everyday so I cannot just get the one for today or yesterday but I do want to get the latest one.

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
holycrapamelon
Posts: 3
Joined: 04 Oct 2018, 09:46

Re: Excel Spreadsheet automation...please help!

04 Oct 2018, 17:41

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
Cheers!
awel20
Posts: 211
Joined: 19 Mar 2018, 14:09

Re: Excel Spreadsheet automation...please help!

04 Oct 2018, 19:07

Nice!
I have a few suggestions.
1) you can simplify the repetitive stuff under the hotkey.
2) Since excel is invisible, you need to close the workbook, quit excel, and clear the vars that contain com objects. Otherwise excel stays open in the background.
3) You could change Loop, 99 to just Loop, and instead have it break the loop when it hits a blank cell.

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
{
    Row := A_Index + nFirstRow - 1 ; current row
    for i, Col in objColumns ; for each column
    {
        currentCell := oWorkbook.Sheets(1).Range(Col Row).Value
        if (i = 1 && currentCell = "") ; if i=1 and the current cell is blank. i=1 when this is the first column.
            break, 2  ; 2 = break the for-loop and the outer loop
        if (Col = "Q" || Col = "P") ; special case for column Q or P date format
        {
            objData.Push(SubStr(currentCell, 1, 2))
            objData.Push(SubStr(currentCell, 4, 2))
            objData.Push(SubStr(currentCell, 7, 4))
        }
        else
            objData.Push(currentCell) ; store the cell value
    }
}
oWorkbook.Close(0) ; close the workbook
oExcel.Quit ; quit excel
oExcel := oWorkbook := "" ; clear COM objects (not required for local vars)
nItem := 1 ; this keeps track of which item in objData to paste
nMaxItem := objData.MaxIndex() ; the number of the last item in objData
objTabs := [2, 1, 1, 8, 1, 1, 1, 1, 1, 6, 3, 2, 1, 2, ""] ; the number of tabs to use between each item in the row
return ; end of auto-execute section

;----------------------------------HOTKEY FUNCTIONS--------------------------
;----------------------------------------------------------------------------

^j::
for i, nTabs in objTabs ; for each item in objTabs. <- edited several hours after posting
{
    Clipboard := objData[nItem++] ; get the current item and increment nItem
    Sleep, 50
    Send, ^v
    Sleep, 50
    if (nTabs != "") ; if nTabs is not blank
        Sendinput, {TAB %nTabs%}
}
Clipboard := ""
if (nItem > nMaxItem) ; if this is the last item
{
    MsgBox, Done
    nItem := 1
}
return
Edited

Return to “Ask for Help (v1)”

Who is online

Users browsing this forum: marypoppins_1, Rohwedder, Spawnova and 153 guests