Creating and Ending a Loop in Using Excel Data

Get help with using AutoHotkey (v1.1 and older) and its commands and hotkeys
CodeFourSDSO
Posts: 7
Joined: 09 Feb 2018, 12:27
Contact:

Creating and Ending a Loop in Using Excel Data

09 Feb 2018, 12:47

Hello,

Brand new to AHK so apologies in advance for lack of experience/knowledge. Normally I'd crunch away on my own until I figured something out but it's a time crunch for a work project and I'm sure you guys/gals probably have a simple solution for me.

I'm trying to create a script that will Copy data from a cell in excel to the clipboard, ALT-Tab to a third party software, paste the data into a field, (a set of macros will run some basic functions on it) and print a report. I would then like to have the script go back to excel, grab the data in the next row, (same column) and repeat the process until it reaches a row with no data. The number of rows used in each spreadsheet is variable.

Grabbing the data and moving it to the other software, performing functions, and going back to excel seems straight forward. However, I would like to have some sort of loop that will grab the data from A1, perform the functions mentioned above, repeat with A2, A3, A4 etc. until it reaches a row with no data, at which point the script should end.

I was thinking a loop that continues running until a check is performed on what is stored in clipboard is empty.

Any suggestions? Any help is greatly appreciated.

Example:
COLUMN A
A
B
C
D

