Consulting

Results 1 to 4 of 4

Thread: Sorting files in folders per name

  1. #1
    VBAX Regular
    Joined
    Jul 2023
    Posts
    14
    Location

    Sorting files in folders per name

    I have thousands of files with names consisted of six capital letters, six numbers, dot, six numbers, dot, xlsx (I.E. SMIJOH140469.040823). What I would like to do is to have all of them sorted in subfolders made automatically according to first twelve symbols (I.E. SIMJOH140469), AND I would like ALL the files starting with same twelve symbols to end-up in the same subfolder. (I.E. SMIJOH140469.040823, SMIJOH140469.080223, SMIJOH140469.030922 to folder SMIJOH140469.
    Can it be done?

  2. #2
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    348
    Location
    See if this gets you started:
    Sub CopyFiles()
        Dim strFolder As String, strFile As String, strPrefix As String
        Dim colFiles As New Collection, varItem As Variant, fso As FileSystemObject
        Set fso = CreateObject("scripting.filesystemobject")
        strFolder = "C:\folderpath"
        strFile = Dir(strFolder & "\*.*")
        Do While strFile <> vbNullString
            If strPrefix <> Left(strFile, 12) Then
                strPrefix = Left(strFile, 12)
                colFiles.Add strPrefix
            End If
            strFile = Dir
        Loop
        For Each varItem In colFiles
            If Dir(strFolder & "\" & varItem) = "" Then MkDir strFolder & "\" & varItem
            fso.CopyFile strFolder & "\" & varItem & "*", strFolder & "\" & varItem
        Next
    End Sub
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    VBAX Regular
    Joined
    Jul 2023
    Posts
    14
    Location

    compile error: user-defined type not defined (in script marked in red)

    Quote Originally Posted by June7 View Post
    See if this gets you started:
    Sub CopyFiles()
        Dim strFolder As String, strFile As String, strPrefix As String
        Dim colFiles As New Collection, varItem As Variant, fso As FileSystemObject
        Set fso = CreateObject("scripting.filesystemobject")
        strFolder = "C:\folderpath"
        strFile = Dir(strFolder & "\*.*")
        Do While strFile <> vbNullString
            If strPrefix <> Left(strFile, 12) Then
                strPrefix = Left(strFile, 12)
                colFiles.Add strPrefix
            End If
            strFile = Dir
        Loop
        For Each varItem In colFiles
            If Dir(strFolder & "\" & varItem) = "" Then MkDir strFolder & "\" & varItem
            fso.CopyFile strFolder & "\" & varItem & "*", strFolder & "\" & varItem
        Next
    End Sub
    Many thanks for your help.
    I am not very experienced, so please ignore my ignorance as much as you can.
    I have started your script as macro from Excel.
    I have changed folder path to "C:\Users\User\Desktop\Sort"
    Compile error: user-defined type not defined (in script marked in red)
    What can I do?

  4. #4
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    348
    Location
    Options:

    1. change code to use late binding: Dim fso As Object

    2. set VBA reference library Microsoft Scripting Runtime for early binding

    Review https://www.automateexcel.com/vba/us...-in-excel-vba/

    Edit code to use your folder path in place of "folderpath"
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •