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

 

[howard]

I have also posted on link below

Macro to Sort Files in a folder i the same order as Col A on Sheet JNLS

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...

www.excelforum.com

 

[HaHoBe]

Hi howard,

instead of

    Dim fileNames() As String

which you would need to ReDim in order to take any values use

    Dim fileNames As Variant

which will create the array for the proper number of files.

I'm not so sure that any renaming of the files and assigning the very same index-number to itself will be helpful - the Windows-Explorer will not be impressed at all. And if you want to relate to the sorting you should make use of a CustomList in order to sort accordingly (see Application.AddCustomList method )

Application.AddCustomList ListArray:=ws.Range("A1:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)

HTH,
Holger

 

[howard]

Thanks for the help Holger

When running the amended code, I now get a run time error "add custom list of object_application failed

Kindly check & amend

 Sub Sort_FileNamesInFolder()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim filePath As String
     Dim fileNames As Variant
    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
    
    Application.AddCustomList ListArray:=ws.Range("A1:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
   ' 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 folders."
End Sub

 

[HaHoBe]

howard,

don't you think it would be a good idea to describe what the code will be used for? I still doubt Excel can manipulate the way the files are listed in a directory (I may be wrong but to my knowledge that is done by the system). It may be a my problem of understanding as English is not my native tongue, I may have missed something which is obvious to anybody else.

As long as you are vague you leave at least me wild guessing what it is you may want. And definitely it's not a good idea to change one codeline to anything that has been guessed but leave the rest of the code referring to the "original" code (which would raise run-time errors as the base for that was commented out once the first RTE has been resolved).

So: more information is needed on my side in order to come up with anything really helpful.

And to remind you: you would need to check if a CustomList with the values does not exist prior to trying to add this list (which would raise a RTE).

Holger

 

[howard]

Many thanks for your reply

I will try and explain what I am trying to achieve. ot sure if this is possible though

I have a list of CSV file names in Col A on sheet "JNL Uploads" I export CSV files to C:\Sales JNLS via VBA. I am looking for code to sort these files in the folder in the same order as those listed on sheet JNL Uploads in Col A

 

[jolivanes]

Open each file starting at the bottom, save it, wait a little, do the same to the next one and so forth till they're all done.
In Explorer, sort on "Date Modified".

 

[howard]

Thanks for the advise Jolivanes, but this will be time consuming. I think the quickest method is to write code to rename the files with a prefix in fronr for eg _001, 002_ etc in theorder one wants these

 

[jolivanes]

Yes, but I did not know that that was allowed.

 

[jolivanes]

Re: "I have a list of CSV file names in Col A on sheet "JNL Uploads""
Just the file name or path included?
If path not included, what is it?
Do these files in Column A have the extension (.CSV) included?
Is there a header in Column A?
What would at any time be the maximum amount of files? (add just a 1 or 01 or 001 or 0001)
If less then 27, will letters (A, B, C, D etc) be an option?