Help on a Project

[JennMaughan]

Hi!

I'm going a little extra out of my way and learning how to use AutoIt. I was previously using a Macro Recorder but it cant do what I really need it to do.   I can follow the Help Contents really well, but many times just need to look at examples of script to see exactly what the smaller things look like.  I'm very new at scripting.  Hoping you can recommend a really good noob website or maybe be willing to answer questions if its not too much.

For Example:

In the following code, I want to open an Excel file that is on my desktop.  (and eventually turn a specific sheet into an array).  

I'm not sure what the File Path should look like.  Under Properties of the excel spreadsheet, the name listed is ‪C:\Users\Jenn\Desktop\GOW reds.xlsx

 

#Include <Excel.au3>
$oExcel = _ExcelBookOpen($sFilePath[, $fVisible = 1[, $fReadOnly = False[, $sPassword = ""[, $sWritePassword = ""]]]])

Parameters

$sFilePath Path and filename of the file to be opened 
$fVisible Flag, whether to show or hide the workbook (0=not visible, 1=visible) (default=1) 
$fReadOnly Flag, whether to open the workbook as read-only (True or False) (default=False) 
$sPassword The password that was used to read-protect the workbook, if any (default is none) 
$sWritePassword The password that was used to write-protect the workbook, if any (default is none)

 

[RaiNote]

Easiest Way for the FilePath is

$sFilePath = @DesktopDir & "\Excelfile.xlsx"

but it could also be:

$sFilePath = "C:\Users\USERNAME\Desktop\Excelfile.xlsx"

but i think you could also left the File Name just like

Excelfile

but I'm not sure about that ^^

Edited May 30, 2015 by RaiNote

[water]

IIRC you have to specify path AND filename.  

[Melba23]

JennMaughan,

Welcome to the AutoIt forums.

The FilePath parameter is exactly what is says in the text you quoted:

Path and filename of the file to be opened

So in the case you mention it would be:

$oExcel = _ExcelBookOpen("C:\Users\Jenn\Desktop\GOW reds.xlsx")

Note the surrounding quotes as the path is a literal string.

M23

[JennMaughan]

Thanks RaiNote!

Looks like there are MANY ways to do any one thing.  What is the easiest way to take information in a closed excel file and have that information available to be typed out during a loop.  I would have two columns and and changing amount of rows.  

[RaiNote]

I'm not sure about to do this because I can't use currently Excel on my Laptop and I'm not using AutoIt in anyways with Excel but i gonna look a bit a round^-^.
 

[water]

Please have a look at:
_Excel_Open
_Excel_BookOpen
_Excel_RangeRead

[RaiNote]

Water The Helpfiles are a bit complicated so it's hard to understand.

#Include <Excel.au3>
$sFilePath = @DesktopDir & "\GOW reds.xlsx"
$oExcel = _Excel_BookOpen(_Excel_Open(),$sFilePath)
$rExcel = _Excel_RangeRead($oExcel,Default,"A10")
ConsoleWrite($rExcel)
_Excel_Close($oExcel)

I think this should anyway work but I'm not sure.

[water]

Why do you think the help files are hard to understand?
Each function has one or more examples to explain how it works. Plus there is a wiki with more information.

[water]

JennMaughan,
When you want to read two columns, do you want to read all rows containing data or just a defined number of rows?

Edited May 30, 2015 by water

[RaiNote]

Then I just say I hate AutoIt+Excel because then I can't understand it but i didn't have seen in the Examples of ReadRange the Function >.<.

 

[water]

Just scroll down in the help file for function _Excel_RangeRead.

[RaiNote]

Nah don't want to right now ^^ gonna look at when i really need it and the code i wrote above should help in a way. I'm off for now in this thread.

[JennMaughan]

JennMaughan,
When you want to read two columns, do you want to read all rows containing data or just a defined number of rows?

Hi Water,

I'll be continuously adding entries and deleting so I need to read all the rows containing the data.  Extraneous information will be on different sheets - just to keep the one I'm reading clean.  Each will use 2 columns A:B and probably use a UBound - 1 in my Loop because there isnt a specific number of rows?  Just need to use whatever information is there when the file is opened. 

The ultimate goal is to have the information from the excel sheet available so I can have the information of  A1 typed out in one box (I'll use a mouse move and click to activate  the entry box) and B1 in a second box. (Then finishes the rest of the script in the loop)  On the second repeat of the loop it will type A2 and B2, third etc.  until it runs out of rows.

I went thru each of the Excel options in the help file.  It seemed like Excel RangeRead fit the most because it has the option of writing to an Array which is required for the loop?  Learning so fast, but not fast enough!

 

And thank you RaiNote, that was incredibly helpful..

 

Jenn Maughan 

[232showtime]

Water The Helpfiles are a bit complicated so it's hard to understand.

​I learned excel automation using excel help files and water's help aswell.

 

 

Edited May 31, 2015 by 232showtime

[water]

Using

$aResult = _Excel_RangeRead($oWorkbook, <insert number/name of the sheet to be read here>)

will read all cells with data into a two dimensional array.

[JennMaughan]

Ok, this works.   How do I reference the values that are now inside the 2 dimension array?  (Basically the info that was inside Excel spreadsheet A1 vs. what is in B1?)  They will be required at two different points in the script.  Is an _ArrayExtract needed? 

Thanks!

[water]

You can directly access the values in the array.
Cell A1 corresponds to $aResult[0][0] and cell B1 to $aResult[0][1], cell A5 corresponds to $aResult[4][0] and cell B5 to $aResult[4][1].

[JennMaughan]

Perfect!  Thanks

[JennMaughan]

Next Stage of Project.

In my former scripting using MacroRecorder, I am use to taking a small image (of a small defined area) of the upcoming changing screen and having the script delay until it recognizes the image being present before continuing with the script.  I am not sure what the best way to do this In AutoIt.  From what I read, I could use a pixelSearch (which I'm not sure how to obtain the pixel code that I am waiting for) or I have also read about a _ImageSearchArea (from a few years ago) but cannot find it in the help files.

 Pixel comparison hasn't worked really well for me before, and might work better if I allow more variation in shade.  What has worked well for me in the past has been Image comparing.  I'm guessing AutoIt works much better , which is one of the reasons I'm learning how to script all over again.  Takes alot of patience!

Advice?