Move files & folders from one location to another from excel

[aravindhan_3]

Hi,

Need your help for moving multiple files from one folder to another .
eg

In Column A I have older path entered
In Column B file name or folder name that I wanted to move is entered
in Column C I have the new path where those files to be moved

I have prepared the data for about 500 rows.

Can someone help me with the macro to do this task

Thanks for help in advance.

regards
Arvind

[snb]

http://www.vbaexpress.com/forum/show...another-folder

[aravindhan_3]

Hi,

Thanks for sharing, I modified a bit on the code and it works for moving files from one folder to another one

Sub Move_Files_From_One_Folder_To_Another_Folder()
' We define our variables
     Dim FSO As Object
     Dim FromDir As String
     Dim ToDir As String
     Dim FExtension  As String
     Dim FNames As String
     Dim Files As String
     Dim LR As Long
' We initialize our source and destination directories
LR = Sheets("Macro").Range("A" & Rows.Count).End(xlUp).Row
For RW = 2 To LR
     FromDir = Cells(RW, 1).Value
      Files = Cells(RW, 2).Value
     ToDir = Cells(RW, 3).Value
    
'We define here the types of files we wish to move. If you had .txt or .docx or .doc files you can use *.* as the FExtension
     FExtension = Files
'Now assign each file name with extension
     FNames = Dir(FromDir & FExtension)
'Check whether there are any files in the folder so that you can exit if there are no files
     If Len(FNames) = 0 Then
         MsgBox "No files in " & FromDir
         Exit Sub
     End If
 'Most methods return values but FSO returns an object. You cannot simply assign an object to a variable using an equals sign. In Excel VBA you need to use the Set statement
     Set FSO = CreateObject("Scripting.FileSystemObject")
'Now we move the file from the source directory to the destination directory
     FSO.MoveFile Source:=FromDir & FExtension, Destination:=ToDir
    Next RW
 End Sub

how do I modify this move folder also ?

Regards
Arvind

[snb]

See:

http://www.vbaexpress.com/forum/show...l=1#post344156

[aravindhan_3]

Hi,

I was able to get this code worked for files & folders separately.

changed this line FSO.MoveFile to FSO movefolder and this works.
need another help for
1. for the file names, I have to put the file extention also in the excel file like .pdf, .xlsx else it says file doesn't exits. how do I modify this to pick up this.
2. how do I club the code for files and folder together if file is there then move file , if folder is there move folder.

Sub Move_Files_From_One_Folder_To_Another_Folder()
' We define our variables
     Dim FSO As Object
     Dim FromDir As String
     Dim ToDir As String
     Dim FExtension  As String
     Dim FNames As String
     Dim Files As String
     Dim LR As Long
' We initialize our source and destination directories
LR = Sheets("Macro").Range("B" & Rows.Count).End(xlUp).Row
For RW = 15 To LR
     FromDir = Cells(RW, 2).Value & "\"
      Files = Cells(RW, 3).Value
     ToDir = Cells(RW, 4).Value & "\"
'We define here the types of files we wish to move. If you had .txt or .docx or .doc files you can use *.* as the FExtension
     FExtension = Files
'Now assign each file name with extension
     FNames = Dir(FromDir & FExtension)
'Check whether there are any files in the folder so that you can exit if there are no files
     If Len(FNames) = 0 Then
         MsgBox "No files in " & FromDir
         Exit Sub
     End If
 'Most methods return values but FSO returns an object. You cannot simply assign an object to a variable using an equals sign. In Excel VBA you need to use the Set statement
     Set FSO = CreateObject("Scripting.FileSystemObject")
'Now we move the file from the source directory to the destination directory
     FSO.MoveFile Source:=FromDir & FExtension, Destination:=ToDir
    Next RW
 End Sub

for folders

Sub Move_Folder_From_One_Folder_To_Another_Folder()
' We define our variables
     Dim FSO As Object
     Dim FromDir As String
     Dim ToDir As String
     Dim FExtension  As String
     Dim FNames As String
     Dim Files As String
     Dim LR As Long
' We initialize our source and destination directories
LR = Sheets("Macro").Range("B" & Rows.Count).End(xlUp).Row
For RW = 15 To LR
     FromDir = Cells(RW, 2).Value & "\"
      Files = Cells(RW, 3).Value
     ToDir = Cells(RW, 4).Value & "\"
