Jump to content
Sign in to follow this  
Lee Bussy

Open Spreadsheet, Run Macro?

Recommended Posts

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 by Lee Bussy

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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. :)

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  

×