Mass rename of worksheets/Workbooks
 

"PaulW" wrote in message
...
Hello Newsgroup Users and Gurus.


You’re kind assistance please.

I’ll be surprised if this can be achieved.
I have over 400 .xls file. Each workbook has one
worksheet.

I have to rename each workbook & each respective
worksheet. Is there anyway of renaming each worksheet on
mass or the easiest way of a completing this task.

All workbooks are in the same folder. I’m using Excel 98
on a WIN-NT machine.

Any help at all would be appreciated.

Kind regards
Paul


You can build a list of all the filenames using the Dir function


Dim MyPath as String, MyName as string, MyList(1000) as String
Dim Mycount as Long, n as Long

' Display the names in C:\ that represent directories.
MyPath = "c:\myxlfiles\" ' Set the path.

MyName = Dir(MyPath & "*.XLS", vbDirectory) ' Retrieve the first entry.

If MyName="" Then Exit Sub

Mycount=1

MyList(Mycount)=MyName

Do While MyName < "" ' Start the loop.
' Ignore the current directory and the encompassing directory.
If MyName < "." And MyName < ".." Then
' Use bitwise comparison to make sure MyName isnt a directory.
If (GetAttr(MyPath & MyName) And vbDirectory) < vbDirectory Then
Mycount=Mycount+1
MyList(Mycount)=MyName
End If
End If
MyName = Dir ' Get next entry.
Loop

Once you have your list open each workbook in turn and
change the sheet names

Dim Mywkb as workbook, Mywksht as Worksheet
Dim NewName as String, NewFileName as String

For n = 1 to Mycount

Set Mywkb = Workbooks.Open(MyList(n))

For Each Mywksht in Mywkb

' Enter your rename code here
Mywksht.Name=NewName
Next

' Enter your rename code here
Mywkb.SaveAs(NewFileName)


Next n

Keith