Lee Bussy Posted June 25, 2009 Share Posted June 25, 2009 (edited) 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 Link to comment Share on other sites More sharing options...
Lee Bussy Posted June 25, 2009 Author Share Posted June 25, 2009 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 Link to comment Share on other sites More sharing options...
Lee Bussy Posted June 30, 2009 Author Share Posted June 30, 2009 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. Link to comment Share on other sites More sharing options...
hhzz Posted July 2, 2009 Share Posted July 2, 2009 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. Link to comment Share on other sites More sharing options...
hhzz Posted July 2, 2009 Share Posted July 2, 2009 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 Link to comment Share on other sites More sharing options...
Lee Bussy Posted July 2, 2009 Author Share Posted July 2, 2009 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. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now