Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename Worksheet tab
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename Worksheet tab
I'm not entirely sure what you are looking for (and a bit of
punctuation in the message would have been helpful), but you might be able to use or adapt the following code. It looks in the folder named "C:\Test" (change this to the appropriate folder), and pulls out all the Excel workbooks (those files ending with ".xls"). It then renames the first worksheet in each workbook with the name of the workbook book (sans the "xls" extension). So, for example, the first worksheet in the workbook "Florida.xls" will be renamed to "Florida". Sub AAA() Dim FolderName As String Dim FileName As String Dim S As String Dim WB As Workbook ' FolderName is the folder that contains the Excel files. FolderName = "C:\Test" '<<< CHANGE ' set the default directory to FolderName ChDrive FolderName ChDir FolderName FileName = Dir("*.xls") ' turn off screen updating for speed and aesthetics Application.ScreenUpdating = False ' loop through all XLS files Do Until FileName = vbNullString ' open the workbook Set WB = Workbooks.Open(FileName:=FileName) ' create the new name from the workbook name S = Left(WB.Name, InStrRev(WB.Name, ".") - 1) ' rename the first worksheet ignore the error ' that might occur if a sheet with the name ' in S already exists. On Error Resume Next WB.Worksheets(1).Name = S On Error GoTo 0 ' save and close the workbook WB.Close savechanges:=False ' get the next file name. this will be ' vbNullString 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename Worksheet tab
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename Worksheet tab
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't rename worksheet | Excel Discussion (Misc queries) | |||
Using information from one worksheet, to rename inserted worksheet | Excel Worksheet Functions | |||
Rename Worksheet | Excel Programming | |||
Rename the worksheet | Excel Discussion (Misc queries) | |||
Add and rename a worksheet | Excel Programming |