Run Excel VBA Macro via AutoIt...

[Hyflex]

Hi Guys,

I used to use the following: 

$Excs = _ExcelBookOpen("C:\Us\Excs.xlsm", 0)
Sleep(1000)
$Excs.Run("MAIN_DS");

But after the Excel.au3 was re-write it doesn't work. 

I've had a look at https://www.autoitscript.com/autoit3/docs/libfunctions/Excel%20Management.htm mainly, [url=https://www.autoitscript.com/autoit3/docs/libfunctions/_Excel_BookOpen.htm and [url=https://www.autoitscript.com/autoit3/docs/libfunctions/_Excel_Open.htm but I can't seem to open the file nor can I find anything about how to run a macro.

Any advise would be very much welcome.

Following _Excel_BookOpen I get the following error...
 

I'm using Excel 2013 64bit.

Edited March 18, 2015 by Hyflex

[water]

Do you run the AutoIt script as 32 or 64 bit?

[Hyflex]

Do you run the AutoIt script as 32 or 64 bit?

Both 32 and 64bit do the same thing :/

[TheSaint]

Should you have a command after the Exit command?

Apart from the code not continuing past that point, Exit can have a parameter.

So perhaps you are confusing it?

[Hyflex]

Should you have a command after the Exit command?

Apart from the code not continuing past that point, Exit can have a parameter.

So perhaps you are confusing it?

I'm following: https://www.autoitscript.com/autoit3/docs/libfunctions/_Excel_BookOpen.htm

The first line is what errors out:

Local $oAppl = _Excel_Open()

[water]

If you are running AutoIt 3.3.12.0 then this "error" can be ignored.

When letting parameters default, then _Excel_Open first tries to connect to an existing Excel instance. If none is running this COM error is issued and the function starts an Excel instance.

Start up Excel and then run your script. You shouldn't get any error.

I would always first check the return value of the function and for @error being <> 0. This should tell you if an error has occurred.

[Hyflex]

If you are running AutoIt 3.3.12.0 then this "error" can be ignored.

When letting parameters default, then _Excel_Open first tries to connect to an existing Excel instance. If none is running this COM error is issued and the function starts an Excel instance.

 

Start up Excel and then run your script. You shouldn't get any error.

 

I would always first check the return value of the function and for @error being <> 0. This should tell you if an error has occurred.

 

So how can I launch the file hidden & run the macro, it doesn't error out when excel is open... is there no way I can do it?

 

Local $oAppl = _Excel_Open()
Local $sWorkbook = "C:\Example.xlsm"
Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook)
$oWorkbook.Run("Example.xlsm!DEFAULT_MACRO");

Let's say I'm using the above WITH excel open prior to running, I get 1 error: 

Line 34 is: 

$oWorkbook.Run("Example.xlsm!DEFAULT_MACRO");

and even if I change it to:

$oWorkbook.Run("DEFAULT_MACRO");

The error's the same...

Edited March 19, 2015 by Hyflex

[13lack13lade]

i think that your code could be wrong, water can confirm but shouldn't it be like this:

Local $oAppl = _Excel_Open()
Local $sWorkbook = "C:\Example.xlsm"
Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook)
$oAppl.Run("DEFAULT_MACRO"); if your macro name is default_macro you do not need to include the sheet name, also you need to run the macro on the application not the workbook

i also never had any luck in running VBA with excel hidden.. easier just to let it open run the macro then close imo.. but i could be wrong

Edited March 19, 2015 by 13lack13lade

[water]

13lack13lade is correct. The Run method is only provided by the application object.

As long as a macro only uses the Excel COM then IMHO it should work fine even when the application object is invisible.

To open Excel hideen use

Local $oAppl = _Excel_Open(False)

BTW:

You do not need to start Excel in advance. When Excel is up then the Excel UDF connects to this instance, if not, a new instances is started.

[Hyflex]

13lack13lade is correct. The Run method is only provided by the application object.

As long as a macro only uses the Excel COM then IMHO it should work fine even when the application object is invisible.

