Unique Variable Name for File

[MichaelFa]

I have 390 folders with the same file name contained in each one (index.txt). The following code will rename all of the files in the folders with the same name. I need to insert a unique variable as part of the file name**. The goal is to concantonate the files and import into Excel. I "borrowed" the code and have no experience with coding or variables.

**newName = "\myNewFileName" & ".txt" -- Line 19 on the code below


<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Sub RenAllFilesInclSubFold()


Dim fso As Object, fold As Object, fFile As Object
Dim fPath As String, fName As String, newName As String


fPath = "C:\temp\OnBaseBackup" ' ----- initial folder that contains all the subfolders
cnt = ""


'ren files in subfolders
Set fso = CreateObject("Scripting.FileSystemObject")
Set fold = fso.GetFolder(fPath)


For Each fFile In fold.subfolders


'the files i want to rename and move, start with "inde"
fName = Dir(fFile.Path & "\inde*", vbNormal)


Do While fName <> ""
newName = "\myNewFileName" & ".txt"
Name fFile.Path & "" & fName As fFile.Path & newName
fso.MoveFile Source:=fFile.Path & newName, Destination:="C:\temp\OnBaseResults" 'this code will not create the new folder
fName = Dir
Loop
Next
End Sub

[Paul_Hossler]

You can start with something like this

It adds a sequential number to the file name in the Move

Option Explicit
Sub RenAllFilesInclSubFold()
    Dim oFSO As Object, oFolder As Object, oFile As Object
    Dim sPath As String, sName As String, sNewName As String
    
    Dim N As Long
    
    sPath = "C:\temp\OnBaseBackup" ' ----- initial folder that contains all the subfolders
    
    'ren files in subfolders
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.GetFolder(sPath)
    
    N = 1
    For Each oFile In oFolder.subfolders
        'the files i want to rename and move, start with "inde"
        sName = Dir(oFile.Path & "\inde*", vbNormal)
    
        Do While sName <> ""
            sNewName = sName & "-" & Format(N, "00000") & ".txt"
            Name oFile.Path & "" & sName As oFile.Path & sNewName
            oFSO.MoveFile Source:=oFile.Path & "\" & sNewName, Destination:="C:\temp\OnBaseResults" 'this code will not create the new folder
            N = N + 1
            sName = Dir
        Loop
    Next
    
End Sub