Replacing column value through VBA

[alibawa]

Hi,

I have lets say more than 1000 files. What i want to achieve is two things

1. Change the name of all the files.
2. Change the value of the first column in each file.

For e.g. File Names are like "XYZ_201205" , "XYZ_201204". I want to rename them to "ABC_201205', "ABC_201204".

Similary column A has 100 records (no. of records may vary in each file) and the value is fixed "BH1234" in all the files. I want to replace this value with "AE1234" in all the files.

Appreciate ur help and Thanks so much in advance

 

[JLGWhiz]

Hi,

I have lets say more than 1000 files. What i want to achieve is two things

1. Change the name of all the files.
2. Change the value of the first column in each file.

For e.g. File Names are like "XYZ_201205" , "XYZ_201204". I want to rename them to "ABC_201205', "ABC_201204".

Similary column A has 100 records (no. of records may vary in each file) and the value is fixed "BH1234" in all the files. I want to replace this value with "AE1234" in all the files.

Appreciate ur help and Thanks so much in advance

This will change your file names, but I did not include anything for the records in column A because the parameters are too vague. No sheet specified. It appears that you only want the first two characters changed, which is simple enough, but is it the value that is fixed or the configuration, ie. two alpha, four numeric?

Here is the code to change the file names:

Code:
Sub ChngFileNm()
Dim OldName As String, newName As String, sPath As String
sPath = ThisWorkbook.Path 'Change to path to be searched if other than ThisWorkbook.
If Right(sPath, 1) <> "\" Then sPath = sPath & "\"
SName = Dir(sPath & "*.xls*")
Do
If Left(SName, 4) = "xyz_" Then
OldName = SName
newName = Replace(SName, "xyz", "abc")
Name OldName As newName
End If
SName = Dir
Loop While SName <> ""
End Sub
Code:

 

[JLGWhiz]

Just on the chance that your workbooks are one sheet workbooks and that you have a consistent two alpha prefix of "BH" or whatever it might be, but consistent, here is a procedure that should do the whole job.

Code:
Sub ChngFileNm()
Dim OldName As String, newName As String, sPath As String, c As Range
sPath = ThisWorkbook.Path 'Change to path to be searched if other than ThisWorkbook.
If Right(sPath, 1) <> "\" Then sPath = sPath & "\"
SName = Dir(sPath & "*.xls*")
Do
If Left(SName, 4) = "xyz_" Then
OldName = SName
newName = Replace(SName, "xyz", "abc")
Name OldName As newName
Set wb = Workbooks.Open(newName)
lr = wb.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
For Each c In wb.Sheets(1).Range("A2:A" & lr)
c = Replace(c, "BH", "AE")
Next
End If
SName = Dir
Loop While SName <> ""
End Sub
Code:

 

[JLGWhiz]

Oops! Forgot to close the workbooks. You don't want 100 open workbooks.

Code:
Sub ChngFileNm()
Dim OldName As String, newName As String, sPath As String, c As Range
sPath = ThisWorkbook.Path 'Change to path to be searched if other than ThisWorkbook.
If Right(sPath, 1) <> "\" Then sPath = sPath & "\"
SName = Dir(sPath & "*.xls*")
Do
If Left(SName, 4) = "xyz_" Then
OldName = SName
newName = Replace(SName, "xyz", "abc")
Name OldName As newName
Set wb = Workbooks.Open(newName)
lr = wb.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
For Each c In wb.Sheets(1).Range("A2:A" & lr)
c = Replace(c, "BH", "AE")
Next
wb.Close True
End If
SName = Dir
Loop While SName <> ""
End Sub

 

[alibawa]

Thanks a lot .. Code worked just fine... You saved a lot of time... Thanks again

 

[JLGWhiz]

Thanks a lot .. Code worked just fine... You saved a lot of time... Thanks again

Your're welcome
Regards, JLG