To open Excel hideen use

Local $oAppl = _Excel_Open(False)

BTW:

You do not need to start Excel in advance. When Excel is up then the Excel UDF connects to this instance, if not, a new instances is started.

So how do I avoid the error I get?

I tried:

 

Local $oAppl = _Excel_Open(False)
If @error Then Sleep(1)

But the error is on line 66 of Excel.au3, I'd rather not edit Excel.au3 to put it in...

[water]

It is not an error, just an information. You can either disable your COM error handler, define it after _Excel_open has been called or force the Excel UDF to always start up a new instance by using:

_Excel_Open(False, Default, Default, Default, True)

[antonioj84]

add this on the excel macro

on error resume next

[odaylton]

Text translated from Portuguese by google - please apologize for any errors

First I have to thank the development team for product quality and the ease with which it is possible to develop tools with AutoIt.

In 2nd place apologize for disturbing you with my comments but I found relevant the following suggestions:

Let the problem.

I am using Excel 2003 and whenever I open a new planinha in Windows it shows me the macro created by default for all Spreadsheet with the name of TMTemp and TMTemp2 (image normal.jpg)

If I make the opening of new files using hotkeys I can run the macro normally.

 

$ExcelProg = 'E:\Arquivos de programas\Microsoft Office2003\OFFICE11'
$ExcelProg = '"' & $ExcelProg & '\excel.exe" "'
Run($ExcelProg & $Arq & '"')
        While 1
            If WinExists("Arquivo em uso") Then
                _WinWaitActivate("Arquivo em uso", "")
                Send("{ENTER}")
                ;ExitLoop
            EndIf
            If WinExists("Microsoft Excel - " & $NomeArq, "Barra de menus da pl") Then ExitLoop
        WEnd
        _WinWaitActivate("Microsoft Excel - " & $NomeArq, "Barra de menus da pl")
       Send("{ALTDOWN}{F8}{ALTUP}")
        _WinWaitActivate("Macro", "")
        Send("{TAB}")
        Send("{DOWN}{DOWN}{DOWN}{DOWN}{DOWN}{DOWN}{DOWN}")
        Send("{ENTER}")

 

But when I try to open using the UDF #include <Excel.au3>. (image biblio.jpg)

 

 

$oMeuExcel=_Excel_Open()
$Plan1=_Excel_BookNew($oMeuExcel, 2)
;Does not display the macros

how to make the instance of Excel starts with the standard macros

Thank you

Edited October 19, 2016 by odaylton

[water]

Will check when I return from my vacation 😊

[odaylton]

ok awaiting and more someone can help me thank

[junkew]

This works like a charm

#include <Excel.au3>
Local $oAppl = _Excel_Open()
Local $sWorkbook = "C:\temp\Example.xlsm"
Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook)
$oAppl.Run("DEFAULT_MACRO"); if your macro name is default_macro you do not need to include the sheet name, also you need to run the macro on the application not the workbook

Public Sub default_macro()
    MsgBox "Hello"
End Sub

would suggest to try this first

$xlApp=objcreate("excel.application")
$xlapp.visible=True

sleep(5000)

shoiuld show you at least 5 seconds excel. I was surprised after script is finishing it kills my excel application

[junkew]

regarding not loading the "default" workbooks I think that is by design

http://stackoverflow.com/questions/213375/loading-addins-when-excel-is-instantiated-programmatically

 

[water]

odayIton,
Seems junkew was faster in replying to your question

[odaylton]

 

On 27/10/2016 at 1:03 PM, water said:

odayIton,
Seems junkew was faster in replying to your question

Translated by google, sorry
This may seem obvious to you but I ate my limited ability I could not solve,
Can you be more gracious in helping me with this?

[odaylton]

On 27/10/2016 at 1:03 PM, water said:

odayIton,
Seems junkew was faster in replying to your question

Translated by google, sorry
This may seem obvious to you but I ate my limited ability I could not solve,
Can you be more gracious in helping me with this?