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.)
Creating and Ending a Loop in Using Excel Data
-
- Posts: 7
- Joined: 09 Feb 2018, 12:27
- Contact:
Re: Creating and Ending a Loop in Using Excel Data
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
Windows 10 Pro 64 bit - Autohotkey v1.1.30.01 64-bit Unicode
-
- Posts: 7
- Joined: 09 Feb 2018, 12:27
- Contact:
Re: Creating and Ending a Loop in Using Excel Data
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!
Cheers!
-
- Posts: 7
- Joined: 09 Feb 2018, 12:27
- Contact:
Re: Creating and Ending a Loop in Using Excel Data
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
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
- FanaticGuru
- Posts: 1906
- Joined: 30 Sep 2013, 22:25
Re: Creating and Ending a Loop in Using Excel Data
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
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
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
-
- Posts: 7
- Joined: 09 Feb 2018, 12:27
- Contact:
Re: Creating and Ending a Loop in Using Excel Data
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 responseFanaticGuru 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
- FanaticGuru
- Posts: 1906
- Joined: 30 Sep 2013, 22:25
Re: Creating and Ending a Loop in Using Excel Data
Example:
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
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
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
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
- FanaticGuru
- Posts: 1906
- Joined: 30 Sep 2013, 22:25
Re: Creating and Ending a Loop in Using Excel Data
Example of Opening Excel Worksheet.
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
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
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
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
-
- Posts: 7
- Joined: 09 Feb 2018, 12:27
- Contact:
Re: Creating and Ending a Loop using Excel Data
[/code][/code]
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...
Hi FG!FanaticGuru wrote:Example: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.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
No need to Alt-Tab.
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
-
- Posts: 7
- Joined: 09 Feb 2018, 12:27
- Contact:
Re: Creating and Ending a Loop in Using Excel Data
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)
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
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.
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
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
- FanaticGuru
- Posts: 1906
- Joined: 30 Sep 2013, 22:25
Re: Creating and Ending a Loop in Using Excel Data
Below is a more complete script using the opening of an Excel file.
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
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
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
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
-
- Posts: 7
- Joined: 09 Feb 2018, 12:27
- Contact:
Re: Creating and Ending a Loop in Using Excel Data
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?
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?
- FanaticGuru
- Posts: 1906
- Joined: 30 Sep 2013, 22:25
Re: Creating and Ending a Loop in Using Excel Data
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.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.
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
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
Who is online
Users browsing this forum: anogoya, Descolada, Mannaia666, sanmaodo and 148 guests