Use a list from Excel to move files on computer

[rjwalter]

Ok I found this code and it works fine except that it needs the entire file name in column A to work. What I have in column A is a server name, so I need it find the server name in the file name. An example is the file name is American_server_pdol1298.mef , but in column A just the server name is listed , pdol1298 , This is the output of a script I run. So now I need to move the file into another folder.

thank you




[VBA]Sub MoveFiles()

Dim Cell As Range
Dim Filename As String
Dim Filepath As String
Dim NewPath As String
Dim Rng As Range
Dim RngEnd As Range
Dim Wks As Worksheet

Set Wks = ActiveSheet

Set Rng = Wks.Range("A1")
Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
If RngEnd.Row < Rng.Row Then Exit Sub Else Set Rng = Wks.Range(Rng, RngEnd)

For Each Cell In Rng
Filename = Cell()
Filepath = Cell.Offset(0, 1)
NewPath = Cell.Offset(0, 2)
Name Filepath & "\" & Filename As NewPath & "\" & Filename
Next Cell

End Sub[/VBA]

[Kenneth Hobs]

Welcome to the forum! Please paste code between VBA code tags.

Please give more details. We need to know where the path to the file is at, what the file name is, and what path and filename to move it to.

e.g.
A1="\\kensServer\Excel\"
B1="ken.xlsx"
C1="\\kensServer\Excel\MovedPath\"

Which would then be:
[vba]Name "\\kensServer\Excel\ken.xlsx" As "\\kensServer\Excel\MovedPath\ken.xlsx"[/vba]
Try something like that above, manually in a VBA run or VBE's Immediate Window. I don't remember if Name can work with UNC paths.

Of course attached files with example data helps us help you. You can always attach a file.

[rjwalter]

I uploaded a file but not sure where it went? I hope it clarifies and I think it has the information you ask for in it.

[Kenneth Hobs]

I guess you want to hard code the first part of the file name? I don't see a filename extension in your example file. Open the VBE and run this Sub. Backup your file(s) first though. I used XLSX as your filename extension. Change to suit.

I set it to run just the first one in row 2. Uncomment the commented line to do all once you know that it works for one. Note how I used the Immediate Window to show the results of string variables. I also added and error catching routine to catch if the Source filename or Destination folder does not exist.

[vba]Sub ken()
Dim sPath As String, dPath As String, fn As String, sFN As String, dFN As String
Dim r As Range, c As Range

Set r = Worksheets("app-ict-02").Range("A2")
'Set r = Worksheets("app-ict-02").Range("A2", Worksheets("app-ict-02").Range("A2").End(xlDown))
For Each c In r
With c
sPath = .Offset(, 1).Value2 & "\"
dPath = .Offset(, 2).Value2 & "\"
fn = "JUMBO_3FEB2013_" & .Value2 & ".xlsx"
sFN = sPath & fn
dFN = dPath & fn
End With
Debug.Print sFN, dFN
If not(Dir(sFN) = "" Or Dir(dPath, vbDirectory) = "") Then Name sFN As dFN
Next c
End Sub[/vba]

[rjwalter]

I can not get it to run, each "server" name in column A refers to a different file in my from folder.

I am not sure what the fn="Jumbo_3FEB2013_" line does.
or the what the value2 does also.

[Kenneth Hobs]

It adds to the filename as you said that you needed in your file. Does the Immediate Window not show the resultant string that Name needs? I can only guess so much.