Sign in to follow this  
Followers 0
13lack13lade

AutoIT & Excel

6 posts in this topic

#1 ·  Posted (edited)

Hi Everyone!

New AutoIt user, not a programmer in anyway however i am an excel power user and do know enough VBA to create macros etc however i have only ever taught myself and am having quite some trouble with AutoIt..

I am moving onto AutoIt now to automate alot of my excel macros i created for database manipulation but cannot for the life of me understand the coding... Have googled and youtubed for hours trying to find any sort of info i can... I have even read the UDF syntax however when i to do something, it never works...

What im trying to do:

I need to be able to open excel file, pull data out, open & paste into a datauploading program, open the CRM program and login, run the datauploading program script and then log out, close everything.

What i don't understand:

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

Local $sFilePath1 = @ScriptDir & "Test.xls" ;This file should already exist
Local $oExcel = _ExcelBookOpen($sFilePath1)

If @error = 1 Then
MsgBox(0, "Error!", "Unable to Create the Excel Object")
Exit
ElseIf @error = 2 Then
MsgBox(0, "Error!", "File does not exist - Shame on you!")
Exit
EndIf

This is the example from the AutoIt Help and i dont understand where the @scriptDir came from? where the file path goes etc.. i know once i understand how it works i will be able to manipulate it like VBA but initally just dont understand.. Like i dont understand which is commands and what parts i change to suit me specifically...

Any tips/advice/help would be muchly appreciated. - Apologies if this is a stupid post i just cannot find good information on AutoIt working with Excel.

Edited by 13lack13lade

Share this post


Link to post
Share on other sites



Is this statement in your script?

_ExcelBookOpen($sFilePath [, $fVisible = 1 [, $fReadOnly = False [, $sPassword = "" [, $sWritePassword = ""]]]])

That's just the syntax description. Brackets denote optional parameters. So the above line should - if you use all default parameters - look like:

_ExcelBookOpen($sFilePath, 1, False, "", "")

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

No i was simply using this as an example because obviously im going to need to open the excel file and thought this would be a good start.

What i mean is $sFilePath would that mean that its _ExcelBookOpen($sC:/blahblah/blah.xls) or is $sFilePath the command? just unsure on how to use the syntax correctly 

Share this post


Link to post
Share on other sites

$sFilePath is a variable.

Either use

_ExcelBookOpen("C:\blahblah\blah.xls")

or

$sFilePath = "C:\blahblah\blah.xls"
_ExcelBookOpen($sFilePath)

And yes, you need to read the help file or some tutorials about the basics of AutoIt.


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

BTW: The Excel UDF that comes with AutoIt doesn't support the "new" formats (xlsx etc.), only okrs on the active worksheet and has some other limitations.

If you like you can test my rewrite of the UDF (still an Alpha version). For download please see my signature.


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

Thank you for your help, i will definately give the UDF a go and spend sometime going through the tutorials.

I think i was just over complicating things and the code is abit intimidating at first but i look forward to figuring it out, it seems the possibilities for automation are endless!

 

Thanks again.

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  
Followers 0