Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jun 2004
    Location
    Itasca, Illinois, USA
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Worksheet Names (Excel 2003)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Worksheet Names (Excel 2003)

    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

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Worksheet Names (Excel 2003)

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

    <code>
    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
    </code>
    Legare Coleman

  4. #4
    New Lounger
    Join Date
    Jun 2004
    Location
    Itasca, Illinois, USA
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Worksheet Names (Excel 2003)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •