Chip, Works like a charm. Thanks for the code I really appreciate your
help. Excellent code.

Cheers.


"Chip Pearson" wrote in message
...
I didn't notice that you mentioned you were using Excel 2007. The code
I posted previously will work in 2007 and earlier, but will only look
at Excel 2003 files. If you have files in Excel 2007 format or a mix
of 2003 and 2007 files, use the following code instead.


Sub AAA()
Dim FolderName As String
Dim FileName As String
Dim S As String
Dim WB As Workbook
Dim Ext As String

' FolderName is the folder that contains the Excel files.
FolderName = "C:\Test" '<<< CHANGE
' set the current directory to FolderName
ChDrive FolderName
ChDir FolderName
FileName = Dir("*.*")
' Turn off screen updating for speed and aesthetics.
Application.ScreenUpdating = False
' Loop through all files in FolderName.
Do Until FileName = vbNullString
' Get the file extension of FileName.
Ext = Mid(FileName, InStrRev(FileName, "."))
Select Case LCase(Ext)
' Test if FileName is an Excel workbook. Supports
' both Excel 2003 and 2007 file formats.
Case ".xls", ".xlsm", ".xlsx", "xlsb"
' FileName is an Excel workbook. Open it.
Set WB = Workbooks.Open(FileName:=FileName)
' Create the new name from the workbook name.
' Chop off the extension.
S = Left(WB.Name, InStrRev(WB.Name, ".") - 1)
' Rename the first Worksheet to the modified file
' name. Ignore the error that might occur if
' a worksheet by that name already exists.
On Error Resume Next
WB.Worksheets(1).Name = S
On Error GoTo 0
' Save and close the workbook.
WB.Close savechanges:=True
Case Else
' not an Excel workbook
End Select
' Get the next file name from Dir. This will be an
' empty string when all files have be processed.
FileName = Dir()
Loop
' Turn screen updating back on.
Application.ScreenUpdating = True
End Sub


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Thu, 19 Feb 2009 21:51:06 -0800, "CAM"
wrote:

Hello,

I have a many different workbooks that are name differentlly by individual
states for example I have a workbook call Florida I want to have the
worksheet name by the file name How do I do that? Any tips will be
appreciated. I am using excel 2007 and I just don't know the vba code to
do
that. Thank you in advance.

Cheers