Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HOW DO I COPY AND RENAME A WORKBOOK LINKED TO OTHER WORKBOOKS | Excel Discussion (Misc queries) | |||
Mass add of a sheet to multiple workbooks | Excel Discussion (Misc queries) | |||
Mass Change Across Many WorkBOOKS | Excel Discussion (Misc queries) | |||
Mass rename of worksheets/Workbooks | Excel Programming | |||
Mass rename of worksheets/Workbooks | Excel Programming |