Help with my project

[BruceGC]

Hi guys, Autoit looks perfect for what I am trying to do.

I would like to copy data from cells in my excel sheet, each row is one dog, the database I use does not allow the importing of new records without overwriting current files. 

So to add a new dogto the database I click "Add New Dog" copy each cell in the excel row to the same named feilds then click done and around I go again to the next row on excel.

Is Autoit the application to slove this for me? I am looking to be pointed in the right direction so I can learn as I go.

I will attach some photos that might help in explaing this better then I can.

Thanks in advance

Bruce GC

 

 

[Subz]

Welcome to Autoit

It is possible to automate this, you could look at the _Excel_* functions within help file, for example _Excel_RangeRead to copy the data into an array, you can then loop through the array and use something like ControlSend to add the cell info to each field.

Although couldn't you just export your current database append the new data (removing any duplicates) and the re-import the entire table?

[Nine]

I would think it is better to enter data into the database form first as there are many error handling occurring during the completion of the form.  Just before clicking OK, you could have a HotKeySet that will copy all fields from the database form into variables.  If all goes well after OK button, then use another HotKeySet to copy the fields into Excel.  But I may be mistaken, hard to say with so little information.

[BruceGC]

Thanks Subz. If I export the current data then add the new data to it and reload it I lose all the imported photos. This Pedigree data base does not allow access to its tables and the makers of PedPro will not import my CSV file in to their database format *.ped and they have a bug in the lastest version which limits me to20 fields per record. So if I add a new dog I can complete all the fields I require.

I would have made my own database in Fliemaker but working out the formula for the coefficient of inbreeding is F = Σ[(0.5)n(1 + FA) and building that in to my own database all just seemed too hard.

I will try and learn autoit as I can see a load of uses for it.

Thanks Bruce

[BruceGC]

Thanks Nine. I am trying to send the data from excel to the database. I am hoping to just have a script copy from A1 and paste in to name: then caopy from A2 and paste in to Sex: and so on until the end of the form. Then click Ok, then click New Dog then step down 1 line on Excel and start again until it reaches a blank *1 or I could put EOF on the the last row and have the strp stop if *1 = EOF or something along those lines. I will only run this once every month or so but the first import might be about 5000 records.

If you require any more information please just ask.

I am still learning how to "Run" the Pedpro.exe but I will get there.

Thanks in advance for any help

Bruce

[Nine]

Could you run the Au3Info.exe info tool on a few of the fields of the database form ?  We will need this to know if we can use better functions (like ControlSetText for example) than Send to complete the form.  Could you also provide an example of the .xls file that is holding the information ?  With it we will be able to give a framework where you could evolve your project.

[BruceGC]

Thanks Nine I have tried to ues the Info the best that I can and it appears to show nothing more then the title of each screen. see attached screen shots.

I hope this helps somewhat.

Bruce

 

[BruceGC]

On 1/28/2021 at 11:24 PM, Nine said:

I would think it is better to enter data into the database form first as there are many error handling occurring during the completion of the form.  Just before clicking OK, you could have a HotKeySet that will copy all fields from the database form into variables.  If all goes well after OK button, then use another HotKeySet to copy the fields into Excel.  But I may be mistaken, hard to say with so little information.

Nine I think I may have miss lead you. I have the data in an excel sheet and I am wanting to add that data (1 row per record) in to a database. There is a bug within that database which does not allow me to just import a CSV file to create a new database. If I use the import CSV feature as it is, it only allows 25 feilds, not enough for my data. The folks in England who wrote the Database were going to try this for me but seem to have given up.

If I can have Autoit read the sheet as an array and then paste a value from that array in to the fields of the database as a New Dog record. I would be have to use something like a mouse click on a set screen location. I am aware this is slow but the New Dog screen opens in the same location and size everytime.Once I have the first lot of recoreds entered approx 5000 then it would only be updates of 200 per month. 

I think I need to read up on the excel read array and then work out how to step along a row of the array pasting / pulling values from the array until I reach the end of the line / row. then click done then click add New Dog step down one row on the array and paste away again until the end of the row. looping along until I reach the last row.

Thanks for any guidance

 

Bruce

[BruceGC]

On 1/31/2021 at 10:43 PM, Nine said:

Could you run the Au3Info.exe info tool on a few of the fields of the database form ?  We will need this to know if we can use better functions (like ControlSetText for example) than Send to complete the form.  Could you also provide an example of the .xls file that is holding the information ?  With it we will be able to give a framework where you could evolve your project.

This is what I am reading from. To obtain this data I copy the screen in a different database then I have some VBA code to paste what is on the clipboard to the Pedpro Database.

By pasting it in to a excel sheet I canremove duplicates.

 

Thanks Bruce 

pedproimport2.xls

[Nine]

You did not show the control tab of the au3info tool.  Please use the tool over the name field and report control tab.

[BruceGC]

3 hours ago, Nine said:

You did not show the control tab of the au3info tool.  Please use the tool over the name field and report control tab.

The control tab remains blank over the whole New Dog window. The summary pasted below

>>>> Window <<<<
Title:    New Dog
Class:    QWidget
Position:    0, 0
Size:    683, 602
Style:    0x96CC0000
ExStyle:    0x00000500
Handle:    0x00000000001F05F0

>>>> Control <<<<
Class:    
Instance:    
ClassnameNN:    
Name:    
Advanced (Class):    
ID:    
Text:    
Position:    
Size:    
ControlClick Coords:    
Style:    
ExStyle:    
Handle:    

>>>> Mouse <<<<
Position:    462, 301
Cursor ID:    2
Color:    0xFFFFFF

>>>> StatusBar <<<<

>>>> ToolsBar <<<<

>>>> Visible Text <<<<

>>>> Hidden Text <<<<
 

Thanks

[Nine]

Ok, could you run UIASpy from UIAutomation UDF over the same field.  We may resort to use MouseClick and Send, but I would rather make sure it is the only solution available to you before.

Edited February 19, 2021 by Nine

[BruceGC]

Have have found the "Sex" field is a drop down box and I cannot paste in to it. (thats a bugger) 

I have attached what I think you have asked for,

Thanks Bruce

[Nine]

Nope that's not it. It looks like this :

[BruceGC]

is the UIASpy an exe file? I can find somewhere? I am finding it hard to locate help on this.

Thanks

Bruce

Edited February 21, 2021 by BruceGC

[BruceGC]

Not sure if this will help.

 

[Nine]

No, it is an au3 tool.  Download the 7z file and extract the tool

 

[BruceGC]

Is this what you are after?

Thanks Bruce

[Nine]

Yes.  It seems it can be reach with UIAutomation.  Now try this with one of the drop down (to see if all options are accessible).  Since I cannot run PedPro, you will have to study the examples provided with the UDF and then experiment on your own.  There is a learning curve, but with one step at a time, I believe you will be able to automate your project.

[BruceGC]

Pedpro has a free limited version here,    http://www.tenset.co.uk/pedpro/download_windows.html but I am happy to give it a crack.

Not sure what you mean "study the examples provided with the UDF" which UDF.

If I save the excel sheet as an Aray can I call A:3 for example for sex  to say If A:3 = "Female" press "F" and if A:3 = "Male" press "M" else skip

It is a bit of a steep learning curve but I appreciate your help.

Cheers Bruce