pireng
01-15-2009, 09:39 AM
I have numerous spreadsheets with password protected worksheets that I need to remove some shading from. I have macros that work for each step 1) un-protect the worksheet, 2) remove the shading, 3) re-protect the worksheet, also have a macro that calls macros 1-3, but there are far too many to have to open each one separatly and copy the macros to each one. I would like to be able to browse to a folder and select multiple files and have it go from there... what do i need to make this work? I have a macro that will let me browse to a folder and select multiple files and then opens them, but I cant get the rest of the macros to work within it. I have no VBA experience and have come up with these macros by searching through forums and adapting them to my files... so I'm sure there is a better/easier way... Thanks for any help.
Module1-This works to unprotects the worksheet
Sub UnprotectAllSheets()
Dim anySheet As Worksheet
For Each anySheet In ThisWorkbook.Worksheets
anySheet.Unprotect Password:="password"
Next
End Sub
Module2-This works to reprotect the worksheet
Sub ProtectAllSheets()
Dim anySheet As Worksheet
For Each anySheet In ThisWorkbook.Worksheets
anySheet.Protect Password:=?password?
Next
End Sub
Module3-This works to remove the shading
Sub UNSHADE()
'
' UNSHADE Macro
'
'
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets(Array("TS1", "TS2", "TS3", "TS4", "TS5", "TS6", "TS7", "TS8", "TS9", "TS10", _
"TS11", "TS12", "TS13", "TS14", "TS15", "TS16", "TS17", "TS18", "TS19", "TS20", "TS21", _
"TS22", "TS23", "TS24", "TS52")).Select
Sheets("TS52").Activate
Sheets(Array("TS25", "TS26", "TS27", "TS28", "TS29", "TS30", "TS31", "TS32", "TS33", _
"TS34", "TS35", "TS36", "TS37", "TS38", "TS39", "TS40", "TS41", "TS42", "TS43", "TS44", _
"TS45", "TS46", "TS47", "TS48", "TS49")).Select Replace:=False
Sheets(Array("TS50", "TS51")).Select Replace:=False
Columns("K:S").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("D6").Select
Sheets(Array("TS1", "TS2", "TS3", "TS4", "TS5", "TS6", "TS7", "TS8", "TS9", "TS10", _
"TS11", "TS12", "TS13", "TS14", "TS15", "TS16", "TS17", "TS18", "TS19", "TS20", "TS21", _
"TS22", "TS23", "TS24", "TS25")).Select
Sheets("TS1").Activate
Sheets(Array("TS26", "TS27", "TS28", "TS29", "TS30", "TS31", "TS32", "TS33", "TS34", _
"TS35", "TS36", "TS37", "TS38", "TS39", "TS40", "TS41", "TS42", "TS43", "TS44", "TS45", _
"TS46", "TS47", "TS48", "TS49", "TS50")).Select Replace:=False
Sheets(Array("TS51", "TS52")).Select Replace:=False
Sheets("TS1").Select
End Sub
Module4-This works to run all three macros once you open the worksheet
Sub CallMyMacros()
Call UnprotectAllSheets
Call UNSHADE
Call ProtectAllSheets
End Sub
Module5- This does not work, but should go to the folder and work globally on the files, but gives an error that the first one is protected
Sub ProcessAll()
Dim Wb As Workbook, sFile As String, sPath As String
Dim itm As Variant
Dim strFileNames As String
sPath = ?C:\fix timecards\?
? Retrieve the current xl files in directory
sFile = Dir(?C:\fix timecards\? & ?*.xls?)
Do While sFile <> ??
strFileNames = strFileNames & ?,? & sFile
sFile = Dir()
Loop
? Open each file found
For Each itm In Split(strFileNames, ?,?)
If itm <> ?? Then
Set Wb = Workbooks.Open(sPath & itm)
Call CallMyMacros ?this runs my macro from above
Wb.Close True
End If
Next itm
End Sub
Module6-This one works to let you browse to any folder and open them, but I don?t know how to put my other macros into it to make it work.
Sub loopyarray()
Dim filenames As Variant
' set the array to a variable and the True is for multi-select
filenames = Application.GetOpenFilename(, , , , True)
counter = 1
' ubound determines how many items in the array
While counter <= UBound(filenames)
'Opens the selected files
Workbooks.Open filenames(counter)
' displays file name in a message box
MsgBox filenames(counter)
'increment counter
counter = counter + 1
Wend
End Sub
Module1-This works to unprotects the worksheet
Sub UnprotectAllSheets()
Dim anySheet As Worksheet
For Each anySheet In ThisWorkbook.Worksheets
anySheet.Unprotect Password:="password"
Next
End Sub
Module2-This works to reprotect the worksheet
Sub ProtectAllSheets()
Dim anySheet As Worksheet
For Each anySheet In ThisWorkbook.Worksheets
anySheet.Protect Password:=?password?
Next
End Sub
Module3-This works to remove the shading
Sub UNSHADE()
'
' UNSHADE Macro
'
'
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets(Array("TS1", "TS2", "TS3", "TS4", "TS5", "TS6", "TS7", "TS8", "TS9", "TS10", _
"TS11", "TS12", "TS13", "TS14", "TS15", "TS16", "TS17", "TS18", "TS19", "TS20", "TS21", _
"TS22", "TS23", "TS24", "TS52")).Select
Sheets("TS52").Activate
Sheets(Array("TS25", "TS26", "TS27", "TS28", "TS29", "TS30", "TS31", "TS32", "TS33", _
"TS34", "TS35", "TS36", "TS37", "TS38", "TS39", "TS40", "TS41", "TS42", "TS43", "TS44", _
"TS45", "TS46", "TS47", "TS48", "TS49")).Select Replace:=False
Sheets(Array("TS50", "TS51")).Select Replace:=False
Columns("K:S").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("D6").Select
Sheets(Array("TS1", "TS2", "TS3", "TS4", "TS5", "TS6", "TS7", "TS8", "TS9", "TS10", _
"TS11", "TS12", "TS13", "TS14", "TS15", "TS16", "TS17", "TS18", "TS19", "TS20", "TS21", _
"TS22", "TS23", "TS24", "TS25")).Select
Sheets("TS1").Activate
Sheets(Array("TS26", "TS27", "TS28", "TS29", "TS30", "TS31", "TS32", "TS33", "TS34", _
"TS35", "TS36", "TS37", "TS38", "TS39", "TS40", "TS41", "TS42", "TS43", "TS44", "TS45", _
"TS46", "TS47", "TS48", "TS49", "TS50")).Select Replace:=False
Sheets(Array("TS51", "TS52")).Select Replace:=False
Sheets("TS1").Select
End Sub
Module4-This works to run all three macros once you open the worksheet
Sub CallMyMacros()
Call UnprotectAllSheets
Call UNSHADE
Call ProtectAllSheets
End Sub
Module5- This does not work, but should go to the folder and work globally on the files, but gives an error that the first one is protected
Sub ProcessAll()
Dim Wb As Workbook, sFile As String, sPath As String
Dim itm As Variant
Dim strFileNames As String
sPath = ?C:\fix timecards\?
? Retrieve the current xl files in directory
sFile = Dir(?C:\fix timecards\? & ?*.xls?)
Do While sFile <> ??
strFileNames = strFileNames & ?,? & sFile
sFile = Dir()
Loop
? Open each file found
For Each itm In Split(strFileNames, ?,?)
If itm <> ?? Then
Set Wb = Workbooks.Open(sPath & itm)
Call CallMyMacros ?this runs my macro from above
Wb.Close True
End If
Next itm
End Sub
Module6-This one works to let you browse to any folder and open them, but I don?t know how to put my other macros into it to make it work.
Sub loopyarray()
Dim filenames As Variant
' set the array to a variable and the True is for multi-select
filenames = Application.GetOpenFilename(, , , , True)
counter = 1
' ubound determines how many items in the array
While counter <= UBound(filenames)
'Opens the selected files
Workbooks.Open filenames(counter)
' displays file name in a message box
MsgBox filenames(counter)
'increment counter
counter = counter + 1
Wend
End Sub