Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to batch fix errors on password protected workbooks
I have a new spreadsheeet we're using for data entry and have discovered a
few errors in formulas in it. Unfortunately, there are now more than 500 copies of it distributed to our staff. I am hoping someone can help me find a macro solution to batch fix my errors. The sheets in question are protected with a password, which seems to complicated matters. Is it possible to creat a macro that will: 1. run on an entire folder of workbooks 2. unprotect the sheet when run 3. make the corrections to the formulas in question 4. reprotect and close the sheet I can provide additional information on the spefic changes that need to be made, but I thought I'd first see if what I want to do is even possible. Thanks in advance. spence |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to batch fix errors on password protected workbooks
Assuming you know the path to your folder, make a list of the
workbook names in a range called, say "BookNames" MyBook.xls ThisBook.xls etc This will make the same changes to each Bk/Sheet in the folder. It will also require that each Bk has a Sheet with the name you indicate below. Recommended: Test thoroghly in advance and add error handling. It's difficult to know what 500 users have done to your workbooks. (Like changing locations, sheet names etc ..........) Option Explicit sub MakeChanges() Dim sPath as String Dim Wbk as Workbook Dim Sh as Worksheet Dim oCell as Range sPath = "C:\Documents and Settings\User\" 'adjust as needed 'Start the loop For each oCell in Range("BookNames") Set Wbk = Workbooks.Open (sPath & oCell.Value) If Wbk is nothing then Msgbox "Can't find the wbk: " & oCell.Value GoTo Shutdown End If Set Sh = Wbk.Sheets("MySheet") With Sh .Activate .Unprotect "ABC" .Range("C14").formula = "=A14+B14" 'Make other changes .Protect "ABC" End with '' Save your changes ActiveWorkbook.Close True Next oCell Shutdown: Set Sh = nothing Set oCell = nothing End sub "spence" wrote: I have a new spreadsheeet we're using for data entry and have discovered a few errors in formulas in it. Unfortunately, there are now more than 500 copies of it distributed to our staff. I am hoping someone can help me find a macro solution to batch fix my errors. The sheets in question are protected with a password, which seems to complicated matters. Is it possible to creat a macro that will: 1. run on an entire folder of workbooks 2. unprotect the sheet when run 3. make the corrections to the formulas in question 4. reprotect and close the sheet I can provide additional information on the spefic changes that need to be made, but I thought I'd first see if what I want to do is even possible. Thanks in advance. spence |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to batch fix errors on password protected workbooks
The answer to your question is Yes. All this can be done and quite easily.
It will help if:- 1) The password is the same (or this is a table of file names and corresponding passwords) 2) The formulas are in the same cells in each spreadsheet Cheers Robert "spence" wrote in message ... I have a new spreadsheeet we're using for data entry and have discovered a few errors in formulas in it. Unfortunately, there are now more than 500 copies of it distributed to our staff. I am hoping someone can help me find a macro solution to batch fix my errors. The sheets in question are protected with a password, which seems to complicated matters. Is it possible to creat a macro that will: 1. run on an entire folder of workbooks 2. unprotect the sheet when run 3. make the corrections to the formulas in question 4. reprotect and close the sheet I can provide additional information on the spefic changes that need to be made, but I thought I'd first see if what I want to do is even possible. Thanks in advance. spence |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to batch fix errors on password protected workbooks
Your (VBA) code will build a list of files. You can even use wildcards to be
more selective. "cush" wrote in message ... Assuming you know the path to your folder, make a list of the workbook names in a range called, say "BookNames" MyBook.xls ThisBook.xls etc This will make the same changes to each Bk/Sheet in the folder. It will also require that each Bk has a Sheet with the name you indicate below. Recommended: Test thoroghly in advance and add error handling. It's difficult to know what 500 users have done to your workbooks. (Like changing locations, sheet names etc ..........) Option Explicit sub MakeChanges() Dim sPath as String Dim Wbk as Workbook Dim Sh as Worksheet Dim oCell as Range sPath = "C:\Documents and Settings\User\" 'adjust as needed 'Start the loop For each oCell in Range("BookNames") Set Wbk = Workbooks.Open (sPath & oCell.Value) If Wbk is nothing then Msgbox "Can't find the wbk: " & oCell.Value GoTo Shutdown End If Set Sh = Wbk.Sheets("MySheet") With Sh .Activate .Unprotect "ABC" .Range("C14").formula = "=A14+B14" 'Make other changes .Protect "ABC" End with '' Save your changes ActiveWorkbook.Close True Next oCell Shutdown: Set Sh = nothing Set oCell = nothing End sub "spence" wrote: I have a new spreadsheeet we're using for data entry and have discovered a few errors in formulas in it. Unfortunately, there are now more than 500 copies of it distributed to our staff. I am hoping someone can help me find a macro solution to batch fix my errors. The sheets in question are protected with a password, which seems to complicated matters. Is it possible to creat a macro that will: 1. run on an entire folder of workbooks 2. unprotect the sheet when run 3. make the corrections to the formulas in question 4. reprotect and close the sheet I can provide additional information on the spefic changes that need to be made, but I thought I'd first see if what I want to do is even possible. Thanks in advance. spence |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to batch fix errors on password protected workbooks
Robert,
Can you explain: Your (VBA) code will build a list of files<< My code doesn't build a list. It requires the user to create the list prior to running the code. Not sure I understand ? "Robert Hind" wrote: Your (VBA) code will build a list of files. You can even use wildcards to be more selective. "cush" wrote in message ... Assuming you know the path to your folder, make a list of the workbook names in a range called, say "BookNames" MyBook.xls ThisBook.xls etc This will make the same changes to each Bk/Sheet in the folder. It will also require that each Bk has a Sheet with the name you indicate below. Recommended: Test thoroghly in advance and add error handling. It's difficult to know what 500 users have done to your workbooks. (Like changing locations, sheet names etc ..........) Option Explicit sub MakeChanges() Dim sPath as String Dim Wbk as Workbook Dim Sh as Worksheet Dim oCell as Range sPath = "C:\Documents and Settings\User\" 'adjust as needed 'Start the loop For each oCell in Range("BookNames") Set Wbk = Workbooks.Open (sPath & oCell.Value) If Wbk is nothing then Msgbox "Can't find the wbk: " & oCell.Value GoTo Shutdown End If Set Sh = Wbk.Sheets("MySheet") With Sh .Activate .Unprotect "ABC" .Range("C14").formula = "=A14+B14" 'Make other changes .Protect "ABC" End with '' Save your changes ActiveWorkbook.Close True Next oCell Shutdown: Set Sh = nothing Set oCell = nothing End sub "spence" wrote: I have a new spreadsheeet we're using for data entry and have discovered a few errors in formulas in it. Unfortunately, there are now more than 500 copies of it distributed to our staff. I am hoping someone can help me find a macro solution to batch fix my errors. The sheets in question are protected with a password, which seems to complicated matters. Is it possible to creat a macro that will: 1. run on an entire folder of workbooks 2. unprotect the sheet when run 3. make the corrections to the formulas in question 4. reprotect and close the sheet I can provide additional information on the spefic changes that need to be made, but I thought I'd first see if what I want to do is even possible. Thanks in advance. spence |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Links to password protected workbooks | Links and Linking in Excel | |||
Link Password Protected Workbooks | Excel Discussion (Misc queries) | |||
password protected file with pivot table errors | Charts and Charting in Excel | |||
Excel Macro to open password protected workbooks? | Excel Discussion (Misc queries) | |||
Password protected workbooks | Excel Discussion (Misc queries) |