(script should move A, from excel to the other program, use the data to generate a report (simple key input), repeat this process with B, C, D, then end script after moving on to the next row after D since it's empty.)
Odlanir
Posts: 659
Joined: 20 Oct 2016, 08:20

Re: Creating and Ending a Loop in Using Excel Data

09 Feb 2018, 13:11

Code: Select all

#SingleInstance, force
#NoEnv
#Persistent

xlApp := ComObjActive("Excel.Application")
xlWS  := xlApp.Sheets(1) 
row := 0
return

#w:: ; windows key + w
    ++row
    if ( xlWS.cells(row,1).value = "" )  { ; if column 1 has no data exit
        clipboard := ""
        ExitApp
    }
    clipboard := xlWS.cells(row,1).value
    ; call your program with the new data in clipboard and so on
return
ExitApp

esc::
	ExitApp
____________________________________________________________________________
Windows 10 Pro 64 bit - Autohotkey v1.1.30.01 64-bit Unicode
CodeFourSDSO
Posts: 7
Joined: 09 Feb 2018, 12:27
Contact:

Re: Creating and Ending a Loop in Using Excel Data

09 Feb 2018, 13:51

Odlanir, you are a wizard! I definitely could NOT have figured that out on my own! Thank you so much looking forward to playing with this and making it work. Really appreciate that prompt assistance though, very kind of you

Cheers!
CodeFourSDSO
Posts: 7
Joined: 09 Feb 2018, 12:27
Contact:

Re: Creating and Ending a Loop in Using Excel Data

20 Feb 2018, 13:20

Hi Odlanir,

I have a few more questions if you have a moment I'd really appreciate your time.

Assumptions:
At the time I run the script command I have:
-Excel running with the cell A1 selected
-Proprietary database opened in the background with the cursor located at a user input cell. I will call this Program A from now on.

I would like the following to occur upon pressing the command, in my case #w
- contents from Excel A1 copied to the clip board
- ALT / Tab to Program A
- clipboard pasted into the user input cell
- A set of commands that automatically take place that essentially print a report and return to the home screen user input box
- ALT / Tab back to Excel, drop down one row in the same column (A2) and repeat this process until the last record in the excel spreadsheet doesn't have data.
- Program closes

Notes: The chunk of code I embedded into yours works in a standalone script I wrote. When I run program A and manually enter data into the user input field and run just that portion of the script, it will in fact locate the queried data, print the report, and return to the home screen in the user input field waiting to accept more data. I don't know much, all the sleeps I'm using may not be necessary, but it kept hanging up if I tried to do too much too fast.

My goal in this project is to automate the user entry portion of the data in Program A by having the script use a spreadsheet of data that can go back and forth between the two programs entering the data, printing the report, and continuing until the spreadsheet doesn't contain data.

I work in Law Enforcement and this effort will greatly assist the way the agency is spending time on this process, it would be a huge help to get it working. Thanks for your assistance!


#SingleInstance, force
#NoEnv
#Persistent

xlApp := ComObjActive("Excel.Application")
xlWS := xlApp.Sheets(1)
row := 0
return

#w:: ; windows key + w
++row
if ( xlWS.cells(row,1).value = "" ) { ; if column 1 has no data exit
clipboard := ""
ExitApp
}
clipboard := xlWS.cells(row,1).value
; call your program with the new data in clipboard and so on

send, !{tab} ; simpling entering data from the clipboard to an input field, printing a report, and exiting back to the initial data entry field
sleep, 30
send, ^V
send, {tab}
sleep, 30
send, {enter}
sleep, 2000
send, ^p
WinWaitActive, Print
sleep, 2500
send, {enter}
sleep, 6000
WinClose, A
sleep, 2000
send, {enter}
sleep, 30
send, {tab}
sleep, 30
send, {tab}

Return


ExitApp

esc::
ExitApp
User avatar
FanaticGuru
Posts: 1906
Joined: 30 Sep 2013, 22:25

Re: Creating and Ending a Loop in Using Excel Data

20 Feb 2018, 17:50

You don't need to Alt-Tab between Excel and your program. You can stay in your program and just pull the data from Excel as you need it. Excel never has to be active or even visible for that matter.

It will save alot of trouble with the Alt+Tabbing.

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
CodeFourSDSO
Posts: 7
Joined: 09 Feb 2018, 12:27
Contact:

Re: Creating and Ending a Loop in Using Excel Data

20 Feb 2018, 17:53

FanaticGuru wrote:You don't need to Alt-Tab between Excel and your program. You can stay in your program and just pull the data from Excel as you need it. Excel never has to be active or even visible for that matter.

It will save alot of trouble with the Alt+Tabbing.

FG
That sounds like some next level scripting I'd definitely be interested in making work! So I can reference just the .xls filename somehow and pull data from it without opening it? Any tips would be greatly appreciated and thanks for your response :)
User avatar
FanaticGuru
Posts: 1906
Joined: 30 Sep 2013, 22:25

Re: Creating and Ending a Loop in Using Excel Data

20 Feb 2018, 18:02

Example:

Code: Select all

; Starting Row and Column
Row := 3
Column := 2

F12::
	xlApp := ComObjActive("Excel.Application")
	val := xlApp.Cells(Row, Column).Value
	Row++
	if !(val = "")
		SendRaw, % val
	else
		MsgBox DONE
return
With the source Excel sheet running (can be minimized whatever, AHK could open Excel and load the sheet if wanted), each time you hit F12 the next cell down starting at B3 will be typed in whatever application you have active.

No need to Alt-Tab.

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
User avatar
FanaticGuru
Posts: 1906
Joined: 30 Sep 2013, 22:25

Re: Creating and Ending a Loop in Using Excel Data

20 Feb 2018, 18:18

Example of Opening Excel Worksheet.

Code: Select all

; Create new instance of Excel and show it
xlApp := ComObjCreate("Excel.Application")
xlApp.Visible := true ; remove or comment out this line to not show Excel

; Open Workbook
xlWb := xlApp.Workbooks.Open("C:\Users\FG\Desktop\Test\Test.xlsx")

; Starting Row and Column
Row := 3, Column := 2

F12::
	val := xlWb.ActiveSheet.Cells(Row, Column).Value
	Row++
	if (val = "")
	{
		xlApp.Quit()
		MsgBox DONE
	}
	else
		SendRaw, % val
return
If you don't do the xlApp.Visible := true then Excel will open but not be visible and all the Excel stuff will happen in the background.

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
CodeFourSDSO
Posts: 7
Joined: 09 Feb 2018, 12:27
Contact:

Re: Creating and Ending a Loop using Excel Data

21 Feb 2018, 10:07

[/code][/code]
FanaticGuru wrote:Example:

Code: Select all

; Starting Row and Column
Row := 3
Column := 2

F12::
	xlApp := ComObjActive("Excel.Application")
	val := xlApp.Cells(Row, Column).Value
	Row++
	if !(val = "")
		SendRaw, % val
	else
		MsgBox DONE
return
With the source Excel sheet running (can be minimized whatever, AHK could open Excel and load the sheet if wanted), each time you hit F12 the next cell down starting at B3 will be typed in whatever application you have active.

No need to Alt-Tab.

FG
Hi FG!
I'm trying to incorporate the first bit of code you sent me with the excel sheet opened and minimized. Stand alone as you wrote it, it does in fact grab the data from the cell (A1) and paste it into my program. Pressing F12 again will place the data from A2 into the field, so this is golden and exactly what I'm looking for.

Now I need to have this process be able to repeat itself without the user pressing F12 each time I want data pasted to the program. For example, imagine the Excel spreadsheet has 50 records in column A. I would like the user to place the cursor in the input field in the program and press F12 one time which will cause the script to enter a loop, pasting the data from each cell into the input field, printing a report, navigating back to the input field, and repeating the process until there are no records left in the spreadsheet.

Any ideas? Below is what I have so far...

Code: Select all

; Starting Row and Column

#SingleInstance, force
#NoEnv
#Persistent

xlApp := ComObjActive("Excel.Application")
xlWS  := xlApp.Sheets(1) 
row := 0
return

Row := 1
Column := 1

F12::
	xlApp := ComObjActive("Excel.Application")
	val := xlApp.Cells(Row, Column).Value
	Row++
	if !(val = "")
		SendRaw, % val
	else
		MsgBox DONE
return
    
	send, {tab}
		sleep, 30
	send, {enter}
		sleep, 2000
	send, ^p
		WinWaitActive, Print
		sleep, 2500
	send, {enter}
		sleep, 6000
	WinClose, A
		sleep, 4000
	send, {enter}
		sleep, 30
	send, {tab}
		sleep, 30
	send, {tab}
		sleep, 30
	if val >= 1
		send, [{F12}
return

CodeFourSDSO
Posts: 7
Joined: 09 Feb 2018, 12:27
Contact:

Re: Creating and Ending a Loop in Using Excel Data

21 Feb 2018, 16:16

Perhaps this will clarify...
This code works stand alone to retrieve data from the spreadsheet and move it into a database program (thanks to FG)

Code: Select all

xlApp := ComObjActive("Excel.Application")
xlWS  := xlApp.Sheets(1) 
row := 0
return

Row := 1
Column := 1

F12::
	xlApp := ComObjActive("Excel.Application")
	val := xlApp.Cells(Row, Column).Value
	Row++
	if !(val = "")
		SendRaw, % val
	else
		MsgBox DONE
return
This code works stand alone once inside the database. It requires manually entering data into the input field but once ^F1 is pressed it will query a report, print it, and return the cursor to the input field ready to accept another entry

Code: Select all

^F1::
	send, {tab}
		sleep, 30
	send, {enter}
		sleep, 2000
	send, ^p
		WinWaitActive, Print
		sleep, 1000
	send, {enter}
		sleep, 5000
	send ^{F4}
		sleep, 500
	send, {enter}
		sleep, 30
	send, {tab}
		sleep, 30
	send, {tab}

Return
I am simply trying to combine the best of both worlds by creating a loop that will grab the data from the spreadsheet, move it to the database, use it, and automatically continue grabbing one record at a time, performing a print, grabbing data from the next spreadsheet row, and repeating the process until no data is encountered in the spreadsheet at which point a msgbox will display "All Done". I want to prevent the user from having to manually input the data into the field but rather have the script just move the data over one at a time.
User avatar
FanaticGuru
Posts: 1906
Joined: 30 Sep 2013, 22:25

Re: Creating and Ending a Loop in Using Excel Data

21 Feb 2018, 23:33

Below is a more complete script using the opening of an Excel file.

Code: Select all

; Create new instance of Excel and show it
xlApp := ComObjCreate("Excel.Application")
xlApp.Visible := true ; remove or comment out this line to not show Excel

; Open Workbook
xlWb := xlApp.Workbooks.Open("C:\Users\FG\Desktop\Test\Test.xlsx")

; Starting Row and Column
Row := 3, Column := 2 ; Cell B3 (change as needed)

F12::
	Loop ; start looping until something in the Loop stops the Loop
	{
		val := xlWb.ActiveSheet.Cells(Row, Column).Value
		Row++ ; Add 1 to Row for next Loop
		if (val = "") ; use "break" to stop Loop if cell is blank
			break
		; if Loop was not stopped by above if-statement do the below
		SendRaw, % val ; SendRaw just treats any characters with special meaning like ^+!#{} that might have been in cell as not special
		Send, {Tab}
		Sleep, 30
		Send, {Enter}
		Sleep, 2000
		Send, ^p
		WinWaitActive, Print
		Sleep, 2500 ; if the WinWaitActive worked properly it would not seem like you would need to Sleep here, you already waited for the print window to open
		Send, {Enter}
		Sleep, 6000
		WinClose, A
		Sleep, 4000
		Send, {Enter}
		Sleep, 30
		Send, {Tab}
		Sleep, 30
		Send, {Tab}
		Sleep, 30
	} ; loop again
	; when Loop is stopped then control jumps to just after Loop
	xlApp.Quit() ; Close Excel
	MsgBox DONE
	ExitApp ; Stop script as pushing F12 again would not work as this Workbooks is already at the end
return
Might need to tinker with the sending of keys between the SendRaw and } ; loop again but that is where you put all the automation of each record. You might need to get back to the proper field for the "val" to be entered at the end for the next loop, I don't know. Your use of Win commands are good. You might want to look at those type commands some more. You might be able to eliminate more of the Sleep commands or shorting them by detecting windows popping up and closing instead of just Sleeping for a set time.

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
CodeFourSDSO
Posts: 7
Joined: 09 Feb 2018, 12:27
Contact:

Re: Creating and Ending a Loop in Using Excel Data

22 Feb 2018, 10:52

SUCCESS!!!!!!!!! FG You're the best! THANK YOU SO MUCH! People at the office are losing their minds, they can't believe this was an alternative to manually inputting all this data :)

