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

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

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

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

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