Sign in to follow this  
Followers 0
jc0r

Run Excel Macro

16 posts in this topic

Hi all

What i am trying to do i believe is relatively simple. I have an Excel workbook open call test.xls and i would like to get AutoIt to execute one of the macros stored wiithin the workbook. The macro is called "just1"

I created the following code:

$oExcel.run("just1")

but get the following error:

>"C:\Program Files\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "C:\Users\jc0r\Desktop\test.au3"

C:\Users\jc0r\Desktop\test.au3 (1) : ==> Expected a "=" operator in assignment statement.:

$oExcel.run("just1")

$oExcel^ ERROR

>Exit code: 1 Time: 0.238

Any ideas please?

Many thanks

jc0r

Share this post


Link to post
Share on other sites



Nope sorry same error:

>"C:Program FilesAutoIt3SciTE..autoit3.exe" /ErrorStdOut "C:Usersjc0rDesktoptest.au3"

C:Usersjc0rDesktoptest.au3 (1) : ==> Expected a "=" operator in assignment statement.:

$oExcel.run = "just1"

$oExcel^ ERROR

>Exit code: 1 Time: 0.225

I dont know if it matters but i am using Excel 2003 and i do not have anything else in my script other than that one line. I am not sure if i need to reference to the workbook or not?

Share this post


Link to post
Share on other sites

In this case the error is clear, you don't have an excel object. Use Excel UDF to open your document and then run the macro.


When the words fail... music speaks

Share this post


Link to post
Share on other sites

This is not possible, i need to Excel document to already be open and i use AutoIt to call macros from within the workbook. Do you know if this is possible please?

Share this post


Link to post
Share on other sites

Look in help file at _ExcelBookOpen() function. This function will return you an object that allow run method to run your macro.


When the words fail... music speaks

Share this post


Link to post
Share on other sites

Use function _ExcelBookAttach of the Excel UDF to attach to an already open workbook. Then you can run the macro.


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

Many thanks for you help so far. I only have 2 posts left for today as i am a new member but i am struggling with what you've provided so far.

This is my current script however it just seems to open another copy of the workbook and not do anything

#include <Excel.au3>

#include <File.au3>

Local $sFilePath = "c:test.xls"

_ExcelBookOpen($sFilePath)

Local $oExcel = _ExcelBookAttach($sFilePath)

$oExcel.run("just1")

I do not want it to open another workbook, as you stated, i want it to attach to the existing one but when i remove the line:

_ExcelBookOpen($sFilePath)

The whole thing breaks down.

This seems tricky when all i want to do is execute a macro in Excel?

Thanks

Share this post


Link to post
Share on other sites

I would use:

#include <Excel.au3>

Global $sFilePath = "c:test.xls"
Global $oExcel = _ExcelBookAttach($sFilePath) ; Returns the workbook object
$oExcel.parent.run("just1") ; Run method needs the application object

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

I cannot thank you enough, this worked just as i desired. I am not allowed to post anymore today as i am new so thanks for taking the time with my problem.

Regards

Share this post


Link to post
Share on other sites

Glad to be of service :D


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

Hello,

I am not able to DL the Beta 2 Excel Rewrite zip file: 

Is there another link?

Share this post


Link to post
Share on other sites

Thre have been 40 downloads. And so far I haven't heard of a problem.

Do you get an error message or is the download incomplete?


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

I found the problem - corporate firewall.  Thanks.

Share this post


Link to post
Share on other sites

Glad the problem could be solved :)


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

Water,

I am using the

$oExcel.parent.Run("MacroName")

as you stated, but get this error message:

==> The requested action with this object has failed.:
$oExcel.parent.Run("ADRReport")
$oExcel.parent.Run("ADRReport")^ ERROR

Is something else needed at the end of the script line?

Share this post


Link to post
Share on other sites

Can you add a COM error handler so we get more detailed error information? The help file for ObjEvent shows what you need to do.


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
Sign in to follow this  
Followers 0