Open Spreadsheet, Run Macro?

[Lee Bussy]

I have a need to iterate through a large number of Excel files, apply protection and then save them. The following VB snippet does what I need as an Excel macro:

Sub protectShts()
   Dim ws    As Worksheet
   Dim cl    As Range

   For Each ws In ThisWorkbook.Worksheets
       'test if protected, skips protected sheets
       With ws
           If Not .ProtectContents = True Then
               
               'now lock cells
               .Cells.Locked = True
               'protect the sheet with password
               'sheets must be protected for Locked Cells to take effect
               .Protect "password"
               .EnableSelection = xlUnlockedCells
           End If
       End With
   Next ws
End Sub

I can select the files, parse them into an array, open them in Excel, etc., but I'm a little lost on how to run a macro against (within?) a spreadsheet from AutoIT. Can someone nudge me in the right direction? I've been searching around a bit but have not found anything - quiite possibly I don't use AutoIT enough to be familiar with all I should. There's thousands of spreadsheets that need to be protected in such a manner, so I'm really hoping to automate.

Thanks in advance for any assistance.

Edited June 25, 2009 by Lee Bussy

[Lee Bussy]

I found some examples in VB that do what I want (sorta) that I have tried to adopt. Not working as expected though but here's the idea:

$oExcel = ObjCreate("Excel.Application")                    ; Create an Excel Object 

WITH $oExcel 
    .Visible = 1                                            ; Let Excel show itself
    .Workbooks.Open "./test.xls"                            ; Open workbook
    .VBE.ActiveVBProject.VBComponents.Import "protect.vb"   ; Import Macro
    .Run "protectShts"                                      ; Run macro
    .Workbooks.Save                                         ; Save workbook
    .Quit                                                   ; Quit Excel
ENDWITH

[Lee Bussy]

I've coded this in VBA inside Excel. A little dissapointed that I was unable to find an AutoIT solution but it's what it is I guess.

[hhzz]

I've coded this in VBA inside Excel. A little dissapointed that I was unable to find an AutoIT solution but it's what it is I guess.

I am not sure how to run the macro, but you could consider doing the work directly in AutoIT. If you look at the include/Excel.au3 you'll see that it references the Excel objects and functions directly, which you can pull out and use in your AutoIT.

_ExcelSheetList should help you move through the worksheets.

The other option is to use the Send or Mouse Click to run the macro once you have the spreadsheet open.

Hope that helps.

[hhzz]

I just saw another posting by Juvigy that has the solution for you. Check it out.

http://www.autoitscript.com/forum/index.ph...79&hl=Excel

[Lee Bussy]

Thanks very much hhzz ... I'll give those a look and see if I can make it work. At this point it's just the principle of the thing - I knew it had to work somehow.