Move multiple files with wild card name

**randell.graybill** · 01-09-2010, 01:30 AM

Hi all I was wondering if anyone knew how to move multiple files from one directory to another? The thing is the file name will always have the word rotation in it but the other part will not be the same and I can't just copy all excel files because that won't work either as not all files in the directory should be copied only those with the name Rotation in them should be moved.

Below is the code that I tried.

Dim UserName As String
UserName = Environ("USERNAME")

Dim fso
Dim sfol As String, dfol As String
sfol = "C:\Documents and Settings\" & UserName & "\My Documents\Downloads" ' change to match the source folder path
dfol = "U:\Rotations\" & SetDate ' change to match the destination folder path
Set fso = CreateObject("Scripting.FileSystemObject")
On Error Resume Next
If Not fso.FolderExists(sfol) Then
    MsgBox sfol & " is not a valid folder/path.", vbInformation, "Invalid Source"
ElseIf Not fso.FolderExists(dfol) Then
    MsgBox dfol & " is not a valid folder/path.", vbInformation, "Invalid Destination"
Else
    fso.MoveFile (sfol & "\* Rotation.xls"), dfol ' Change "\*.*" to "\*.xls" to move Excel Files only
End If
If Err.number = 53 Then MsgBox "File not found"

**randell.graybill** · 01-09-2010, 01:35 AM

I removed the on error resume next and it gives me the error message permission denied but I can't see why I am the owner of the folder, file and have full control over both.

**Leith Ross** · 01-10-2010, 03:47 AM

Hello Randell,

The code you have is for VB.net. I rewrote the code for Excel 2003 using VBA.

Sub MoveFiles()

  Dim dfoldername As String
  Dim fso As Object
  Dim fsoFile As Object
  Dim sFileName As String
  Dim sFilePath As String
  Dim UserName As String
  
    UserName = Environ("USERNAME")

    sFileName = "\* Rotation.xls"
    sFilePath = "C:\Documents and Settings\" & UserName & "\My Documents\Downloads" ' change to match the source folder path
    dfoldername = "U:\Rotations\" & SetDate ' change to match the destination folder path
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    
      If Dir(sFilePath, vbDirectory) = "" Then
         MsgBox sFilePath & " is not a valid folder/path.", vbInformation, "Invalid Source"
         Exit Sub
      End If
      
      If Dir(dfoldername, vbDirectory) = "" Then
         MsgBox dfoldername & " is not a valid folder/path.", vbInformation, "Invalid Destination"
         Exit Sub
      End If
      
        sFileName = Dir(sFilePath & sFileName)
        If sFileName = "" Then
           MsgBox "No files were found."
           Exit Sub
        End If
        
        Do While sFileName <> ""
          Set fsoFile = fso.GetFile(sFileName)
          fsoFile.Move dfoldername
          sFileName = Dir()
        Loop
        
    Set fso = Nothing
    Set fsoFile = Nothing
   
End Sub