Jump to content
Sign in to follow this  
GuitarBehindTheBar

AutoIt and Excel, clarify for a relative newbie please

Recommended Posts

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 :)

Share this post


Link to post
Share on other sites
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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
GuitarBehindTheBar

Also Sorry Water :P 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.

Share this post


Link to post
Share on other sites
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?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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. 

Share this post


Link to post
Share on other sites
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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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 by GuitarBehindTheBar

Share this post


Link to post
Share on other sites
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)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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 :)

Share this post


Link to post
Share on other sites
water
:)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

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  

×