L
LWhite
Hello everyone,
I am trying to do the following.
1 Go to a folder on my computer
2 open the first workbook in the folder
3 change the value of two cells on one tab
4 save the workbook
5 close the workbook
6 repeat until every workbook in the folder has been changed
I will be changing the same two cells with the same two new values on the
same tab name for all workbooks. I tried the following but have since found
out the .filesearch no longer works. Any help would be appreciated.
LWhite
Sub MassEdit()
'
' MassEdit Macro
'
Dim myBook As Workbook, _
ThisSheet As Worksheet, _
sDir, sFile As String
sDir = "C:\MTD\"
sFile = "*.xls"
Set ThisSheet = ActiveWorkbook.Sheets(1)
With Application
.DisplayAlerts = False
With .FileSearch
.NewSearch
.LookIn = sDir
.Filename = sFile
.MatchTextExactly = True
If .Execute < 0 Then Exit Sub
For i = 1 To .FoundFiles.Count - 1
Set myBook = Workbooks.Open(.FoundFiles(i))
With myBook
'
Sheets("Revision").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "VER"
Range("A2").Select
ActiveCell.FormulaR1C1 = "1"
Range("A3").Select
ActiveWorkbook.Save
ActiveWindow.Close
'
End With
Next i
End With
.DisplayAlerts = True
End With
Set myBook = Nothing
Set ThisSheet = Nothing
'
End Sub
I am trying to do the following.
1 Go to a folder on my computer
2 open the first workbook in the folder
3 change the value of two cells on one tab
4 save the workbook
5 close the workbook
6 repeat until every workbook in the folder has been changed
I will be changing the same two cells with the same two new values on the
same tab name for all workbooks. I tried the following but have since found
out the .filesearch no longer works. Any help would be appreciated.
LWhite
Sub MassEdit()
'
' MassEdit Macro
'
Dim myBook As Workbook, _
ThisSheet As Worksheet, _
sDir, sFile As String
sDir = "C:\MTD\"
sFile = "*.xls"
Set ThisSheet = ActiveWorkbook.Sheets(1)
With Application
.DisplayAlerts = False
With .FileSearch
.NewSearch
.LookIn = sDir
.Filename = sFile
.MatchTextExactly = True
If .Execute < 0 Then Exit Sub
For i = 1 To .FoundFiles.Count - 1
Set myBook = Workbooks.Open(.FoundFiles(i))
With myBook
'
Sheets("Revision").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "VER"
Range("A2").Select
ActiveCell.FormulaR1C1 = "1"
Range("A3").Select
ActiveWorkbook.Save
ActiveWindow.Close
'
End With
Next i
End With
.DisplayAlerts = True
End With
Set myBook = Nothing
Set ThisSheet = Nothing
'
End Sub