How to change data in 300 workbooks

[Soky]

Hello, I have 300 workbooks in one folder, and I want to change cell
a5 in all of them from "\ 04" to "\ 05".

Names of workbooks are 001, 002, 003...... 300

Is there an easy way or will I have to open each of them and change
the data manualy?

Thanks...

 

[Ron de Bruin]

Hi Soky

Copy this code in a workbook outside the folder C:\Data and run it

Try this for all files in the folder C:\Data (it change A5 of the first worksheet)

Sub Test()
Dim mybook As Workbook
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
MyPath = "C:\Data"
ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If

Application.ScreenUpdating = False
Do While FNames <> ""
Set mybook = Workbooks.Open(FNames)

mybook.Worksheets(1).Range("A5").Value = "/05"

mybook.Close True
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

 

[Soo Cheon Jheong]

Hi,

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Option Explicit
Sub TEST()

Dim str_Path As String
Dim str_File As String
Dim str_Full As String
Dim str_Sheet As String
Dim str_New As String
Dim i As Integer

str_Path = "C:\Korea"
str_Sheet = "Sheet1"
str_New = "\ 05"

For i = 1 To 300

str_File = Format(i, "000") & ".xls"
str_Full = str_Path & Application.PathSeparator & str_File

If Dir(str_Full, vbNormal) = "" Then
MsgBox "File not found -- " & str_Full, vbCritical
Else
With Workbooks.Open(str_Full)
.Worksheets(str_Sheet).Range("A5").Value = str_New
.Close SaveChanges:=True
End With
End If

Next

End Sub
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -


--
Regards,
Soo Cheon Jheong
_ _
^¢¯^
--

 

[anthony slater]

Why not select all the sheets (right click on one sheet
and selct all) and then Find and Replace All ?

 

[Soky]

Thanks, this worked great!