Sign in to follow this  
Followers 0
watsdm4

Running Excel VBA Macros from AutoIt

5 posts in this topic

Hi,

nOOb to the AutoIt scene here.

I'd like to run, or at least find a way to parse my original Excel macros from AutoIt.

I've downloaded the latest ExcelCOM UDF and I don't see any functions related to my problem.

Preferrably, I want to stay away from sending keystrokes, unless there is no other option.

In searching through the archives, I've come up empty-handed. So, please help!

This part of the code works perfectly.....I just don't have good experiences with end-users and keystroke automation.

$Modules = "\\orf-fs\sys\EI\Test\Canon Script Files\filestemp.xls"
$CSVPath = "\\orf-fs\sys\EI\Test\Canon Script Files\files.csv"

$oExcel = _ExcelBookOpen($Modules,1,False)

;This is where macro automation would be nice
Send("{ALT}tmm")
Send("cleansheet{ENTER}")
 
$answer = MsgBox(1, "Expeditors:  Line Item Assistant", "Add your files to the Excel Spreadsheet, then click OK to continue.")
If $answer = 2 Then
    _ExcelBookClose($oExcel,2,-1)
    Exit
EndIf

If Not WinActive("filestemp.xls") Then WinActivate("filestemp.xls")

;This is where macro automation would be nice
Send("{ALT}tmm")
Send("addzeros{ENTER}")

_ExcelBookSaveAs($oExcel,$CSVPath,"txt",-1,2)
_ExcelBookClose($oExcel,-1,-1)

Any suggestions would be greatly appreciated.

Share this post


Link to post
Share on other sites



You won't believe how easy it actually is. Try this up there in place of your keystrokes:

$oExcel.Application.Run("Macro_Name", "Parameter1", "Parameter2")
oÝ÷ Ù©§¶ªº^v+^Ì"¶¨ºº0Ü¢êìx÷«ªljëh×6
$oExcel.Application.Run("cleansheet")

-S


(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]

Share this post


Link to post
Share on other sites

Thanks Locodarwin! Worked like a charm.

Your UDF is a godsend for me and my colleagues.

BTW, I commented out the last DisplayAlerts line on your _ExcelBookClose function to work with CSV's. I'm running Office 2000 at work and the default setting for comma-delimited files is set to ask the user to save upon close, regardless of the save status. I haven't noticed any adverse effects by doing this......just thought you should know.

Func _ExcelBookClose($oExcel, $fSave = 1, $fAlerts = 0)
    If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
    If $fSave > 1 Then $fSave = 1
    If $fSave < 0 Then $fSave = 0
    If $fAlerts > 1 Then $fAlerts = 1
    If $fAlerts < 0 Then $fAlerts = 0
    $oExcel.Application.DisplayAlerts = $fAlerts
    $oExcel.Application.ScreenUpdating = $fAlerts
    If $fSave Then
        $oExcel.ActiveWorkBook.Save
    EndIf
;   $oExcel.Application.DisplayAlerts = True
    $oExcel.Application.ScreenUpdating = True
    $oExcel.Quit
    Return 1
EndFunc ;==>_ExcelBookClose

Share this post


Link to post
Share on other sites

Thanks for the positive feedback! I'm glad you find the collection useful.

I set both DisplayAlerts and ScreenUpdating back to True just to reset Excel back to default for both properties. I do this because whatever you set these properties to gets saved either on the application level or the workbook level - I'm unsure which. They remain what they were set to until specifically set otherwise, like a state machine. What I should do at some point in the future is make turning on/off alerts and screen updating a separate function. Unfortunately there is some weirdness that occurs for a number of users where setting the saved property to True and turning off DisplayAlerts still does not suppress the save alert - even for standard .XLS files. I'm still trying to bang up a solution for that as well.

-S


(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]

Share this post


Link to post
Share on other sites

Gang, :whistle:

I want to make sure I understand this correctly and it's not just a glitch-in-my-matrix :P

When I use _ExcelBookOpen the "do you want to run the macro" window does not come up. Is this normal?


A decision is a powerful thing

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