Excel Worksheet Names (Excel 2003)

Topic

New Reply

I am trying to “reach into” multiple excel files and change the worksheet to a common name (i.e. “data”). I want to automate this process so that it can be executed from one excel file and runs as a loop until all files in a target directory have been changed. I can do this in a macro that will affect one file at a time but would like to modify to run as a loop. All files have only one sheet so I don’t think I need to select the ActiveSheet.

Dim CWB As Workbook
Dim DWB As Workbook
Set CWB = ActiveWorkbook
DestinationBook = Application.GetOpenFilename(“All Excel Files, *.xls”)
Workbooks.Open DestinationBook
Set DWB = ActiveWorkbook
CWB.Activate
Sheets(“exc56”).Select
Sheets(“exc56”).Name = “data”

Thanks in advance,
Bill

Reply | Quote

Replies

Copy the following code into a standard module. BrowseFolder is a utility function used in the macro ProcessFiles.

‘ BrowseFolder from Don Ceraso:

Public Function BrowseFolder(Optional Title As String = “Select a Folder”, _
Optional RootFolder As Variant) As String
On Error Resume Next
BrowseFolder = CreateObject(“Shell.Application”).BrowseForFolder _
(0, Title, 0, RootFolder).Items.Item.Path
End Function

Sub ProcessWorkbooks()
Dim strFolder As String
Dim strFile As String
Dim wbk As Workbook

On Error GoTo ErrHandler

strFolder = BrowseFolder
If strFolder = “” Then Exit Sub

If Not Right(strFolder, 1) = “” Then
strFolder = strFolder & “”
End If

strFile = Dir(strFolder & “*.xls”)
Do While Not strFile = “”
Set wbk = Workbooks.Open(strFolder & strFile)
wbk.Sheets(“exc56”).Name = “data”
wbk.Close SaveChanges:=True
strFile = Dir
Loop

ExitHandler:
Set wbk = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

Reply | Quote

You will need to change the path in the code below to where your workbooks are located:


Public Sub RenameWB()
Dim oTgt As Workbook
Dim strPath As String, strFile As String
strPath = "C:Work"
strFile = Dir(strPath & "*.xls", vbNormal)
Do While strFile ""
Set oTgt = Workbooks.Open(strPath & strFile)
oTgt.Worksheets(1).Name = "data"
oTgt.Close
Loop
End Sub


Reply | Quote

Thanks Hans and Legare.

I tried both sets of code and had better luck with Hans’. I only made one modification to the code and it worked flawlessly. Pasted in below for reference:

‘ BrowseFolder from Don Ceraso:

Public Function BrowseFolder(Optional Title As String = “Select a Folder”, _
Optional RootFolder As Variant) As String
On Error Resume Next
BrowseFolder = CreateObject(“Shell.Application”).BrowseForFolder _
(0, Title, 0, RootFolder).Items.Item.Path
End Function

Sub ProcessWorkbooks()
Dim strFolder As String
Dim strFile As String
Dim wbk As Workbook

On Error GoTo ErrHandler

strFolder = BrowseFolder
If strFolder = “” Then Exit Sub

If Not Right(strFolder, 1) = “” Then
strFolder = strFolder & “”
End If

strFile = Dir(strFolder & “*.xls”)
Do While Not strFile = “”
Set wbk = Workbooks.Open(strFolder & strFile)
wbk.ActiveSheet.Name = “data”
wbk.Close SaveChanges:=True
strFile = Dir
Loop

ExitHandler:
Set wbk = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler

Reply | Quote