Jump to content
Sign in to follow this  
mark1983

Autoit on excel 2013

Recommended Posts

mark1983

I was able to run a script which ran successfully on excel 2010. The programme open a .xlam (add-in) file and does some sort of automation. However, when I try to run the same script to open the add-in in excel 2013, excel opens without the add-in menu. Please help.

#include <Excel.au3>
$sAddIn ="C:iMacrosMacrosDemoReport v8.xlam" ;This file should already exist
$oExcel = ObjCreate("Excel.Application")   
$oExcel.Visible=1 
$oExcel.WorkBooks.Add
$oAddIn = $oExcel.AddIns.Add($sAddIn)
$oAddIn.Installed = False
$oAddIn.Installed = True
Sleep(3000)
Send("!x")
Sleep(2000)
MouseClick("left",10,55, 1)
Sleep(3000)
MouseMove(10,100)
Sleep(3000)
MouseClick("left",10,100, 1)
Sleep(3000)
Send("C:UsersDocumentsProject")
Send("{Enter}")
Exit  
 

Share this post


Link to post
Share on other sites
water

You need to do some kind of error checking. Add a COM error handler to get detailed error information. You will find an example in the help file for ObjEvent.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 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
mark1983

Thanks for your response, water. I added some parameters for error handling but I still didn't get any detailed error information. Please I need help.

#include <Excel.au3>
Local $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")
$sAddIn ="C:iMacrosMacrosDemoReport v8.xlam" ;This file should already exist
$oExcel = ObjCreate("Excel.Application")   
$oExcel.Visible=1 
$oExcel.WorkBooks.Add
$oAddIn = $oExcel.AddIns.Add($sAddIn)
$oAddIn.Installed = False
$oAddIn.Installed = True
Sleep(3000)
Send("!x")
Sleep(2000)
MouseClick("left",10,55, 1)
Sleep(3000)
MouseMove(10,100)
Sleep(3000)
MouseClick("left",10,100, 1)
Sleep(3000)
Send("C:UsersDocumentsProject")
Send("{Enter}")
Exit  

Share this post


Link to post
Share on other sites
water

By "error checking" I mean to verify that a command was run successfully before the next is executed.

Don't see anything in your code that meets this description.
Did you have a look at ObjEvent?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 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
mark1983

Yes, I saw some examples in the autoit help but the two main key function sthat was applied was "Local $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")" and "Local $oIEEvents = ObjEvent($oIE, "_IEEvent_", "DWebBrowserEvents2")" . I am lost at this stage. I will appreciate some assistance.

Thanks!

-M

Share this post


Link to post
Share on other sites
water

Local $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc") ; Add at the top of your script

; ... your script goes here

Func _ErrFunc($oError)
    ConsoleWrite("err.number is: " & @TAB & $oError.number & @CRLF & _
            "err.windescription:" & @TAB & $oError.windescription & @CRLF & _
            "err.description is: " & @TAB & $oError.description & @CRLF & _
            "err.source is: " & @TAB & $oError.source & @CRLF & _
            "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
            "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
            "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
            "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
            "err.retcode is: " & @TAB & $oError.retcode & @CRLF & @CRLF)
EndFunc   ;==>_ErrFunc


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 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
mark1983

Thanks but I just did that and there was still no error message. The excel (excel 2013)  opened up without the add-in menu. Like I said earlier, it worked fine with Excel 2010 but Excel 2013 is and including the add-ins (send!x). Any further guide/assiatance will be appreciated.

Local $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")

#include <Excel.au3>
$sAddIn ="C:iMacrosMacrosDemoReport v8.xlam" ;This file should already exist
$oExcel = ObjCreate("Excel.Application")   
$oExcel.Visible=1 
$oExcel.WorkBooks.Add
$oAddIn = $oExcel.AddIns.Add($sAddIn)
$oAddIn.Installed = False
$oAddIn.Installed = True
Sleep(3000)
Send("!x")
Sleep(2000)
MouseClick("left",10,55, 1)
Sleep(3000)
MouseMove(10,100)
Sleep(3000)
MouseClick("left",10,100, 1)
Sleep(3000)
Send("C:UsersDocumentsProject")
Send("{Enter}")
Exit  
Func _ErrFunc($oError)
    ConsoleWrite("err.number is: " & @TAB & $oError.number & @CRLF & _
            "err.windescription:" & @TAB & $oError.windescription & @CRLF & _
            "err.description is: " & @TAB & $oError.description & @CRLF & _
            "err.source is: " & @TAB & $oError.source & @CRLF & _
            "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
            "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
            "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
            "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
            "err.retcode is: " & @TAB & $oError.retcode & @CRLF & @CRLF)
EndFunc   ;==>_ErrFunc

Share this post


Link to post
Share on other sites
water

Mixing COM and GUI is a bad idea! Send statements are not guaranteed to go to the correct window.

And why do you set Addin to False AND True?

$oAddIn.Installed = False
$oAddIn.Installed = True

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 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
mark1983

Good Question. I used both false and true based on an example I saw on one of the threads here. So are you saying that I can open all .xlam file and mimick the application by GUI? How? Also, what can be a work around here? I am still puzzled on why it will work on one and not the other. 

Share this post


Link to post
Share on other sites
water

I suggest to use COM for everything because - in my eyes - using Send works "by accident".

Can you describe what you want to do?

You start Excel, create a new Workbook and add an AddIn ... what do you try to do with the Send statements?

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 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
mark1983

What I am trying to do is to open a .xlam file. Click a custom macro drop down and select an item under it. Once the item is clicked, the second send (Send C:UsersDocumentsProject)  specifies which directory to save the file. The first send (Send("!x")) clicks on the add-in menu because without that the program will not be able to access the customized macro. I am trying to mimic it like if it was a human being using it. Please let me know if you have other questions. Upon running the script on excel 2010, the add-ins menu shows up( please see attached) and the program works fine. When I run the script in excel 2013, the scrip opens up excel but without the add-in menu. I think that is the missing link. I will appreciate further help.

-M

post-82961-0-92175700-1383920819_thumb.p

post-82961-0-44004600-1383920954_thumb.p

Share this post


Link to post
Share on other sites
water

Can you start the VBA macro recorder in Excel, do all the steps you described above and tehn post the recorde VBA code? This should be "easy" to translate to AutoIt.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 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
mark1983

When I did the VBA recorder this is all it returns. I am sure I did it in the right way. Any further help will be really appreciated.

Sub Macro1()
'
' Macro1 Macro
 
'
End Sub

Share this post


Link to post
Share on other sites
water

Could you please post the XLAM file you are talking about?

I only have Excel 2010 available but would like to test if possible.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 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
mark1983

Do you have a private link I can send it to. It is not something I can share to the public.

-M

Share this post


Link to post
Share on other sites
water

If you like you can send me a private mail using this forum.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 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
mark1983

I am sorry to ask but how what is the forum email address?

Share this post


Link to post
Share on other sites
water

You find your user name in the upper right corner of this page. Click on the arrow to open the dropdown. Select "Personal Messanger", then click on "Compose New".


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 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
mark1983

I just sent the message.

Share this post


Link to post
Share on other sites
water

Thanks. Got it. I hope to find some time to test today or at least tomorrow.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 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

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  

×