renaming multiple excel files

[pauliethegull]

Hi,

Feeling about foolish as at my age your suppose to know these things.......

Anyway, Im trying to create a folder with 4200 excel files, the files are identical but I need to name the files numerically, ie: start at 2300 and end at 6500. I am presently renaming them individually which is taking ages.

Is there anyway I can change the name of them quicker?

Thanks

 

[daverunt]

Hi,

This works for me but there are no guarantees!
Give it a small test first then change the numbers to suit.

This sub is to get the file names in the directory into Sheet1 column A

Sub DirList()
Const ListDir = "C:\junk\"
Sheets("Sheet1").Activate
If Not Dir(ListDir & "*.xls") = "" Then
Flist = Dir(ListDir & "*.xls")
R = 1
Do Until Flist = ""
Cells(R, 1).value = Flist
R = R + 1
Flist = Dir
Loop
End If
End Sub

This sub is to put a number into Sheet1 column B with.xls extension.

4401.xls, 4402.xls,...4409
You'll need to change this to 2300 to 6500 once it's tested.

Sub AddNewName()
Sheets("Sheet1").Activate
j = 1
For i = 4401 To 4410
Range("B" & j).value = i & ".xls" 
j = j + 1
Next i
End Sub

This renames the files from names in column A to column B in the order they are listed in A. Again you will need to set the upper number limit on the files 1 To 4200 once tested.

Sub RenameFiles()
Sheets("Sheet1").Activate
For i = 1 To 10
OldFileName = Range("A" & i).value
NewFileName = Range("B" & i).value
If Not Dir(OldFileName) = "" Then Name OldFileName As NewFileName
Next
End Sub

If you just want to create a load of workbooks that are numbered.
This will create 4200.xlx to 4210.xls in C:\junk

Sub Create_WB()
ChDir "C:\Junk"
For i = 4200 To 4210
 
strNewWBName = i & ".xls"
Workbooks.Add
ActiveWorkbook.SaveAs strNewWBName
 
ActiveWorkbook.Close
Next i
End Sub

 

[pauliethegull]

Hey, Thanks so much!!

Ive created a test run to create a load of workbooks that are numbered.

however when I open them its says "the file you are trying to open "4210.xls" is in different format than specified by the file extension. (all workbooks have the message)

I cant find what I'm doing wrong any ideas?

Thanks

 

[ak_excel]

Try this :

ActiveWorkbook.SaveAs strNewWBName, fileformat:= -4142

If you use Excel 2007

 

[daverunt]

Hi,


yep same problem here. However the file does open in Excel.
I am running XL2007 I assume from the problem you are too?

If I change the extension to a 2007 extension i.e .xlsx this problem does not occur.

Perhaps one of the MVPs can explain this?


Try:

strNewWBName = i & ".xlsx"

 

[pauliethegull]

Hi,

yep, I changed it to ".xlsx" and it worked a treat. nice one,

yet I'm still unable to produce an exact copy of the Workbook template Im trying to reproduce. I'm placing the file name in the Sub u , obviously this isnt correct.

ideas?

Cheers

 

[ak_excel]

This problem occurs because if you want to save the file in a previous format you have to specify the file format number.
refer to my previous post for it

 

[daverunt]

Hi change the extensions to .xlsx throughout the Subs
or use ak_excels format fileformat:= -4142.

I misunderstood your post in that I thought the files were already created and you were renaming them manually. If that isn't the case then do the following.

By the time I figured out how to copy your template file 4200 times and rename it at the same time you can do this - took me less than 3 minutes

My Excel isn't that great. So we go for the brutal easy method.

1. Put your template file in C:\junk
2. Copy it
3. CTRL A and copy both files
4. Rename files 1.xlsx to 4.xlsx (important - filenames get long)
5. CTRL A and Copy/paste
6. Repeat 12 times and you have 4096 copies of your file
7. Copy and paste the first 104 files and you'll have 4200


Run Sub DirList()

For i = 2300 To 6500 in these 2 subs

Sub AddNewName()
Run Sub RenameFiles()

 

[pauliethegull]

Thanks Dave,

Your right my post title was misleading, sorry about that!!

You have saved me so much time and embarrassment at work, I've been hiding my excel one by one approach all day!!! thank you again! the ****** youngsters at work dont understand that all I had at school to learn on was a Spectrum ZX,!!

looks like im in need af night school classes!!

Cheer mate

 

[Ruddles]

Here's my attempt. Set MyPath to the location where your files are, set MyRoot to the filename you want to use - the one shown will start with newfilename4200 - and set FileSpec to specify which files you want to rename. Note that the files are not opened, merely renamed.

Option Explicit
 
Sub LoopThroughFolder()
 
  Const MyPath As String = "[COLOR=blue][B]C:\TEMP\[/B][/COLOR]"
  Const MyRoot As String = "[COLOR=blue][B]newfilename[/B][/COLOR]"
  Const FileSpec As String = "[COLOR=blue][B]*.xls*[/B][/COLOR]"
  
  Dim MyFile As String
  Dim FileRoot As String
  Dim FileExt As String
  Dim SequenceNo As Integer
  Dim iDot As Integer
  
  SequenceNo = 4200
  MyFile = Dir(MyPath & FileSpec)
  Do While Len(MyFile) > 0
    iDot = InStrRev(MyFile, ".")
    FileRoot = Left(MyFile, iDot - 1)
    FileExt = Mid(MyFile, iDot + 1)
    If IsNumeric(Right(FileRoot, 4)) Then
      Name MyPath & MyFile As MyPath & FileRoot & Right(FileRoot, 4) & "." & FileExt
      FileRoot = FileRoot & Right(FileRoot, 4)
    End If
    Name MyPath & FileRoot & "." & FileExt As MyPath & MyRoot & CStr(SequenceNo) & Mid(MyFile, iDot)
    MyFile = Dir
    SequenceNo = SequenceNo + 1
  Loop
    
End Sub

Test on a copy of your real folder!