AutoIt and Excel, clarify for a relative newbie please

[GuitarBehindTheBar]

Hi Guys,

Okay so I've been working on my first large program and I've managed to self-teach myself most of what I've needed so far through the help files and wiki. (Impressive I know haha) I have finally hit a speedbump and while I'm sure the information is there somewhere I am having a lot of trouble trying to find the specific information for me to complete what seems to be a pretty simple task.

I have written a program that takes user input data and writes up a quotation which is then emailed through outlook. I now want to take this program to the next level and really limit the things the user has to do. What I need the program to do is access the pricelist which is saved as an excel spreadsheet, find user input data in column A, and take the price from the cell immediately to the right (in the B Column) storing that price temporarily as a variable so I can return it as part of the mathematical equation in the quote.

I really hope this makes sense and if somebody could even direct me to the right place to look for the information (Excel UDF doesn't seem to explain this to me, I've even looked at maybe converting the excel pricelist to an array but I can't find a way to do it that way either.) 

Any help would be greatly appreciated.

Regards,

Frustrated Newbie

[water]

Welcome to AutoIt and the forum!

First question: Which version of AutoIt do you run? There have been big changes starting with 3.3.10.0

[GuitarBehindTheBar]

Hi MVP Thanks for the warm welcome I think I updated a few months back, but I like to keep on top of things so if we assume I have the latest and if it turns out I don't I will download the newest one. Sound reasonable? hehe

[GuitarBehindTheBar]

Also Sorry Water Not only is this my first time on this forum, this is my first time on ANY forum so I misread what your name was.

[water]

I just re-read your first post. You explain what you try to do but there is not question.

So my question is: What have you tried (coded) so far?

[GuitarBehindTheBar]

Sorry! To be truthful I think I do things differently to most people, as I said self taught so I'm sure I've developed some bad habits.

Typically when I'm coding I spend a lot of time thinking first. About what I need it to do. Then I sit down (Usually with a packet of cigarettes and a bottle of Gentleman Jack) and nut out the how. What particular steps I'll need to take in my scriptwriting to achieve the desired result. Then I use my current knowledge to fill in (in my head) roughly how it will be written, so I can go on to see the blanks in my knowledge I will need to fill to be successful. Then I do the reading and learn how. When I've got the idea thoroughly nutted out I'll sit down and write it.

So basically, for now I haven't actually tried anything because I can't get my head around which function I'd be looking at.

Hope that makes sense. 

[water]

That's fine. Most of the time I use this approach myself.

To do what you want to do I suggest the latest AutoIt version which comes with a completely rewritten Excel UDF. Use function _Excel_RangeFind to look for the value in column A. When found the address of the cell is being returned. Then use Range and Offset to retrieve the value of the cell right to the result.

If you like I can provide an example.

And to send a mail using Outlook there is the OutlookEX UDF available.

[GuitarBehindTheBar]

Okay water, I had a better look through the Excel UDF with your guidance (I've already written a script to automate outlook to the extent I need it automated without the Outlook UDF - The program effectively writes quotations already but at the moment it's all based on user input information, I'm going to revamp the coding for this when I understand more about the Outlook UDF but for the time being it serves my purposes).

I think I have a good understanding of function _Excel_RangeFind and thank you very much for that it's exactly what I needed for the first part!

If you could provide an example (or the link to the wiki entry that will have the info) for where you talk about using range and offset to find the next cell. This part I do not understand yet.

Edited August 18, 2014 by GuitarBehindTheBar

[water]

Example:

Search in column "A" for "Doe, Jane". Grab the returned cell address and create a Range object. Offset the Range by 1 column to the right and return the value of the new Range.

#include <Excel.au3>
#include <MsgBoxConstants.au3>

Global $oAppl = _Excel_Open(False)
If @error Then Exit MsgBox(16, "Excel UDF", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

Global $sWorkbook = @ScriptDir & "\Excel test.xlsx"
Global $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF", "Error opening '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Global $aResult = _Excel_RangeFind($oWorkbook, "Doe, Jane", "A:A") ; Search for "Doe, jane" in column A
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF", "Nothing found in '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Else
    $iValue = $oWorkbook.ActiveSheet.Range($aResult[0][2]).Offset(Default, 1).Value ; Create a Range object out of the cell address, offset it by one column and return the value
    MsgBox($MB_SYSTEMMODAL, "Excel UDF", "Price for the found user: " & $iValue)
EndIf

_Excel_Close($oAppl)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF", "Error closing the Excel application." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

[GuitarBehindTheBar]

And like that the walls came crashing down and the people could see to the ends of the horizon once more...

Thankyou so very much! You have taught me so much in such a short time I am eternally grateful to you

[water]