'We define here the types of files we wish to move. If you had .txt or .docx or .doc files you can use *.* as the FExtension
     FExtension = Files
'Now assign each file name with extension
     FNames = Dir(FromDir & FExtension)
 'Most methods return values but FSO returns an object. You cannot simply assign an object to a variable using an equals sign. In Excel VBA you need to use the Set statement
     Set FSO = CreateObject("Scripting.FileSystemObject")
'Now we move the file from the source directory to the destination directory
     FSO.Movefolder Source:=FromDir & FExtension, Destination:=ToDir
    Next RW
 End Sub

[mdmackillop]

Untested but give this a try. TempFiles could refer to a cell on the spreadsheet

Sub Move_Files_From_One_Folder_To_Another_Folder()
     ' We define our variables
    Dim FSO As Object
    Dim FromDir As String
    Dim ToDir As String
    Dim FExtension  As String
    Dim FNames As String
    Dim Files
    Dim TempFiles As String
    Dim LR As Long
    Dim i As Long
    Dim msg As String
     ' We initialize our source and destination directories
    LR = Sheets("Macro").Range("B" & Rows.Count).End(xlUp).Row
    For RW = 15 To LR
        FromDir = Cells(RW, 2).Value & "\"
        Files = Cells(RW, 3).Value
        ToDir = Cells(RW, 4).Value & "\"
         'We define here the types of files we wish to move. If you had .txt or .docx or .doc files you can use *.* as the FExtension
        TempFiles = "*.txt, *.doc, *.xls*"
        Files = Split(TempFiles, ",")
        For i = 0 To UBound(Files)
         'Now assign each file name with extension
        FNames = Dir(FromDir & Files(i))
         'Check whether there are any files in the folder so that you can exit if there are no files
        msg = msg & Files(i) & ", "
        Next i
        If Len(msg) > 0 Then
            MsgBox "No " & msg & " files in " & FromDir
        End If
         'Most methods return values but FSO returns an object. You cannot simply assign an object to a variable using an equals sign. In Excel VBA you need to use the Set statement
        Set FSO = CreateObject("Scripting.FileSystemObject")
         'Now we move the file from the source directory to the destination directory
        FSO.MoveFile Source:=FromDir & FExtension, Destination:=ToDir
    Next RW
End Sub

[snb]

Sub M_snb() 
    CreateObject("scripting.filesystemobject").MoveFolder "G:\OF\__tst", "G:\__tst" 
End Sub

[aravindhan_3]

Hi Mak, the code is giving error saying no files found., but files are there.
Snb,, can you tell me where do I plug your code?

[snb]

Excel VBA Programming For Dummies by John Walkenbach | 9781119077398 | Paperback | Barnes & Noble

[mdmackillop]

Option Explicit


Sub Move_Files_From_One_Folder_To_Another_Folder()
' We define our variables
    Dim FSO As Object
    Dim FromDir As String
    Dim ToDir As String
    Dim FNames As String
    Dim Files
    Dim TempFiles As String
    Dim LR As Long
    Dim i As Long
    Dim RW As Long
    
    'Create this outside the loop
    'Most methods return values but FSO returns an object. You cannot simply assign an object to a variable using an equals sign. In Excel VBA you need to use the Set statement
    Set FSO = CreateObject("Scripting.FileSystemObject")
    ' We initialize our source and destination directories
    LR = Sheets("Macro").Range("B" & Rows.Count).End(xlUp).Row
    For RW = 15 To LR
        FromDir = Cells(RW, 2).Value & "\"
        'Files = Cells(RW, 3).Value
        ToDir = Cells(RW, 4).Value & "\"
        'We define here the types of files we wish to move. If you had .txt or .docx or .doc files you can use *.* as the FExtension
        TempFiles = "*.txt, *.doc, *.xls*"
        Files = Split(TempFiles, ",")
        For i = 0 To UBound(Files)
            'Now assign each file name with extension
            FNames = Dir(FromDir & Files(i))
            'Now we move the file from the source directory to the destination directory
            If FNames <> "" Then FSO.MoveFile Source:=FromDir & FNames, Destination:=ToDir
        Next i
    Next RW
End Sub