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/showthread.php?56260-Copy-complete-folder-to-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/showthread.php?56260-Copy-complete-folder-to-another-folder&p=344156&viewfull=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 (http://www.barnesandnoble.com/w/excel-vba-programming-for-dummies-john-walkenbach/1101874584)

[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