move file macro

[daveofgv]

I have a list of file names in column A (example: 54335343.tif)
I have one directory with about 40 or so subfolders where this image is located, however, I don't know which one unless I manually searched for it.

What I need is a macro to run where I can have a list of 200 or even 1000 file names (like the above example) and move only those file names to another folder. I don't want to move the whole directory - just the file names I have in the column.

I know how to move the whole directory and also file names, however, I only know how to hardcode the actual file path (which I don't know).

Anyone know how to have excel macro search for the file name in a directory and once it finds it that file will move to another given folder?

Also, I need another column to put a "1" or something next to the image that the move method cannot find.

Maybe also move the file name to another column instead of a "1".....

Is this possible and if so can anyone help me?

We are hiring temp workers at work so I need to make this simple for those that don't know computers that well....

Thanks in advanced

daveofgv

 

[daveofgv]

Appears to work! Thanks.....

If I wanted to add another error in column B that says duplicate would I add it here

For i = 1 To LastRow
        With Application
            MatchVal = .Match(Cells(i, "A").Value, .Index(arrMyFiles, 2, 0), 0)
            If IsError(MatchVal) Then
                Cells(i, "B").Value = 1
            Else
                objFSO.GetFile(.Index(arrMyFiles, 1, MatchVal) & "\" & .Index(arrMyFiles, 2, MatchVal)).Move strDestFolder
            End If
        End With
    Next i

In other words, right now, Column "B" has a 1 value for all missing file name when compared and copied. If the Filename was already copied and is in the new folder, instead of overwriting the file (just skipping it) what would be the best way for it to have error "Duplicate" in column "C"?

I know your time is valueable and I don't mean to ask more of you.

We are just having a heck of a time at work and manually pulling 200-1000 files and copying them to another directory is a huge waste of time. Automation seems the best way to go.

 

[Domenic]

Try replacing...

[font=Verdana]objFSO.GetFile(.Index(arrMyFiles, 1, MatchVal) & "\" & .Index(arrMyFiles, 2, MatchVal)).Copy Destination:=strDestFolder, OverWriteFiles:=[color=darkblue]True[/color]
    [/font]

with

[font=Verdana][color=darkblue]If[/color] objFSO.FileExists(strDestFolder & Cells(i, "A").Value) [color=darkblue]Then[/color]
    Cells(i, "C").Value = "Duplicate"
[color=darkblue]Else[/color]
    objFSO.GetFile(.Index(arrMyFiles, 1, MatchVal) & "\" & .Index(arrMyFiles, 2, MatchVal)).Copy strDestFolder, [color=darkblue]False[/color]
[color=darkblue]End[/color] [color=darkblue]If[/color][/font]

 

[daveofgv]

Domenic -

If I had your talent I would consider my programming days complete.

Thank you for all your help and I am honored you first replied.

If you are bored - is there a way to color each -non found- cell with yellow?

If not, that's ok - just thought I would spice it up a little.

You went above and beyond to help.

Kindest,

daveofgv

 

[Domenic]

Thank you for all your help and I am honored you first replied.

You're very welcome!

...is there a way to color each -non found- cell with yellow?

Try the following change in red...

[font=Verdana]    [color=darkblue]For[/color] i = 1 [color=darkblue]To[/color] LastRow
        [color=darkblue]With[/color] Application
            MatchVal = .Match(Cells(i, "A").Value, .Index(arrMyFiles, 2, 0), 0)
            [color=darkblue]If[/color] IsError(MatchVal) [color=darkblue]Then[/color]
                Cells(i, "B").Value = 1
                [COLOR="Red"]Cells(i, "B").Interior.ColorIndex = 6[/COLOR]
            [color=darkblue]Else[/color]
                [color=darkblue]If[/color] objFSO.FileExists(strDestFolder & Cells(i, "A").Value) [color=darkblue]Then[/color]
                    Cells(i, "C").Value = "Duplicate"
                [color=darkblue]Else[/color]
                    objFSO.GetFile(.Index(arrMyFiles, 1, MatchVal) & "\" & .Index(arrMyFiles, 2, MatchVal)).Copy strDestFolder, [color=darkblue]False[/color]
                [color=darkblue]End[/color] [color=darkblue]If[/color]
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]End[/color] [color=darkblue]With[/color]
    [color=darkblue]Next[/color] i
    [/font]

 

[daveofgv]

Sorry to have to pick your brain again.....

If I wanted to add the source path and dest path into a cell instead of having to open the code and putting it there is that possible?

Example:

Copy the directory path and place in a cell (H18)to call

strDestFolder = "C:\Users\Dave\Desktop\newto\"

and the same into (H20)

strSourceFolder = "C:\Users\Dave\Desktop\newfrom\"

Thanks again in advanced

daveofgv

 

[Domenic]

Replace...

strDestFolder = "C:\Users\Dave\Desktop\newto\"

with

strDestFolder = Range("H18").Value

and

strSourceFolder = "C:\Users\Dave\Desktop\newfrom\"

with

strSourceFolder = Range("H20").Value

 

[daveofgv]

Domenic -

are you also an access guy???

will need to have this in access instead of excel. Was just notified that I will have to add other features and boss wants it in access.

daveofgv

 

[Domenic]

Domenic -

are you also an access guy???

will need to have this in access instead of excel. Was just notified that I will have to add other features and boss wants it in access.

daveofgv

Sorry... Unfortunately, I'm not familiar with Access.

 

[daveofgv]

Domenic -

It appears this makes Excel (Not Responding) when ran.

There are a huge amount of files it has to search for and creates a (Not Responding) as soon as it runs.

I am not sure if it is still running and just says Not Responding, however, the screen greys out like it failed.

I am trying to find the "attach file" to this thread to see if you can look at for me, however, I do not know where it is.

If you can show me where to upload the file - can you happen to look at it for me?

Thanks

daveofgv

 

[Domenic]

I've tested the solution and it seems to work fine. With cells A1:A1048575 containing the file names for which to search, it took about 2 minutes to run. If you're having problems, you'll likely need an alternate solution.