I only had to make a few very small tweeks to get it going. One last kink to work out and it's good to go. If the data in spreadsheet cell A1 is 12345 when the program sends the contents of val to the user input field in the database it converts it to 12345.0 The database uses the literal value and therefore doesn't find the correct record. I've played with the formatting of the cell in Excel but it doesn't seem to matter what I change it to, the decimal and zero always come after the value. I could probably just send a few {backspace} commands but I'm guessing there's a cleaner alternative?
User avatar
FanaticGuru
Posts: 1906
Joined: 30 Sep 2013, 22:25

Re: Creating and Ending a Loop in Using Excel Data

22 Feb 2018, 23:27

CodeFourSDSO wrote:If the data in spreadsheet cell A1 is 12345 when the program sends the contents of val to the user input field in the database it converts it to 12345.0 The database uses the literal value and therefore doesn't find the correct record.
That is a common situation with Excel that it likes to return numbers will trailing zeroes to show the persicion of numbers when getting the value of a cell.

The easest way around this is to get the cell's "text" instead of "value".
val := xlWb.ActiveSheet.Cells(Row, Column).Text

You could rename the variable val to something more appropriate since now you are not really getting the value property but variable name doesn't really matter.

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

Return to “Ask for Help (v1)”

Who is online

Users browsing this forum: garry, mikeyww and 122 guests