I am fairly new to AutoHotKey and I want to create a script to import data from an excel spreadsheet into a program to automatically add device serials into a database.
I have two programs the script will be interacting with, Mixcrosoft Excel and another private program. The private program has to have each device entered in one at a time in a certain input field and then processed with Ctrl+p and then click Yes to enter in the next serial. Alternatively it has the option to enter in sequential serials if I have a batch that the serials are sequential.
I attached an image of the program I need to import data into. Again, it has to be one at a time from an excel file that has data in the A column.
Any help would be appreciated. Not quite sure on where to start. Let me know if more information is required.
Import data from Excel into another program.
-
- Posts: 9
- Joined: 19 Jun 2017, 08:52
Import data from Excel into another program.
- Attachments
-
- equip.jpg (31 KiB) Viewed 5316 times
Re: Import data from Excel into another program.
If you right click on the AutoHotkey tray icon you can start the Window spy, or if you don't have AutoHotkey running look for the Spy in the AutoHotkey folder. What does the Spy say when you hover your mouse of the controls of your target program?
Perhaps you can use https://autohotkey.com/docs/commands/ControlSetText.htm or ControlSend ^v to paste if the program accepts it. Otherwise just a regular paste (send ^v) and Send {tab} to move the next control might work. Reading Excel can either be done via copy/paste (crude manner) or via COM (should be very reliable).
So try to figure out if your target application can accept ControlSetText and/or ControlSend, if so you're half way there.
Tutorial reading Excel + COM https://autohotkey.com/board/topic/6903 ... for-excel/
Perhaps you can use https://autohotkey.com/docs/commands/ControlSetText.htm or ControlSend ^v to paste if the program accepts it. Otherwise just a regular paste (send ^v) and Send {tab} to move the next control might work. Reading Excel can either be done via copy/paste (crude manner) or via COM (should be very reliable).
So try to figure out if your target application can accept ControlSetText and/or ControlSend, if so you're half way there.
Tutorial reading Excel + COM https://autohotkey.com/board/topic/6903 ... for-excel/
-
- Posts: 302
- Joined: 05 Feb 2017, 00:03
Re: Import data from Excel into another program.
Without knowing more about your private program I can't really help with that part, but this should loop through all cells in the A column until a blank cell is found and msgbox its value:
Code: Select all
F1:: ; Arbitrary HotKey for testing, code can be put in a label or function instead.
FilePath := "C:\Users\user\Desktop\SerialNumbers.xlsx" ; file path
xl := ComObjCreate("Excel.Application") ; access Workbook
oWorkbook := xl.Workbooks.Open(FilePath,0,0)
aRange := xl.Range("A:A") ; Get the Range of A column
For cell in aRange
{
IF (!cell.Text) ; Break loop on first empty cell in Column A
Break
msgBox, % cell.Text
}
oWorkbook.Close()
xl.Quit()
xl := ""
Return
-
- Posts: 9
- Joined: 19 Jun 2017, 08:52
Re: Import data from Excel into another program.
Will that copy each cell individually and then paste it into the other program?
The other program has the serial inputted into it, then I click CTRL + P to process it into the inventory, then a new window appears asking if I would like to input more devices, I then click left once and then space to select YES. This allows me to input the next serial from the Excel List.
Hope that helps clarify. Thank you for your response.
The other program has the serial inputted into it, then I click CTRL + P to process it into the inventory, then a new window appears asking if I would like to input more devices, I then click left once and then space to select YES. This allows me to input the next serial from the Excel List.
Hope that helps clarify. Thank you for your response.
-
- Posts: 302
- Joined: 05 Feb 2017, 00:03
Re: Import data from Excel into another program.
No, the code I provided only shows the values in the A column going down in order in a MsgBox, and was really only to show how to do that part.
Can you use the 'Window Spy' program that is included with an AHK install, and see if you can get information about the windows and edit controls in your private program? If you want everything automated, unless someone else knows another way, I would need the ClassNN of the edit you want the serial numbers sent to, and the window's name/class that holds the control.
Can you use the 'Window Spy' program that is included with an AHK install, and see if you can get information about the windows and edit controls in your private program? If you want everything automated, unless someone else knows another way, I would need the ClassNN of the edit you want the serial numbers sent to, and the window's name/class that holds the control.
-
- Posts: 9
- Joined: 19 Jun 2017, 08:52
Re: Import data from Excel into another program.
>>>>>>>>>>( Window Title & Class )<<<<<<<<<<<
Add Equipment
ahk_class #32770
>>>>>>>>>>>>( Mouse Position )<<<<<<<<<<<<<
On Screen: 775, 298 (less often used)
In Active Window: 745, 253
>>>>>>>>>( Now Under Mouse Cursor )<<<<<<<<
Color: 0x87CEF0 (Blue=87 Green=CE Red=F0)
>>>>>>>>>>( Active Window Position )<<<<<<<<<<
left: 30 top: 45 width: 626 height: 462
>>>>>>>>>>>( Status Bar Text )<<<<<<<<<<
>>>>>>>>>>>( Visible Window Text )<<<<<<<<<<<
Equipment Type:
Program:
Vendor:
Vintage:
Voltage:
Radio Address:
&Validate
Serial Number
From:
To:
Status
&Active
&Failed
&Retired
Site
&Stock
&Installed
Ven&dor
&Lost
Location:
Dates
Purchased:
Received:
Warranty:
Retired:
Task List
Signal Test:
Install Details...
Vie&w LEDs...
>>>>>>>>>>>( Hidden Window Text )<<<<<<<<<<<
>>>>( TitleMatchMode=slow Visible Text )<<<<
/ /
/ /
/ /
/ /
>>>>( TitleMatchMode=slow Hidden Text )<<<<
This is the windows spy info from the program I need to enter it into. The device serial goes into the "From" Input. I click CTRL+P and a window pops up where I have to press "Left" then "Space" to get back to be able to enter next serial into the "From" input section.
Add Equipment
ahk_class #32770
>>>>>>>>>>>>( Mouse Position )<<<<<<<<<<<<<
On Screen: 775, 298 (less often used)
In Active Window: 745, 253
>>>>>>>>>( Now Under Mouse Cursor )<<<<<<<<
Color: 0x87CEF0 (Blue=87 Green=CE Red=F0)
>>>>>>>>>>( Active Window Position )<<<<<<<<<<
left: 30 top: 45 width: 626 height: 462
>>>>>>>>>>>( Status Bar Text )<<<<<<<<<<
>>>>>>>>>>>( Visible Window Text )<<<<<<<<<<<
Equipment Type:
Program:
Vendor:
Vintage:
Voltage:
Radio Address:
&Validate
Serial Number
From:
To:
Status
&Active
&Failed
&Retired
Site
&Stock
&Installed
Ven&dor
&Lost
Location:
Dates
Purchased:
Received:
Warranty:
Retired:
Task List
Signal Test:
Install Details...
Vie&w LEDs...
>>>>>>>>>>>( Hidden Window Text )<<<<<<<<<<<
>>>>( TitleMatchMode=slow Visible Text )<<<<
/ /
/ /
/ /
/ /
>>>>( TitleMatchMode=slow Hidden Text )<<<<
This is the windows spy info from the program I need to enter it into. The device serial goes into the "From" Input. I click CTRL+P and a window pops up where I have to press "Left" then "Space" to get back to be able to enter next serial into the "From" input section.
-
- Posts: 302
- Joined: 05 Feb 2017, 00:03
Re: Import data from Excel into another program.
Sorry, I should've been more specific as that is great for getting the window title, but not the edit box directly. If you hover over the 'From' Edit box, does it have anything listed for ClassNN in the 'Control Under Mouse Position' box?
I'm hoping it will say something like Edit7.
Edit: Sorry just saw your other reply, OK let me try something and I'll have you test it...
I'm hoping it will say something like Edit7.
Edit: Sorry just saw your other reply, OK let me try something and I'll have you test it...
Last edited by Nightwolf85 on 19 Jun 2017, 10:58, edited 2 times in total.
-
- Posts: 9
- Joined: 19 Jun 2017, 08:52
Re: Import data from Excel into another program.
The ClassNMM for the FROM: Input field is WIDNumFld2
-
- Posts: 302
- Joined: 05 Feb 2017, 00:03
Re: Import data from Excel into another program.
You can try this, but know that i have no way of testing myself...
Also note I have no checks for if the window exists or anything, if you are going to use this regularly and it works I would add that in to make it more robust.
Code: Select all
F1:: ; Still an arbitrary hotkey to activate.
FilePath := "C:\Users\User\Desktop\SerialNumbers.xlsx" ; file path
xl := ComObjCreate("Excel.Application")
oWorkbook := xl.Workbooks.Open(FilePath,0,0) ; access Workbook
aRange := xl.Range("A:A") ; Get the Range of A column
For cell in aRange
{
IF (!cell.Text) ; Break loop on first empty cell in Column A
Break
ControlSetText, WIDNumFld2, % cell.Text, Add Equipment ; Set the text of WIDNumFld2 in Add Equipment Window to the cell's text
ControlSend, , ^p, Add Equipment ; Send Ctrl-P to the top control on the Add Equipment window
Sleep 200 ; Trying a sleep incase it takes a bit for the window to show up
ControlSend, , {Left}{Space}, Add Equipment ; Send Left Space to the top control on the Add Equipment window
; Loop to the next cell...
}
oWorkbook.Close()
xl.Quit()
xl := ""
Return
-
- Posts: 9
- Joined: 19 Jun 2017, 08:52
Re: Import data from Excel into another program.
Thanks! I will test it out and report back. Thank you for your time and work.
-
- Posts: 9
- Joined: 19 Jun 2017, 08:52
Re: Import data from Excel into another program.
Can't test it out yet but by looking at the code, this piece I feel may be incorrect:
ControlSend, , {Left}{Space}, Add Equipment ; Send Left Space to the top control on the Add Equipment window
When this action is prompted, a new window comes up and I am assuming it will have a new window title. I should replace "Add Equipment" with the new window title to correct?
ControlSend, , {Left}{Space}, Add Equipment ; Send Left Space to the top control on the Add Equipment window
When this action is prompted, a new window comes up and I am assuming it will have a new window title. I should replace "Add Equipment" with the new window title to correct?
-
- Posts: 302
- Joined: 05 Feb 2017, 00:03
Re: Import data from Excel into another program.
That is definitely possible as I only have the information you gave me, and if it does have a different window name, then just change that line... I was going on the assumption that the main program window is "Add Equipment" and if so that a MsgBox inside that window should still receive the keys I believe.
-
- Posts: 9
- Joined: 19 Jun 2017, 08:52
Re: Import data from Excel into another program.
Ok thank you, Will update after I am able to test.
-
- Posts: 9
- Joined: 19 Jun 2017, 08:52
Re: Import data from Excel into another program.
I tested out the script. It reads and pastes the serial into the input field but it does not seem to process it with Ctrl+P. Additionally, it does not delete the previous serial that was inputted into the field.
Re: Import data from Excel into another program.
I know we all love AHK + COM, but what happened to that ol' dam reliable Excel CSV (export) file option?
And btw, that "private" app, is it one I would be able to buy as well if requested. What's its so secret name?
I take that as ... it offers a bulk load option ?!! If yes, why not create/process such a "batch" file and do the whole thing via the backend? What's the exact file specification?Alternatively it has the option to enter in sequential serials if I have a batch that the serials are sequential.
And btw, that "private" app, is it one I would be able to buy as well if requested. What's its so secret name?
-
- Posts: 9
- Joined: 19 Jun 2017, 08:52
Re: Import data from Excel into another program.
The private app is a proprietary software that is not available for sale to the public.
I receive a list that can be in sequence or out of sequence. The list can be up to 500 serials long. If there is a sequential batch of serials, then the program offers me the ability to enter in a sequential batch.
For the sake of the process I am not really worries about utilizing the sequential batch processing portion as I would just like the script to input the serial one by one into the program on it's own.
It seems that the script that nightwolf provided does not send the "CTRL + P" properly for some reason, and additionally it does not erase the previously entered serial before entering the new one.
There is no file extension, I am taking a list from an excel column and manually entering in each serial into the program.
I receive a list that can be in sequence or out of sequence. The list can be up to 500 serials long. If there is a sequential batch of serials, then the program offers me the ability to enter in a sequential batch.
For the sake of the process I am not really worries about utilizing the sequential batch processing portion as I would just like the script to input the serial one by one into the program on it's own.
It seems that the script that nightwolf provided does not send the "CTRL + P" properly for some reason, and additionally it does not erase the previously entered serial before entering the new one.
There is no file extension, I am taking a list from an excel column and manually entering in each serial into the program.
Re: Import data from Excel into another program.
https://autohotkey.com//boards/viewtopic.php?f=7&t=8978
i asked a similar question and Mr. Kon gave a great reply.
i'm working a lot with lists in excel and frankly, if he did not give that reply i probably would have been stuck till today.
i asked a similar question and Mr. Kon gave a great reply.
i'm working a lot with lists in excel and frankly, if he did not give that reply i probably would have been stuck till today.
Re: Import data from Excel into another program.
If I understand correctly, you are wanting to go
down
the
list.
Try this!
Edit: Added hotkey and suggestion for loop.
down
the
list.
Try this!
Code: Select all
#SingleInstance,Force
XL := ComObjActive("Excel.Application")
F2:: ;or just add a loop, until (var = "") :)
var := XL.ActiveCell.Value ;capture the current cell's data.
XL.ActiveCell.Offset(1,0).Select ;Select the next in the row, example if started at A1, it would select A2 for next time.
sleep, 5
ControlSetText, WIDNumFld2, %var%, Add Equipment ;ControlSetText should reset the field's context.
sleep, 25
WinActivate, Add Equipment ;***try this if control send isn't working
SendInput, ^p ;***
;Alternately if there is any menu items that can do this or buttons try WinMenuSelect. For button, ControlClick or Control,Check..* for improved reliability
Sleep 200 ; Trying a sleep incase it takes a bit for the window to show up
ControlSend, , {Left}{Space}, Add Equipment ;Is there any buttons you can do a click on instead?
return
Who is online
Users browsing this forum: No registered users and 210 guests