Is it even possible?

[draconius]

I ran a macro (ListMyFiles) that gives me the file name of everything in a folder (used a folderpicker code). I am then going to run it through some criteria to identify key files that I want and other not so much.

Is there a way to have a set list of files (all have a unique 6-10 digit code in beginning of filename) on a spreadsheet and then have them move to identified folders on my harddrive?

ex:
123456.pdf
564231.pdf
945672.pdf
453185.pdf

and I want these to move from 'X' folder to 'Y' folder.

Then put in a new set of "pdf's" to move from 'X' folder to 'Z' folder.



I have searched for code but it looks like it is mostly copies or deleted a selected folder...I wanted to match it to a list of files to move them.

MANY MANY thanks!

[patel]

from FAQ
When posting a question, you should include:

A meaningful subject line (not "please help" or "urgent I can't get this to work" or "Microsoft Office problem")
A statement of your goal
What you have already tried to reach that goal
Your version of Microsoft Office and operating system
Sample input data
The expected outcome
Before and after screenshots or data samples
The exact text of error messages, if any

[draconius]

Tried to update the title, but I don't think it took...I would rather not create a new request...

[patel]

to move files you have to rename them

Sub movefile()
Dim fs, f, f1, s, sf
Set fs = CreateObject("Scripting.FileSystemObject")
oldpath = "D:\DATI\prova"
newpath = "D:\DATI\prova\textfile"
Set f = fs.getfolder(oldpath)
Set NFile = f.Files
For Each pf1 In NFile
If pf1.Name = "" Then Exit Sub
NameFile = pf1.Name
Name pf1 As newpath & "\" & NameFile
Next
End Sub

[Kenneth Hobs]

I have posted examples of this kind of thing before. Search for Name and my name. Name renames and moves the file. FSO methods and Shell() methods can create the folder moved to if needed.

Providing an example file goes a long way in helping you.

[snb]

Assuming:
- the folder in which the files reside is G:\OF
- the folder into which the files have to be transported (copied or moved) is E:\new
- the names of the files in column A in sheet1

to copy files:

sub copy_snb()
c00="G:\OF\"
c01="E:\new\"
sn=sheet1.columns(1).specialcells(2)

for j=1 to ubound(sn)
filecopy c00 & sn(j,1), c01 & sn(j,1)
next
End sub


to move files:

sub move_snb()
c00="G:\OF\"
c01="E:\new\"
sn=sheet1.columns(1).specialcells(2)


for j=1 to ubound(sn)
name c00 & sn(j,1) As c01 & sn(j,1)
next
End sub

[draconius]

Thank you all for your help with this!


Patel,
I altered your code a little to allow for browsing for the folder and it works, however, it only moves everything in one folder to the other...it isn't referencing the files that are on the excel sheet.

My code: Sub movefile()
Dim fs, f, f1, s, sf
Set fs = CreateObject("Scripting.FileSystemObject")
oldpath = BrowseForFolder
newpath = BrowseForFolder
Set f = fs.GetFolder(oldpath)
Set NFile = f.Files
For Each pf1 In NFile
If pf1.Name = "" Then Exit Sub
NameFile = pf1.Name
Name pf1 As newpath & "\" & NameFile
Next
End Sub

It does however end with an error of "File does not exist", which looks like it needs a data_null_ type setup (Sorry...SAS reference) in the code.


SNB,
I tried your code, changed the paths, changed the sn = sheet1.columns(1).specialcells(2) and it would error out on that line stating Run-time error '424': Object required. Then it would error at FileCopy stating File not found... Your code looks very straight forward, but won't work itself out.

Mr. Hobs,

I tried searching for your code examples and man oh man have you helped a LOT of people!!! Kudos to you :) I tried searching on the NAME criteria and nothing came up...I don't know if the search function searches within the code function or not...

EDIT: Mr. Hobs, I found one from "Moving files from one folder to another". I am going to try it now...though it looks pretty specific to the persons request...though it did not work.

[draconius]

Oi...This is becoming a beast...Here is a code that I am trying...still doesn't work though...

Sub MoveMyFiles()

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 = BrowseForFolder
NewPath = BrowseForFolder
Name Filepath & "\" & Filename As NewPath & "\" & Filename
Next Cell

End Sub

I delimited the file name to separate by "_" so column A has the first part of a file name...hopefully that isn't was is breaking it. I did try to keep one as the file name and it still broke saying "File Not Found"

[snb]

sn = sheet1.columns(1).specialcells(2)

From a distance I can't read the names of the sheets in your workbook.
You will have to adapt the code to your situation (unless you post your sample workbook here).

if the name on the tab is "Sheet1"
you should use

sn = sheets("Sheet1").columns(1).specialcells(2)

[draconius]

Here is a sample workbook. I can't post the actual one due to proprietary info.

The row 1 is the new delimited information, and row 2 is the full file name that after it is delimited is cleaned for further analysis.

It is macro enabled, so you can check the codes I have...

[draconius]

I have a code that is working if the A column has the EXACT file name...is there a way to have it as partial? The code states in a "note" that is works for partials, but doesn't seem to be working as such. I included the code writers information for ownership.

If there is a partial file name is comes back as Not Found.

ex:
8512341234 Not Found
8528669495 Not Found
8535654727 Not Found
8540254370 Not Found
8512341234_Dis_3282012_03082012_3622040 Moved
8528669495_Dis_2282012_382012_3619855 Moved
8535654727_Dis_312012_03082012_3626023 Moved
8540254370_Dis_03012012_03082012_3627277 Moved


Sub MoveFilesVariably()
'Jerry Beaucaire - 9/1/2012
'Move files listed in column A on a sheet from one folder to another
'Column A values are partial filenames, the EXT is variable as are the folders

Dim myPath As String, myDest As String, MyExt As String, fNAME As String
Dim MyFiles As Range, aFile As Range

'Source Folder selection
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.InitialFileName = "C:\2012\" 'default path
.Show
If .SelectedItems.Count > 0 Then
myPath = .SelectedItems(1) & "\"
Else
Exit Sub
End If
End With

'Source Folder selection
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.InitialFileName = myPath
.Show
If .SelectedItems.Count > 0 Then
myDest = .SelectedItems(1) & "\"
Else
Exit Sub
End If
End With

'The extension (filetype) to move, .pdf or xls, etc.
MyExt = Application.InputBox("What file type to move? Enter the extension", "Type of file", "PDF", Type:=2)
If MyExt = "False" Then Exit Sub

Set MyFiles = Sheets("DATA").Range("A:A").SpecialCells(xlConstants)

For Each aFile In MyFiles 'evaluate one filename at a time
fNAME = Dir(myPath & "*" & aFile.Value & "." & MyExt)
If Len(fNAME) > 0 Then 'make sure file exists
Name myPath & fNAME As myDest & fNAME
aFile.Offset(, 7).Value = "Moved"
fNAME = ""
Else
aFile.Offset(, 7).Value = "Not Found"
End If
Next aFile

End Sub

[snb]

to move files:


Sub move_snb()
c00="G:\OF\"
c01="E:\new\"
sn=sheet1.columns(1).specialcells(2)

For j=1 To UBound(sn)
name c00 & dir(sn(j,1) & "*.*") As c01 & dir(sn(j,1) & "*.*")
Next
End Sub

[draconius]

This thread can now be closed. I have the macro working correctly. Thank you all for your help!