Jump to content
Sign in to follow this  
Blueman

Using excel sheet to find values

Recommended Posts

Blueman

Hey Guys,

I was wondering if you can help me with my excel problem.

But first i have to know if it is even possible because i don't know how far AutoIT goes in this.

I have a application where i need to fill out some values, these values are stored in a excel file and now i have to manually add them into the program. If it is possible i would like to do this automatically with some scripting.

In my program i have several rows on each row i have four coloms, i will show you;

Number | name | something else | quantity

Only the first colom "Number" is filled the rest is empty. I can navigate through the program using the [TAB] and [ARROW] keys.

In my Excel sheet i have also several rows with coloms, i will show you;

Number | Details | Base | count

Now i would like to set my cursor on the first row in the first colom (from the program) and start the script.

- The script will copy the Number to the clipboard

- Search in the excel document for the correct row

- And then copy the count from that row to the clipboard

- And final paste the count into the quantity colom

And repeat this for the following rows (maybe by pressing the enter button every time a row is done).

Do you guys think this is possible and if so can you help me get started?

I have experimented with some _ExcelReadArray scripts but didn't work.

Thanks :)

Edit:

Just starting with a simple IF statement but this didn't work. When i copy something from NOTEPAD to the clipboard it works but when i copy a excel cell (Just through crtl+c) to the clipboard it just keep saying this is a string,.. what am i doing wrong?

Local $bak = ClipGet()
If StringIsDigit($bak) Then
     MsgBox(4096, "", "Value contains digits")


Else
     MsgBox(4096, "", "Value is a string.")

Send("{LEFT}")
Send("{CTRLDOWN}{c}{CTRLUP}")

EndIf

Fixed it! // I just used the StringRegExp function and now it is working

Edited by Blueman

Share this post


Link to post
Share on other sites
water

AutoIt has a builtin Excel UDF (User Defined Functions Library). Check function _ExcelReadSheetToArray to read the whole sheet into an array and then locate and process the needed row.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
MuchTex

Building upon what water said, use _ExcelBookOpen and use that window handle when doing _ExcelReadSheetToArray. I'm assuming the problem you're having is just that.

Share this post


Link to post
Share on other sites
water

Or you could have a look at my ExcelEX UDF (still an early Alpha version!). It has a search/find function.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
Blueman

Guys,

It worked thanks, but i want to make it some faster by directly getting the data from my WEB MySQL Database.

So i have a website with a MySQL database and want to let the script search directly into the database without exporting it to Excel,...

Is this possible?

Because i have tried to do so, with this UDF;

But it keeps saying that it cannot make a connection to my server on several adresses; Localhost, IP from my webserver here on LAN, IP from my webserver on WAN, just the web-adress www.mydomain.nl

It just doesn't work!

What am i doiing wrong ? :ermm:

Thanks

EDIT

Guys, don't worry (Be happy? ^^).

I got it working!

My router was the problem, he didn't transfered the correct MySQL port to the server,. changed it and it works fine!

But what i want to know is, how dangerous is it to open your database to the WWW?

Edited by Blueman

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×