Macro to sort CVS files in C:\JNL Uploads

[howard]

I have list CSV files in a specific order in Col A on sheet "JNL Uploads"

The CSV files are exported to C:\Sales JNLS" and I would like these to be sorted in the same order as in Col A on sheet "JNL Uploads"

I have tried to write code to do this, but get a run time eerror type mismatch and the code below is highlighted

  fileNames = WorksheetFunction.Transpose(ws.Range("A1:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row).Value)

It would be appreciated if someone could kindly check my code and amend it

 Sub SortCVSFiles()
 
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim filePath As String
    Dim fileNames() As String
    Dim i As Long, j As Long, k As Long
    Dim temp As String
    
    ' Set workbook and worksheet variables
    Set wb = ActiveWorkbook
    Set ws = wb.Sheets("JNL Uploads")
    
    ' Get file names from column A and convert to strings
    fileNames = WorksheetFunction.Transpose(ws.Range("A1:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row).Value)
    For i = LBound(fileNames) To UBound(fileNames)
        fileNames(i) = CStr(fileNames(i))
    Next i
    
    ' Sort the file names array
    For i = LBound(fileNames) To UBound(fileNames) - 1
        For j = i + 1 To UBound(fileNames)
            If StrComp(fileNames(i), fileNames(j), vbTextCompare) > 0 Then
                temp = fileNames(i)
                fileNames(i) = fileNames(j)
                fileNames(j) = temp
            End If
        Next j
    Next i
    
    ' Loop through the sorted file names and move the files
    filePath = "C:\Sales JNLS\"
    For k = LBound(fileNames) To UBound(fileNames)
        If Len(Dir(filePath & fileNames(k))) > 0 Then
            Name filePath & fileNames(k) As filePath & "temp"
            Name filePath & fileNames(k) As filePath & fileNames(k)
            Name filePath & "temp" As filePath & fileNames(k)
        End If
    Next k
    
    ' Notify user that sorting is complete
    MsgBox "File names sorted in C:\Sales JNLS folder."
End Sub

 

[jolivanes]

Try this. To be safe, use temporary folders to make sure it works as required.
Column A has a header in the first row and file names do not have the path but do have the .CSV extension.
Change references where needed.

Sub For_Howard()
Dim sh1 As Worksheet
Dim path1 As String, path2 As String
Dim files
Dim i As Long
    Set sh1 = Worksheets("JNL Uploads")
    path1 = "C:\AAAAAA\AAAAA\"    '<----- Folder where files are stored. Change as required.
    path2 = "C:\Sales JNLS\"
    files = sh1.Range("A2:A" & sh1.Cells(Rows.Count, 1).End(xlUp).Row).Value
        For i = 1 To UBound(files)
            Name path1 & files(i, 1) As path2 & Format(i, "0000") & "_" & files(i, 1)
        Next i
End Sub

 

[howard]

Hi Jolivanes

The File path where these files reside are : C\Sales JNLS
The files in Column A have the extension CV included
There is no header in Col A

The current number of files is 20 (should not exceed 30)

Letter A, B, C etc is certainly an option


I have attached a link to my file below

Macro to rename Files in Folder.xlsm

Shared with Dropbox

www.dropbox.com

 

[howard]

Thanks for you code.I get a run time error -File not fouund

 Name path1 & files(i, 1) As path2 & Format(i, "0000") & "_" & files(i, 1)

Kindly check & amend

 

[jolivanes]

In Post #1 you mention exported but you just want to rename the files, is that correct?
"The files in Column A have the extension CV included". I assume that that is a slip of the finger. Should be CSV.

Did you change the path to what it should be with the backslash included?

 

[jolivanes]

If numerical additions are as in your example, you don't need Column B and Column C.

Sub For_Howard_Version_2()
Dim sh1 As Worksheet, files, path As String, i As Long
Set sh1 = Worksheets("JNL Uploads")    '<----- Check for proper spelling of sheet name
files = sh1.Range("A1:A" & sh1.Cells(Rows.Count, 1).End(xlUp).Row).Value
path = "C:\Sales JNLS\"    '<----- Check for proper spelling
    For i = LBound(files) To UBound(files)
        On Error Resume Next    '<----- Continue if file does not exist
            Name path & files(i, 1) As path & Format(i, "000") & "_" & files(i, 1)
        On Error GoTo 0
    Next i
End Sub

 

[howard]

Thanks Jolivanes-Your code works 100%

 

[jolivanes]

Thanks for the update Howard and good luck.