lamensterms
05-15-2014, 06:31 PM
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.
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.