0

Hi I have drafted the below code, which use the DIR function to loop through all the files and rename them, however this is not carried out in alphabetical order

Can the below code be amended to ensure it is completed in alphabetical order.

    Sub Rename_Files()

Dim name As String
Dim returnaname As String

returnName = ActiveWorkbook.name

Application.ScreenUpdating = True
Application.DisplayAlerts = False
Application.EnableEvents = False

'On Error Resume Next

MyFolder = "G:\Corpdata\STRAT_Information\Open\1. Yot Data (Scoring)\34. Disproportionality Tool\201718 Tool\Local Level Tool\Database_Extract_Tools\Area Files Offences"

MyFile = Dir(MyFolder & "\*.xls")
Do While MyFile <> ""
Workbooks.Open Filename:=MyFolder & "\" & MyFile

name = Left(ActiveWorkbook.name, Len(ActiveWorkbook.name) - 5)
name = name & ("_Offence")

ActiveWorkbook.SaveAs Filename:=name

Windows(returnName).Activate

MyFile = Dir$ 'goes to next entry

Loop

End Sub
nir020
  • 97
  • 1
  • 1
  • 5
  • 3
    I'm pretty sure that `DIR()` has no concept of sorting. – Gareth Oct 22 '18 at 11:52
  • 3
    Possible duplicate of [Does Dir() make any guarantee on the order of files returned?](https://stackoverflow.com/q/4282940/11683) – GSerg Oct 22 '18 at 11:53
  • 4
    As @Gareth says, from [the Official documentation](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/dir-function): *"Because file names are retrieved in no particular order, you may want to store returned file names in an array, and then sort the array."* – Chronocidal Oct 22 '18 at 12:05
  • If you aren't going to supply a path and folder for the saveas, you should probably be using ActiveWorkbook.fullname. –  Oct 22 '18 at 12:36
  • Is there any particular reason you're _opening_ every file then doing a `.SaveAs` and _leaving the file open_ instead of using the [File System Object](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/filesystemobject-object) to [copy](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/copyfile-method) the file? – FreeMan Oct 22 '18 at 12:39
  • Also, does it really matter that the files be processed in alphabetical order? You're renaming everything in the folder, unless your code breaks during the process (in which case you'd better be checking for files that already end in `_Offence` and skipping them), they'll all get processed... – FreeMan Oct 22 '18 at 12:41

0 Answers0