Import data from Excel into another program.

Get help with using AutoHotkey (v1.1 and older) and its commands and hotkeys
FFVIIVince10
Posts: 9
Joined: 19 Jun 2017, 08:52

Import data from Excel into another program.

19 Jun 2017, 09:22

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.
Attachments
equip.jpg
equip.jpg (31 KiB) Viewed 5316 times
Guest

Re: Import data from Excel into another program.

19 Jun 2017, 09:38

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/
Nightwolf85
Posts: 302
Joined: 05 Feb 2017, 00:03

Re: Import data from Excel into another program.

19 Jun 2017, 10:04

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
FFVIIVince10
Posts: 9
Joined: 19 Jun 2017, 08:52

Re: Import data from Excel into another program.

19 Jun 2017, 10:36

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.
Nightwolf85
Posts: 302
Joined: 05 Feb 2017, 00:03

Re: Import data from Excel into another program.

19 Jun 2017, 10:46

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.
FFVIIVince10
Posts: 9
Joined: 19 Jun 2017, 08:52

Re: Import data from Excel into another program.

19 Jun 2017, 10:53

>>>>>>>>>>( 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.
Nightwolf85
Posts: 302
Joined: 05 Feb 2017, 00:03

Re: Import data from Excel into another program.

19 Jun 2017, 10:57

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...
Last edited by Nightwolf85 on 19 Jun 2017, 10:58, edited 2 times in total.
FFVIIVince10
Posts: 9
Joined: 19 Jun 2017, 08:52

Re: Import data from Excel into another program.

19 Jun 2017, 10:57

The ClassNMM for the FROM: Input field is WIDNumFld2
Nightwolf85
Posts: 302
Joined: 05 Feb 2017, 00:03

Re: Import data from Excel into another program.

19 Jun 2017, 11:03

You can try this, but know that i have no way of testing myself...

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
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.
FFVIIVince10
Posts: 9
Joined: 19 Jun 2017, 08:52

Re: Import data from Excel into another program.

19 Jun 2017, 11:11

Thanks! I will test it out and report back. Thank you for your time and work.
FFVIIVince10
Posts: 9
Joined: 19 Jun 2017, 08:52

Re: Import data from Excel into another program.

19 Jun 2017, 11:58

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?
Nightwolf85
Posts: 302
Joined: 05 Feb 2017, 00:03

Re: Import data from Excel into another program.

19 Jun 2017, 12:18

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.
FFVIIVince10
Posts: 9
Joined: 19 Jun 2017, 08:52

Re: Import data from Excel into another program.

19 Jun 2017, 12:27

Ok thank you, Will update after I am able to test.
FFVIIVince10
Posts: 9
Joined: 19 Jun 2017, 08:52

Re: Import data from Excel into another program.

27 Jun 2017, 06:25

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.
BoBo
Posts: 6564
Joined: 13 May 2014, 17:15

Re: Import data from Excel into another program.

27 Jun 2017, 08:57

I know we all love AHK + COM, but what happened to that ol' dam reliable Excel CSV (export) file option?
Alternatively it has the option to enter in sequential serials if I have a batch that the serials are sequential.
I take that as ... it offers a bulk load option :o ?!! If yes, why not create/process such a "batch" file and do the whole thing via the backend? What's the exact file specification?

And btw, that "private" app, is it one I would be able to buy as well if requested. What's its so secret name?
FFVIIVince10
Posts: 9
Joined: 19 Jun 2017, 08:52

Re: Import data from Excel into another program.

27 Jun 2017, 13:29

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.
okl

Re: Import data from Excel into another program.

19 Aug 2017, 00:29

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.
Vh_
Posts: 203
Joined: 17 Mar 2017, 22:06

Re: Import data from Excel into another program.

19 Aug 2017, 16:19

If I understand correctly, you are wanting to go
down
the
list.

Try this! :thumbup:

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
Edit: Added hotkey and suggestion for loop.

Return to “Ask for Help (v1)”

Who is online

Users browsing this forum: No registered users and 210 guests