Jump to content
JennMaughan

Help on a Project

Recommended Posts

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)

 

Share this post


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

  • C++/AutoIt/OpenGL Easy Coder
  • I will be Kind to you and try to help you
  • till what you want isn't against the Forum
  • Rules~

 

Share this post


Link to post
Share on other sites
water

IIRC you have to specify path AND filename.  


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - 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
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


Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


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

Share this post


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


  • C++/AutoIt/OpenGL Easy Coder
  • I will be Kind to you and try to help you
  • till what you want isn't against the Forum
  • Rules~

 

Share this post


Link to post
Share on other sites
water

Please have a look at:
_Excel_Open
_Excel_BookOpen
_Excel_RangeRead


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - 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
RaiNote

Water The Helpfiles are a bit complicated :o 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.


  • C++/AutoIt/OpenGL Easy Coder
  • I will be Kind to you and try to help you
  • till what you want isn't against the Forum
  • Rules~

 

Share this post


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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - 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
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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - 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
RaiNote

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

 


  • C++/AutoIt/OpenGL Easy Coder
  • I will be Kind to you and try to help you
  • till what you want isn't against the Forum
  • Rules~

 

Share this post


Link to post
Share on other sites
water

Just scroll down in the help file for function _Excel_RangeRead.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - 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
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.


  • C++/AutoIt/OpenGL Easy Coder
  • I will be Kind to you and try to help you
  • till what you want isn't against the Forum
  • Rules~

 

Share this post


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

Share this post


Link to post
Share on other sites
232showtime

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

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

 

 

Edited by 232showtime

ill get to that... i still need to learn and understand a lot of codes graduated.gif

Correct answer, learn to walk before you take on that marathon.

Share this post


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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - 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
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!

Share this post


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

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - 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
JennMaughan

Perfect!  Thanks

Share this post


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

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

×