vba

Search for file using partial name then copy file to another location


Hi I have been having a go at trying to make this work but its been a while since I have used VBA, can anybody shine any light on the below? Any help is greatly appreciated the getfolder line doesnt seem to be working for me.

Private Sub CommandButton1_Click()

Private Sub CommandButton1_Click() 'use Const for fixed values Const SRC_PATH As String = "C:\Users\oliver\Servicing - Job Sheets" Const DEST_PATH As String = "C:\Users\oliver\Servicing Client Files\Wasdell\Job Sheets"

Dim fname As Variant

fname = Dir(SRC_PATH & "Wasdell Europe*")

If Len(fname) > 0 Then
    FileCopy SRC_PATH & fname, DEST_PATH & fname
End If

End Sub


Solution

  • Should work:

    Private Sub CommandButton1_Click()
        'use Const for fixed values 
        Const SRC_PATH As String = "C:\Users\oliver\Servicing - Job Sheets\"
        Const DEST_PATH As String = "C:\Users\oliver\Servicing Client Files\Wasdell\Job Sheets\"
    
        Dim fname As Variant
        
        fname = Dir(SRC_PATH & "*Wasdell Europe*") '<< anywhere in file name
        
        If Len(fname) > 0 Then
            FileCopy SRC_PATH & fname, DEST_PATH & fname
        End If
    
    End Sub