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

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

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" <[email protected]> wrote in message
news:[email protected]...
>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

Your (VBA) code will build a list of files. You can even use wildcards to be
more selective.


"cush" <[email protected]> wrote in message
news:[email protected]...
> 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

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>