Jump to content

Autoit on excel 2013


 Share

Go to solution Solved by water,

Recommended Posts

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

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 (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

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 (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

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 (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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 (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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 (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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 (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...