MrMohican1989

AUTOIT - Excel VBA commands like ...

4 posts in this topic

Good evening everybody.

I'm pretty fine with VBA and can automate most of the the things I need while I work between Excel file but,

I need to get value that are stored in lets say 250 cells in the first column of and Excel file, get the value of the first cell and then activate a software where I need to insert that value click some button and so on.

I want would liketo do something like that :
 

#include <Excel.au3>
$ExcelObj=objget("","Excel.Application")
for $element in $excelobj.workbooks
    msgbox(0,"Test,$element.name)
next
$excelobj.workbooks("MyExcelfilename.xls").activate

the for loop is just to check if am doing things right as I am really newbie to this.
The name of my workbook is "Utenze Comune Amatrice.xls"

I would like to bring  the Excel file in top position like I would do working manually, then set a range like i do in vba something like

$MyRange= $Excelobj.workbooks("Utenze Comune Amatrice.xls").sheets(1).range("A1:A" & range("A1").end(Xldown).row
for $cells in $myrange
    $stringtouse=$cells
    other stuff to enter the software and start doing the automatism i need
next

Any suggestion bout this ? I would like to know if it's possibile to work vba like on autoit3 or how can i put the file i need on top of other by referring by just it's name and not Others things that autoitwindow info check out.

Share this post


Link to post
Share on other sites



Welcome to AutoIt and the forum!

With the Excel UDF you can automate Excel the way you need without the need to automate the Excel GUI (put the file on top etc.).
Please check _Excel_Open, _Excel_BookAttach (when the books is already open when you start the script) or _ExcelBookOpen (to open a new workbook) and _Excel_RangeRead to read the value of cells.


 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

Hi water and thanks for the quick replay.
I've read some more about the Excel function and got to know that you suggeast not to interact with the Excel GUI and that anything can be done without manipulate the GUI. However, as I mostly work with VBA and used to look at first instance what I'm doing with my code before speed it up, I would like to keep managing the GUI via AUTOIT.
I ended up with the following code and I'd like to know if there is another way to acitvate the Excel workbook not using WinActivate but something like $oExcel.Workboos($FileName).activate

Opt("WinTitleMatchMode",2)
#include <Excel.au3>
$oExcel=_Excel_Open()
$ListaExcel=_Excel_BookList()
for $x = 0 to $oExcel.workbooks.count
If msgBox(4,"Prova",$ListaExcel[$x][2] & "\" & $ListaExcel[$x][1])= 6 Then
    MsgBox(0,"File selezionato","Hai selezionato il file: " & $ListaExcel[$x][1])
    ExitLoop
endIf
Next
$FileName=$ListaExcel[$x][1]
MsgBox(0,"CHECK FILE NAME", "The file name you selected is : " & $FileName & @CRLF)

$nomefile=$ListaExcel[$x][2] & "\" & $ListaExcel[$x][1]
MsgBox(0,"Path",$nomefile)

$oWorkBook1 = _Excel_BookAttach($FileName,"FileName")
WinActivate($FileName)

Thanks in advance 

Share this post


Link to post
Share on other sites
$oWorkBook1 = _Excel_BookAttach($FileName,"FileName")
$oWorkBook1.Activate()

should do the trick.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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