Copy files listed in Excel from one directory to another...?

[lamensterms]

Hey guys,

I have found this code below, which will move files listed in column B from a directory listed in column A to to a directory listed in column C.


Sub MoveFiles()

Dim SourcePath As String
Dim DestPath As String
Dim FileName As String
Dim LastRow As Long
Dim i As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

For i = 1 To LastRow

FileName = Cells(i, "B").Value

If Right(Cells(i, "A").Value, 1) <> Application.PathSeparator Then
SourcePath = Cells(i, "A").Value & Application.PathSeparator
Else
SourcePath = Cells(i, "A").Value
End If

If Right(Cells(i, "C").Value, 1) <> Application.PathSeparator Then
DestPath = Cells(i, "C").Value & Application.PathSeparator
Else
DestPath = Cells(i, "C").Value
End If

If Dir(SourcePath & FileName) = "" Then
Cells(i, "D").Value = "Source file does not exist."
ElseIf Dir(DestPath & FileName) <> "" Then
Cells(i, "D").Value = "File already exists."
Else
Name SourcePath & FileName As DestPath & FileName
Cells(i, "D").Value = "File moved to new location"
End If

Next i

End Sub


This marco works quite well (at what it does), but I would like to slightly change what it does.

Rather than MOVE the files from one location to another, I would like to COPY the files.

Also, the routine only works is the file name is entered exactly as it is. For instance, in my case - I am trying to sort CAD drawings. Our DWG naming format is "1068-B1001_0.dwg". The "1068-B1001" is the drawing number, "_0" is the revision suffix, and of course ".dwg" is the extension. I would like the macro to only consider the drawing number and leave the revision suffix and extension out of column B (can this be handled with a wild card?). This way we can list drawing numbers in column B, and regardless of their revision, the routine will be able to find them.

So, I was wondering if someone can please help me tweak the macro to suit these requirements? I am very new to VBA and unfortunately need a fair bit of hand holding.

Open to suggestions of any alternative methods to achieving this.

Thanks a lot for any help, looking forward to hearing back.

[snb]

Are you familiar with the helpfiles in teh VBEditor ?

Did you enter the lemma copyfile of filecopy ?

[lamensterms]

Hi snb,

Thanks for the reply.

Yes, I do use the help files. But my knowledge of VBA is so limited that in this case I do not know how to even change the move function to a copy function. I cannot even identify the "move" function in the code above.

I'm afraid I do not understand your second question.