Generic Macro

  • Thread starter Thread starter WildWill
  • Start date Start date
W

WildWill

I have a range of files stored in the same location/sub-directory, and I need
to make the same change to each one of the files. How do I achieve this by
using a Macro? My files are named 1.xls, 2.xls....50.xls
 
You could do this with a macro if you require that the new change be "hard
typed" into each of the workbooks, but you could do it simply by changing
things in one of the workbooks if you're willing to do a little up front work:

Open 1.xls and leave it open. Then open up 2.xls through 50.xls, one at a
time so you don't get lost, and go to the cell location in them that needs to
be updated and change the value to a formula pointing back to the same
location in 1.xls.

Process is to pick the location in 2.xls (and the rest) and type in an =
symbol and then select 1.xls, the sheet and location in it that holds the new
value, and then hit the [Enter] key and save 2.xls (and the rest). After
you've done this, all you have to do is change the value in 1.xls and when
you open the others, they will have the same new value in them.

This is only really worth the effort if this change process is going to be
something you do routinely.

If we had more information about the sheet name and cell locations that need
to be updated in them, we could probably have already provided code to do it.
I'll see what I can whip up quickly that you may be able to adapt to your
use.
 
Will
This macro should be placed in a regular module of a new blank file, or in
any other file you wish. That file must be placed in the same folder as the
files you want to change. Note that this macro will change EVERY .xls file
in that folder except the file that contains this macro. The names of your
files are not important to this macro. This macro looks at only the file
extensions. Note that this macro does not contain any code to actually
accomplish the changes you want. As written, this macro will only open and
close each file in the folder, in turn. It's up to you to insert the change
code where designated. If you need help with the change code, post back and
detail the changes you want made to each file. Include sheet names, rows,
columns, whatever, as needed to make the changes you want. HTH Otto
Sub AllFolderFiles()
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
MyPath = ThisWorkbook.Path
ChDir MyPath
TheFile = Dir("*.xls") 'The name of the first .xls file
Do While TheFile <> ""
If TheFile <> ThisWorkbook.Name Then
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
'At this point workbook wb is the active workbook.
'Insert your change code here
wb.Close
End If
TheFile = Dir 'The name of the next .xls file
Loop
End Sub
 
OK, I've tried to come up with something you can adapt and use.

Personally, I'd make a test folder and put copies of 1.xls through 50.xls
into it and run the code below against those files just in case things go
poorly. If it works, you can always copy the modified files back to the
original folder, overwriting those with the updated ones. This is a
'destructive' process, that is, it does make changes to at least one cell in
one sheet in each of the files, so best to work with copies of the files
initially.

Open a brand new workbook. Change the name of Sheet1 to SETUPSheet.
In cell A1 type in the full path to the 1.xls ... 50.xls files,
In cell A2 type in the name of the sheet in those workbooks that needs a
change
in cell A3 type the cell address that needs to be changed
in cell A4 type in the new data to write to those files

Press [Alt]+[F11] to open the VB Editor. Choose [Insert] --> Module and
copy the code below and paste it into that module. Close the VB Editor.
Save the workbook.

Use Tools --> Macro --> Macros to identify the macro and [Run] it. A
message should appear at the end telling you when all 50 workbooks have been
modified.
Here's the code:

Sub MakeMassChange()
'you need a worksheet named 'SETUPSheet' in this workbook.

'Cell A1 must hold the full path to the 1.xls ... 50.xls
'workbooks, like C:\Documents\Username\Stuff\MoreStuff

'Cell A2 must hold the name of the sheet that needs to
'be changed in the 1.xls ... 50.xls files. It MUST
'be spelled exactly like the name on the sheet tab
'in those 50 files.

'Cell A3 must hold the address of the cell to be changed
'like A3 or $B$55 or AA4 (with or with $ signs is ok)
'
'Cell A4 must hold the new value that is to be put into
'the cell shown in A3.
'
Dim anySheet As Worksheet

Dim nextFile As String
Dim fullPath As String
Dim LC As Integer
Const mySheetName = "SETUPSheet"
Dim fixSheetName As String
Dim fixCellAddress As String
Dim newValue As Variant

Set anySheet = ThisWorkbook.Worksheets(mySheetName)
fullPath = anySheet.Range("A1")
fixSheetName = anySheet.Range("A2")
fixCellAddress = anySheet.Range("A3")
newValue = anySheet.Range("A4")
Set anySheet = Nothing

If Right(fullPath, 1) <> Application.PathSeparator Then
fullPath = fullPath & Application.PathSeparator
End If

Application.ScreenUpdating = False
For LC = 1 To 50
nextFile = fullPath & Trim(Str(LC)) & ".xls"
Application.EnableEvents = False
Application.DisplayAlerts = False
Workbooks.Open nextFile
ActiveWorkbook.Worksheets(fixSheetName). _
Range(fixCellAddress) = newValue
ActiveWorkbook.Close True
Application.DisplayAlerts = True
Application.EnableEvents = True
Next ' go on to next file
MsgBox "Job Done"
End Sub
 
First store the location/filenames in column A:

In A1:

C:\Temp\1.xls

and then also fill-in A2 thru A50

Then:

Sub dural()
n = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To n
fl = Cells(i, 1).Value
Workbooks.Open (fl)
Sheets("Sheet1").Activate
Range("B9").Value = Date
ActiveWorkbook.Save
ActiveWorkbook.Close
Next
End Sub


This particular example puts the date in B9 of Sheet1 of each file.
 
Hi

My bad - I should have given more info on the change that I want to apply to
each file. It goes something like this:

1. Unprotect Sheet X, Y and Z;
2. Move to cell B22 in Sheet X and remove cell protection.
3. Select cells C39 on Sheet Y and delete the comments in those cells;
4. On Sheet Z, insert a cell at line 6 of column C.
5. Now re-apply protection on sheets X, Y and Z,
6. Save changes,
7. Close file.

I need to apply exactly the same routine to all 50 x files.
 
Will
You say:
2. Move to cell B22 in Sheet X and remove cell protection.
A cell doesn't have cell protection. A sheet does, but not a cell. Do you
mean that you want to UNLOCK B22? I'll assume that's what you want. Otto
Sub AllFolderFiles()
Dim wb As Workbook, TheFile As String
Dim MyPath As String, ws As Worksheet
MyPath = ThisWorkbook.Path
ChDir MyPath
TheFile = Dir("*.xls") 'The name of the first .xls file
Do While TheFile <> ""
If TheFile <> ThisWorkbook.Name Then
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
For Each ws In Sheets(Array("X", "Y", "Z"))
ws.Unprotect
Next ws
Sheets("X").Range("B22").Locked = False
Sheets("Y").Range("C39").ClearComments
Sheets("Z").Range("C6").Insert Shift:=xlDown
For Each ws In Sheets(Array("X", "Y", "Z"))
ws.Protect
Next ws
wb.Save
wb.Saved = True
wb.Close
End If
TheFile = Dir 'The name of the next .xls file
Loop
End Sub
 
Yes, I meant unblock the cell, so that if you have protection applied you can
still add/delete data in that cell. Thanks all for the solution!
 
Back
Top