Running Excel VBA Macros from AutoIt

[watsdm4]

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.

[Locodarwin]

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

[watsdm4]

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

[Locodarwin]

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

[JohnBailey]

Gang,

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

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