Hyflex

Run Excel VBA Macro via AutoIt...

20 posts in this topic

#1 ·  Posted (edited)

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...
 NJNZxYv.png

I'm using Excel 2013 64bit.

Edited by Hyflex

Share this post


Link to post
Share on other sites



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


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

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

Both 32 and 64bit do the same thing :/

Share this post


Link to post
Share on other sites

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?


AutoIt.4.Life Clubrooms - Life is like a Donut (secret key)

Make sure brain is in gear before opening mouth!
Remember, what is not said, can be just as important as what is said.

Spoiler

What is the Secret Key? Life is like a Donut

If I put effort into communication, I expect you to read properly & fully, or just not comment.
Ignoring those who try to divert conversation with irrelevancies.
If I'm intent on insulting you or being rude, I will be obvious, not ambiguous about it.
I'm only big and bad, to those who have an over-active imagination.

I may have the Artistic Liesense ;) to disagree with you. TheSaint's Toolbox (be advised many downloads are not working due to ISP screwup with my storage)

userbar.png

Share this post


Link to post
Share on other sites

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.


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

#7 ·  Posted (edited)

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: 

BFC7uZh.png

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 by Hyflex

Share this post


Link to post
Share on other sites

#8 ·  Posted (edited)

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 by 13lack13lade

Share this post


Link to post
Share on other sites

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.


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

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...

Share this post


Link to post
Share on other sites

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)

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

add this on the excel macro

on error resume next

Share this post


Link to post
Share on other sites

#13 ·  Posted (edited)

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}")

normal.JPG

 

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

biblio.JPG

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

Thank you

Edited by odaylton

Share this post


Link to post
Share on other sites

Will check when I return from my vacation 😊


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

ok awaiting and more someone can help me thank

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

odayIton,
Seems junkew was faster in replying to your question :)


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

#19 ·  Posted

 

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?

Share this post


Link to post
Share on other sites

#20 ·  Posted

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?

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