Loop through files in a folder and rename worksheet

[TaskMaster]

Hi all,

Wondering if someone can help me with something. I have a list of csv files in a folder each containing a single tab. Is there a quick way to go into the file and rename the tab to Summary without opening each file and changing manually. Files are saved in \\users\TM\Desktop\Test\

 

[Domenic]

There's really no "sheet name" in a CSV file. A CSV file is simply a text file containing comma separated values. And the worksheet that appears to store the values will always be named the same as the filename. So, for example, if you rename the worksheet, save, close, and reopen the file in Excel, you'll see that the worksheet will in fact show as the filename.

Hope this helps!

 

[TaskMaster]

Hi Domenic, thank makes sense to me. Just out of interest how would this work for a normal excel file?

 

[Domenic]

In order to rename a worksheet, the file needs to be opened. However, you can set screen updating to False, so that you won't see the workbooks opening and closing. So, for example, you would set screen updating to False at the beginning of your code, like this...

Application.ScreenUpdating = False

Then you would set it back to True at the end of your code.

 

[Domenic]

The following code will loop through each file in the specified folder (change accordingly). For each file, it will open it, rename the first sheet as "Summary", Save, and then close the file. Note that if you want to refer to the worksheet by its name (assuming that all workbooks have the same name) instead of index number, replace...

currentWorkbook.Worksheets(1).Name = "Summary"

with

currentWorkbook.Worksheets("Sheet1").Name = "Summary"

Here's the code...

Option Explicit

Sub RenameWorksheets()

    Application.ScreenUpdating = False
    
    Dim pathToFolder As String
    Dim currentFileName As String
    Dim fileCount As Long
    Dim currentWorkbook As Workbook
    
    pathToFolder = "c:\users\tom\desktop\new folder\" 'change the path accordingly
    If Right(pathToFolder, 1) <> "\" Then
        pathToFolder = pathToFolder & "\"
    End If
    
    currentFileName = Dir(pathToFolder & "*.xlsx", vbNormal)
    
    fileCount = 0
    Do While Len(currentFileName) > 0
        Set currentWorkbook = Application.Workbooks.Open(pathToFolder & currentFileName)
        currentWorkbook.Worksheets(1).Name = "Summary"
        currentWorkbook.Close SaveChanges:=True
        fileCount = fileCount + 1
        currentFileName = Dir
    Loop
    
    MsgBox "Number of files amended: " & fileCount
    
    Application.ScreenUpdating = True
    
End Sub

Hope this helps!

 

[TaskMaster]

The following code will loop through each file in the specified folder (change accordingly). For each file, it will open it, rename the first sheet as "Summary", Save, and then close the file. Note that if you want to refer to the worksheet by its name (assuming that all workbooks have the same name) instead of index number, replace...

currentWorkbook.Worksheets(1).Name = "Summary"

with

currentWorkbook.Worksheets("Sheet1").Name = "Summary"

Here's the code...

Option Explicit

Sub RenameWorksheets()

    Application.ScreenUpdating = False
   
    Dim pathToFolder As String
    Dim currentFileName As String
    Dim fileCount As Long
    Dim currentWorkbook As Workbook
   
    pathToFolder = "c:\users\tom\desktop\new folder\" 'change the path accordingly
    If Right(pathToFolder, 1) <> "\" Then
        pathToFolder = pathToFolder & "\"
    End If
   
    currentFileName = Dir(pathToFolder & "*.xlsx", vbNormal)
   
    fileCount = 0
    Do While Len(currentFileName) > 0
        Set currentWorkbook = Application.Workbooks.Open(pathToFolder & currentFileName)
        currentWorkbook.Worksheets(1).Name = "Summary"
        currentWorkbook.Close SaveChanges:=True
        fileCount = fileCount + 1
        currentFileName = Dir
    Loop
   
    MsgBox "Number of files amended: " & fileCount
   
    Application.ScreenUpdating = True
   
End Sub

Hope this helps!

Hi Dom, This is really helpful thank you

 

[Domenic]

You're very welcome, I'm glad I could help.

Cheers!