• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Move files on my desktop using a list in excel

R

rjwalters

New Member
Ok So I have list in excel, column A.The list corresponds to file names in a folder on my desktop, I want the VBA to find the names in column A in my folder and move those files to another folder.


Example is in A1 the file name is "ZAC". On my desktop is a folder called Cousins. The file ZAC is in that folder. I want the code to move ZAC to a new folder called EX Cousins.


I found this code below that is suppose to work but I do not know where to put the path names to the folders.

I hope I have the code tags right.

[pre]
Code: 
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
[/pre]
 
Hi Walters ,


List the file names in column A , the current paths in column B , and the desired new paths in column C ; for example , suppose you have a file temp.pdf in the C:TEMP folder , and you wish to move it to the F:DOWNLOADS folder , then :


In A1 enter temp.pdf


In B1 enter C:TEMP


In C1 enter F:DOWNLOADS


Repeat for all the files you wish to move.


Narayan
 
So now its throwing an error at line:


" Name Filepath & "" & Filename As NewPath & "" & Filename"


Am I suppose to rename these to something?
 
In column A is only a server name, not the entire file name.Will this require a different code?


So in A1 is AELLS0001


Johns_10JAN2013_AELLS0001.mef3
 
Hi Walters ,


Sorry ; since the backslash is being concatenated by the Name command , you should eliminate it from the folder names :


In B1 enter C:TEMP , not C:TEMP


In C1 enter F:DOWNLOADS , not F:DOWNLOADS


Otherwise , eliminate the concatenated backslash from the Name command , thus :


Name Filepath & Filename As NewPath & Filename


Narayan
 
Hi Walters ,


The server name is a part of the path , not the file name ; the file name is the text after the last backslash in the full name :


If the full name is :

[pre]
Code: 
F:Documents and SettingsAll Users.WINDOWSApplication DataMAGIXMusic_Maker_MX_Production_Suite_Download_VersionBitmapsInfoBoxbe1e74fb2192ee088e7a8966b83e51e.JPG
[/pre]
then the file name is 0be1e74fb2192ee088e7a8966b83e51e.JPG


Narayan
 
Is there a way to do it changing the " Name Filepath & "" & Filename As NewPath & "" & Filename"


Below is the from path and to path, and as you can see there are many 's


C:UsersIBM_ADMINDesktopAccountsMy AccountsSmackToolsCompareMEFfiles


C:UsersIBM_ADMINDesktopAccountsMy AccountsSmackToolsCompareResults
 
So as long as I put the entire file name it will work, but it still hangs up on the same line of code. Any thoughts
 
Can I have it look for a particular name, instead of the whole file name. I get a report and it tells me the server name, say TED,


I want it to move the file if the name of the file contains TED. The actual file name may be 'I_move_TED.mefs" , but since it contains TED I want it moved to new folder.


And just so you know I appreciate the help.
 
Hi Walters ,


Sorry for the delay , it's a new day today.


Can you please post just one sample for one file , giving the from folder name , to folder name , and the file name ?


Can I take this as a sample ?


1. C:UsersIBM_ADMINDesktopAccountsMy AccountsSmackToolsCompareMEFfiles


2. C:UsersIBM_ADMINDesktopAccountsMy AccountsSmackToolsCompareResults


3. I_move_TED.mefs


Narayan
 
First thank you for your response.


Yes you could use that example.


In A1= TED

In B1= C:UsersIBM_ADMINDesktopAccountsMy AccountsSmackToolsCompareMEFfiles

In C1= C:UsersIBM_ADMINDesktopAccountsMy AccountsSmackToolsCompareResults


I want the code to recognize "TED" in the file name "I_move_TED.mefs"


At the current time I have to have the entire file name in A1.
 
Hi Walters ,


OK. So what you have in A1 is not the entire file name , but the text which you wish to match ; so all files in the from folder which have this text in their names , will be moved.


Give me some time.


This is a totally different piece of code that you have to use , since it will involve scanning a folder , retrieving each file , checking to see whether the match exists , and then use the Name statement to move the file.


Narayan
 
Yea, and the code we have in this thread works fine, just that it requires the entire file name,instead

Of just the server name ( TED).
 
Hi Walters ,


You are calling TED the server name , but I don't think this is the correct term , since the word "server" is very specific in its meaning ; if the text "TED" is a part of the file name , as in I_move_TED.mefs , then it is not really a server , it is just another piece of text.


Secondly , the original code was expecting a list of file names in column A , which is why it used the following construct :

[pre]
Code: 
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
[/pre]
It was a simple matter to use the three given components to construct a full name , and use the Name statement to move the file.


Now , it is no longer so simple ; the from folder may have many files , some of which have the text "TED" in their file name , and some which don't. The first requirement is that now the FOR ... NEXT loop has to loop through each file in a folder , rather than loop through each cell in a range ! This is a substantial difference.


The steps involved in this code will be :


1. Use the from folder name to first navigate to the required folder on disk.


2. Retrieve the file name


3. Check whether the specified text ( from A1 ) is present in the file name ; if it is , then move the file else do not.


4. Go to the next file.


Repeat steps 2 , 3 and 4 till all the files in the folder have been checked.


Narayan
 
I may not be explaining this right. In column A is a list of server names, they are all different and unique..These are extracted by a script from the another sheet. The actual server file would be "I_move_TED.mefs" which is the mef file that belongs to the server TED.The script unfortunately does not give the entire file name.


So yes there is a list in column A, but the list is not the entire file name, more just a name from the file name.


The from and to portion of the VBA works fine, I just need it to look for the word TED in the file name. Each line has a different word that it will look for.


Sorry I am making this difficult, it may be beyond my scope to do, but thanks for your help.


Bed time for me, again thanks....
 
I am not sure how to post an example from my skydrive but let me try the embed link,


https://skydrive.live.com/view.aspx?resid=63F95EA410E059A9!130&cid=63f95ea410e059a9&authkey=APS0V9azIHK4nIY


Let me know if this works...
 
Hi Walters ,


Your link works , and I have downloaded your file ; I'll get back to you.


Narayan


P.S. Using the https://bitly.com link shortener , gives the following link :


http://sdrv.ms/YPpWuB
 
Hi Walters ,


Can you try this ?

[pre]
Code: 
Sub MoveFiles()
Dim fs, f, f1, fc, s

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

Set fs = CreateObject("Scripting.FileSystemObject")
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)

Set f = fs.GetFolder(Filepath)
Set fc = f.Files

For Each f1 In fc
If InStr(1, f1.Name, Cell) Then
Name Filepath & "" & f1.Name As NewPath & "" & f1.Name
End If
Next
Next Cell
Set fs = Nothing
Set Rng = Nothing
Set RngEnd = Nothing
End Sub
[/pre]
Narayan
 
You must log in or register to reply here.
Back
Top