[RESOLVED] Renaming Worksheets

[brucevde]

This might give you some ideas

Dim oExcel As Excel.Application
    Dim oBook As Excel.Workbook
    Dim oSheet As Excel.Worksheet
    
    Set oExcel = New Excel.Application
    Set oBook = oExcel.Workbooks.Open("C:\projects\testing.xls")
    oExcel.Visible = True
    
    For Each oSheet In oBook.Worksheets
        'assumes cell A23 is a valid date 
        'The Sheet Name cannot contain the characters \ / ? * [ ]
        oSheet.Name = Format$(oSheet.Cells(23, 1), "dd-MMM-yyyy") 
    Next
    
    oBook.Close True
    Set oBook = Nothing
    
    oExcel.Quit
    Set oExcel = Nothing

[Danno]

It doesn't like this line of your code:

oSheet.Name = Format$(oSheet.Cells(23, 1), "dd-MMM-yyyy")

It produces the same error messages I've been getting about bad spelling and/or illegal characters in the worksheet name. The weird thing is that it will work if all you want to do is rename the last worksheet because you can use the (sheet.count) code. Excel just seems to reject the automatic renaming of any other sheet except the last one. I know I'm doing something wrong, just don't know what.

Danno