Oops, sorry about that - forgot to add the, quite important, part to close the workbooks.
I've added it in the code below.
VBA Code:
Option Explicit
Public Function GetFolder(Optional OpenAt As String, Optional strTitle = "Please select folder") As String
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.InitialFileName = OpenAt
.Title = strTitle
.Show
If .SelectedItems.Count <> 0 Then
GetFolder = .SelectedItems(1)
End If
End With
End Function
Sub ChangeTabName()
Dim wb As Workbook
Dim objFSO As Object
Dim objFile As Object
Dim objFolder As Object
Dim strFolder As String
strFolder = GetFolder
If strFolder = "" Then
MsgBox "No folder selected!", vbInformation, "No folder selected"
End If
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strFolder)
For Each objFile In objFolder.Files
If objFile.Type Like "*Excel*" Then
Set wb = Workbooks.Open(objFile.Path)
wb.Sheets(1).Name = wb.Sheets(1).Range("B7").Value
wb.Close SaveChanges:=True
End If
Next objFile
End Sub