Macro to open 100 sheets in a folder and rename sheets with a specific name

[Bob S]

Hello,

I am looking to write a macro that will open excel sheets that are located in the following folder. M:\Files\June Files\Current

There are 100 .xlsx files in this folder, with multiple sheets. Some of the files have a sheet named Repeater-5 and others have Repeater-6. So I would like to create a macro to rename all the sheets to Repeater. Any assistance would be appreciated.

Thanks


▬▬▬▬▬▬▬▬▬ Mod edit : thread moved to appropriate forum !

​

 

[Marc L]

Hi !

Use Dir function to scan workbooks, to see in VBA inner help …​

 

[Bob S]

Here's the code that I am using. The Macro isn't finding the worksheet names Part C D Repeater Section-6 Any suggestions??

Sub RenameTab_Revised()
Dim ws As Worksheet
strPath = "M:\GIDS\Test\"
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.DisplayAlerts = False
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFso.GetFolder(strPath)
For Each objFile In objFolder.Files
If objFso.GetExtensionName(objFile.Path) = "xlsx" Then
Set objWorkbook = objExcel.Workbooks.Open(objFile.Path)
On Error Resume Next
Set ws = Sheets("Part C D Repeater Section-6")
On Error GoTo 0
If ws Is Nothing Then
MsgBox "The Worksheet does not exist in " & objWorkbook.Name
objWorkbook.Close True
Else
Sheets("Part C D Repeater Section-6").Select
Sheets("Part C D Repeater Section-6").Name = "Part C D Repeater Section"
objWorkbook.Close True
End If
End If
Next
End Sub

 

[Marc L]

As per forum rules thanks to use code tags or appropriate icon ‼
A Logic error of Excel object model in your VBScript code …

A Dir VBA procedure according to the initial post :​

Sub Demo()
       Const D = "M:\Files\June Files\Current\"
         Dim F$, Ws As Worksheet
             Application.ScreenUpdating = False
             F = Dir(D & "*.xlsx")
    Do Until F = ""
        With Workbooks.Open(D & F)
            For Each Ws In .Worksheets
                If Ws.Name Like "Repeater-[56]" Then Ws.Name = "Repeater": Exit For
            Next
               .Close Not .Saved
        End With
             F = Dir
    Loop
             Set Ws = Nothing
             Application.ScreenUpdating = True
             MsgBox "Done …", vbInformation
End Sub

Do you like it ? So thanks to click on bottom right Like !
​