excel vba to move files based on partial file name

Question

I have scenario where I need to move files to another location based on partial file name. For eg., "FAI 741727-001 SMS CQ 6U PASS 061217.xlsx" is the file name and I want to create another location as 6U and then move this file to that folder.

I have a code which helps me to move the file to a folder only if I give the full file name. Can someone please help me on this..

Code:

Sub MoveFiles()

Dim SourcePath As String
Dim DestPath As String
Dim FileName As String
Dim LastRow As Long
Dim i As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

For i = 1 To LastRow

    FileName = Cells(i, "B").Value

    If Right(Cells(i, "A").Value, 1) <> Application.PathSeparator Then
        SourcePath = Cells(i, "A").Value & Application.PathSeparator
    Else
        SourcePath = Cells(i, "A").Value
    End If

    If Right(Cells(i, "C").Value, 1) <> Application.PathSeparator Then
        DestPath = Cells(i, "C").Value & Application.PathSeparator
    Else
        DestPath = Cells(i, "C").Value
    End If

    If Dir(SourcePath & FileName) = "" Then
        Cells(i, "D").Value = "Source file does not exist."
    ElseIf Dir(DestPath & FileName) <> "" Then
        Cells(i, "D").Value = "File already exists."
    Else
        Name SourcePath & FileName As DestPath & FileName
        Cells(i, "D").Value = "File moved to new location"
    End If

Next i

End Sub

So, where you want your files to be moved to? Is it the same directory as workbook? Or somehwere else? And what's the name of the catalog based on? — Michał Turczyn
– Michał Turczyn, Commented Jun 29, 2017 at 12:54
@ Michal Turczyn - I would need to create another folder like C:\Users\raja\Desktop\todaysdate\partial file name mentioned in B column — Kelvin
– Kelvin, Commented Jun 29, 2017 at 12:57
@pnuts - Sorry I dont have any clue about batch file/PowerShell? — Kelvin
– Kelvin, Commented Jun 29, 2017 at 13:00

Kostas K. · Accepted Answer · 2017-06-30 13:29:16Z

Loop through cells in Column B, locate files matching the pattern of cell value, create subfolders from today's date & cell value and move files.

Public Sub MoveFiles()
    On Error GoTo ErrProc

    'Today's date folder
    Dim today As String
        today = Format(Date, "dd.mm.yyyy") 'Change this to the format you wish

    Dim r As Range, c As Range
    Set r = Range(Cells(2, 2), Cells(Cells(Rows.Count, "B").End(xlUp).Row, 2)) 'Column B

    Dim filesCollection As Collection, idx As Long
    With CreateObject("Scripting.FileSystemObject")
        For Each c In r
            'Create a Collection of files matching pattern in column B
            Set filesCollection = New Collection

            FillCollectionWithFilePattern obj:=filesCollection, path:=c.Offset(0, [-1]).Value, pattern:=c.Value

            For idx = 1 To filesCollection.Count
                'Validate source exist
                If Len(Dir(.BuildPath(c.Offset(0, [-1]).Value, filesCollection(idx)))) > 0 Then
                    .MoveFile Source:=.BuildPath(c.Offset(0, [-1]).Value, filesCollection(idx)), _
                              Destination:=.BuildPath(PathFromNewFolders(c.Offset(0, [-1]).Value, today, c.Value), filesCollection(idx))
                End If
            Next idx
            Set filesCollection = Nothing
        Next c
    End With

    MsgBox "Completed.", vbInformation

Leave:
    Set filesCollection = Nothing
    On Error GoTo 0
    Exit Sub

ErrProc:
    MsgBox Err.Description, vbCritical
    Resume Leave
End Sub

'Find files matching pattern and add to Collection
Private Sub FillCollectionWithFilePattern(obj As Collection, ByVal path As String, pattern As String)

    Dim strFile As String
        strFile = Dir(AddPathSeparator(path) & "*" & pattern & "*.xlsx")

    Do While Len(strFile) > 0
        obj.Add strFile
        strFile = Dir
    Loop
End Sub

'Creates a new folder (if not exists) for each argument
Public Function PathFromNewFolders(ByVal path As String, ParamArray args() As Variant) As String

    path = AddPathSeparator(path)

    Dim idx As Integer
    For idx = LBound(args) To UBound(args)
        If Len(Dir(path & args(idx), vbDirectory)) = 0 Then MkDir path & args(idx)
        path = path & args(idx) & "\"
    Next idx

    PathFromNewFolders = path
End Function

'Adds PathSeparator '\' to the end of path if mising
Private Function AddPathSeparator(ByVal path As String) As String
    path = Trim(path)
    If Right(path, 1) <> "\" Then path = path & "\"
    AddPathSeparator = path
End Function

ASH · Accepted Answer · 2017-06-29 15:54:14Z

0

The copy part should be pretty simple. Check out the script below.

Sub Copy_Folder()
'This example copy all files and subfolders from FromPath to ToPath.
'Note: If ToPath already exist it will overwrite existing files in this folder
'if ToPath not exist it will be made for you.
    Dim FSO As Object
    Dim FromPath As String
    Dim ToPath As String

    FromPath = "C:\Users\Ron\Data"  '<< Change
    ToPath = "C:\Users\Ron\Test"    '<< Change

    'If you want to create a backup of your folder every time you run this macro
    'you can create a unique folder with a Date/Time stamp.
    'ToPath = "C:\Users\Ron\" & Format(Now, "yyyy-mm-dd h-mm-ss")

    If Right(FromPath, 1) = "\" Then
        FromPath = Left(FromPath, Len(FromPath) - 1)
    End If

    If Right(ToPath, 1) = "\" Then
        ToPath = Left(ToPath, Len(ToPath) - 1)
    End If

    Set FSO = CreateObject("scripting.filesystemobject")

    If FSO.FolderExists(FromPath) = False Then
        MsgBox FromPath & " doesn't exist"
        Exit Sub
    End If

    FSO.CopyFolder Source:=FromPath, Destination:=ToPath
    MsgBox "You can find the files and subfolders from " & FromPath & " in " & ToPath

End Sub

Now, for the part where you need to find characters in a string, can't you just do something like this.

=MID(A1,FIND("CQ",A1,1)+3,2)

Fill down to pick up everything.

answered Jun 29, 2017 at 15:54

ASH

20.5k28 gold badges115 silver badges247 bronze badges

2 Comments

Kelvin Over a year ago

@ ry guy72 - Thanks for the code. But the problem is I will have more than 1K files with different names. So it would be a difficult task to use this method. the values mentioned in the B column is the unique ones and I need to segregate and move them based on those values.

ASH Over a year ago

There must be some kind of pattern in the data that you can exploit. I'm not close to your data, so I don't know what the pattern looks like. Think to yourself, how do YOU know what to do? What is YOUR logic. Leverage that knowledge.

Collectives™ on Stack Overflow

excel vba to move files based on partial file name

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related