Move files & folders from one location to another from excel

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

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

Hi,

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

Code:

---

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

See:

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

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.

Code:

---

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

Code:

---


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

---

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

Code:

---


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

---

Code:

---

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

---

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?

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

Code:

---

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

---