Macro to move files from one directory to another

[Guest]

I want to create a macro to move files from one server to another.
Basically, I'd want to move anything that has a .XXX at the end. I want to
move the files as listed in the active workbook and determine the number of
files to move based on the info stored in column A.
For i = 2 To Cells(Rows.Count, "A").End(xlUp).row

Next i

The directory location is stored in column B of my workbook.
(Y:\abc\file.xxx)

Can someone assist?

Thanks,
Barb Reinhardt

 

[Bob Phillips]

Something like

currentPath = "C:\myFiles\"
newPath = "Y:\abc\"
For i = 2 To Cells(Rows.Count, "A").End(xlUp).row
Name currentPath & Cells(i,"A").Value _
newPath & Cells(i,"A").Value
next i

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

I've modified it as shown below:

Sub Transfer()

newPath = InputBox("Please enter the directory name", "Directory Name") '
For i = 2 To Cells(Rows.Count, "A").End(xlUp).row
currentPath = Cells(i, "A").Value
Filename = Cells(i, "G").Value
Debug.Print currentPath, Filename, newPath, Filename
Name currentPath & "\" & Filename As newPath & "\" & Filename
Next i

End Sub

This moves the files from one location to another. I want it to copy the
file that's at the current path an move it to the new path. What needs to
change?

Thanks,
Barb Reinhardt

 

[Bob Phillips]

Dim oFSO As Object

Set oFSO = CreateObject("Scripting.FileSystemobject")

newPath = InputBox("Please enter the directory name", "Directory Name")
'
For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
currentPath = Cells(i, "A").Value
Filename = Cells(i, "G").Value
Debug.Print currentPath, Filename, newPath, Filename
oFSO.copyfile currentPath & "\" & Filename, newPath & "\" & Filename
Next i

Set oFSO = Nothing


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

I figured out what I needed. This is the macro that works:

ub Transfer()

newPath = InputBox("Please enter the directory name", "Directory Name") '
For i = 2 To Cells(Rows.Count, "A").End(xlUp).row
currentPath = Cells(i, "A").Value
Filename = Cells(i, "G").Value
Debug.Print currentPath, Filename, newPath, Filename
'copy currentPath & "\" & Filename As newPath & "\" & Filename
FileCopy currentPath & "\" & Filename, newPath & "\" & Filename

Next i

End Sub