Вариант:
[vba]Код
Sub Change_Sheets_Name()
Dim sPath$, sXL$
sPath = "C:\путь_до_файлов"
sPath = sPath & IIf(Right(sPath, 1) = Application.PathSeparator, "", Application.PathSeparator)
Application.ScreenUpdating = 0
Application.DisplayAlerts = 0
sXL = Dir(sPath & "*.xls*")
Do While sXL <> ""
Workbooks.Open sPath & sXL
ActiveWorkbook.ActiveSheet.Name = "tmpQuery1"
ActiveWorkbook.Close -1
sXL = Dir
Loop
Application.DisplayAlerts = -1
Application.ScreenUpdating = -1
End Sub
[/vba]
PS Можно приделать FileDialog, если надо