Rename Sheet name for all the excel files within the folder

[akmalkhan]

Hi Gurus,


Need some help with the macro on this subject.


I have a folder with multiple xls and xlsx files in it. All the files have a sheet titled " 1. St Request" within them. I want to rename the sheet name to "1. Suite Request" for all the files.


Please let know if there an efficient way of doing this..


I am looking at alternate modes of accomplishing this and will keep posted .


Thanks in advance.


EDIT: There are other sheets in each of the excel files that don't need to be renamed. Only the sheet with the current title " 1. St Request" will need to be renamed.

 

[sdsurzh]

Hi,


Can you please create a workbook and paste the below code change the path and run the code.


Sub openallworkbookandrename()

Dim path As Variant

Dim excelfile As Variant

Dim i As Integer

MyDir = "C:Documents and SettingsABCMy Documents" 'please update your respective path to load the folder

strPath = MyDir & "files" ' files subdir


excelfile = Dir("*.xls ")

Do While excelfile <> ""

Workbooks.Open Filename:=path & excelfile

excelfile = Dir

Loop


For i = 1 To Workbooks.Count

Workbooks(i).Activate

If ActiveSheet.Name = "1. St Request" Then

ActiveSheet.Name = "1. Suite Request"

Else

MsgBox "Requested sheet not found"

End If

Next

End Sub


Thanks,

SK

 

[akmalkhan]

Hi Suresh,


I set the right path as advised.


something not working right with the macro. its opening multiple excel files and not closing them. approx 100 of them.

 

[sdsurzh]

Hi,


please use the below code


Note:Before running this macro make sure all other workbook are closed.


This macro will open all the files in the specified path and check the sheet name then it will close with or without save deponds upon the sheet name.


Sub openallworkbookandrename()

Dim path As Variant

Dim excelfile As Variant

Dim i As Integer

MyDir = "C:Documents and SettingsABCMy Documents" 'please update your respective path to load the folder

strPath = MyDir & "files" ' files subdir


excelfile = Dir("*.xls ")

Do While excelfile <> ""

Workbooks.Open Filename:=path & excelfile

excelfile = Dir

Loop


For i = 1 To Workbooks.Count

Workbooks(i).Activate

If ActiveSheet.Name = "1. St Request" Then

ActiveSheet.Name = "1. Suite Request"

ActiveWorkbook.Close True

' closes the active workbook and saves


Else

MsgBox "Requested sheet not found"

ActiveWorkbook.Close False

' closes the active workbook without saving

End If

Next

End Sub


Thanks,

SK

 

[akmalkhan]

hi! Unfortunately, its still the same result. I did close all other excel files and followed the above instructions.

Lots of irrelevant excel files are opening up from my C drive, my documents, etc and the macro hangs after opening about 25 files.All opened files remain open as well.

 

[sdsurzh]

Hi Akmalkhan,


I am sorry about that. Let wait for our Excel Ninja to provide the corrrect soultion.


Thanks,

SK

 

[akmalkhan]

Excel Ninja. Please rescue

 

[anupamtiwari05]

Hi akmalkhan


I have written the below code which is working perfectly. Please try it.

[pre]

Sub RenameSheetNames()
Dim NewWb As Workbook
Dim myFileName As String
Dim myFileFolder As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
myFileFolder = "C:UsersTempDesktopDummy" 'Change the path as per your path
Set wb = ThisWorkbook
myFileName = Dir$(myFileFolder, vbNormal)
If myFileName = "" Then
MsgBox "No file"
Exit Sub
End If
Do Until myFileName = ""
Workbooks.Open myFileFolder & myFileName
Set NewWb = ActiveWorkbook
For Each sh In ActiveWorkbook.Worksheets
If sh.Name = "1. St Request" Then
sh.Name = "1. Suite Request"
End If
Next
NewWb.Close True
myFileName = Dir$()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True

[/pre]
End Sub

The above code will check all the files present in the provided path folder.


Thanks & Regards,

Anupam Tiwari

 

[akmalkhan]

Thanks Anupam.


There are 2 errors. ( Compile error. Variable not defined)


Set wb = ThisWorkbook


For Each sh In ActiveWorkbook.Worksheets

 

[shrivallabha]

akmalkhan,


You can either remove option explicit at the top of module or dimension all variables in the code routine and remove redundant items in Anupam's code.

Untested [modified code]

[pre]

Option Explicit
Sub RenameSheetNames()
Dim NewWb As Workbook
Dim myFileName As String, myFileFolder As String
Dim sh As Worksheet

Application.ScreenUpdating = False
Application.DisplayAlerts = False

myFileFolder = "C:UsersTempDesktopDummy" 'Change the path as per your path
myFileName = Dir$(myFileFolder, vbNormal)

If myFileName = "" Then
MsgBox "No file"
GoTo eosub
End If

Do Until myFileName = ""
Workbooks.Open myFileFolder & myFileName
Set NewWb = ActiveWorkbook
For Each sh In ActiveWorkbook.Worksheets
If sh.Name = "1. St Request" Then
sh.Name = "1. Suite Request"
End If
Next
NewWb.Close True
myFileName = Dir$()
Loop

eosub:
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

[/pre]

 

[akmalkhan]

Thanks Shrivallabha..i am trying to get that macro working and i have stumbled across another issue as there is a broken linked document in each of the files in that folder. there is an pop up to " Update/ don't update/cancel" every time the macro wants to open the file. regardless of which option i click on, the sheet name doesn't seem to change.


In the mean while, i stumbled across this tool excelpipe that was available for demo and that seems to do the trick for now. using that tool didn't pop up the linked file error message as well.

 

[shrivallabha]

Again untested:

Modify

Workbooks.Open myFileFolder & myFileName

to

Workbooks.Open myFileFolder & myFileName, UpdateLinks:=False

 

[anupamtiwari05]

Hi akmalkhan,


Please try below code:-

[pre]

Sub RenameSheetNames()
Dim NewWb As Workbook
Dim myFileName As String
Dim myFileFolder As String
Dim sh As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
myFileFolder = "C:UsersAnupamDesktopDummy" 'Change the path as per your path
myFileName = Dir$(myFileFolder, vbNormal)
If myFileName = "" Then
MsgBox "No file"
Exit Sub
End If
Do Until myFileName = ""
Workbooks.Open myFileFolder & myFileName
Set NewWb = ActiveWorkbook
For Each sh In ActiveWorkbook.Worksheets
If sh.Name = "1. St Request" Then
sh.Name = "1. Suite Request"
End If
Next
NewWb.Close True
myFileName = Dir$()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True

[/pre]
End Sub

Thanks & Regards,

Anupam