How To Move Selected Bulk Files Using Macro In Excel

This is the macro. You have to paste it in your excel vba sheet.

Option Explicit

Sub CopyFiles()

Dim iRow As Integer ' ROW COUNTER.

Dim sSourcePath As String

Dim sDestinationPath As String

Dim sFileType As String

Dim bContinue As Boolean

bContinue = True

iRow = 2

' THE SOURCE AND DESTINATION FOLDER WITH PATH.

sSourcePath = "D:\KDK\aLREADY UPLOADED DATA\Pune\"

sDestinationPath = "C:\Users\sekhar.reddy\Desktop\PuneFinalphotos\"

sFileType = ".jpg" ' TRY WITH OTHER FILE TYPES LIKE ".pdf".

' LOOP THROUGH COLUMN "B" TO PICK THE FILES.

While bContinue

If Len(Range("B" & CStr(iRow)).Value) = 0 Then ' DO NOTHING IF THE COLUMN IS BLANK.

MsgBox "Process executed" ' DONE.

bContinue = False

Else

' CHECK IF FILES EXISTS.

If Len(Dir(sSourcePath & Range("B" & CStr(iRow)).Value & sFileType)) = 0 Then

Range("C" & CStr(iRow)).Value = "N"

Range("C" & CStr(iRow)).Font.Bold = True

Else

Range("C" & CStr(iRow)).Value = "Y"

Range("C" & CStr(iRow)).Font.Bold = False

If Trim(sDestinationPath) <> "" Then

Dim objFSO

Set objFSO = CreateObject("scripting.filesystemobject")

' CHECK IF DESTINATION FOLDER EXISTS.

If objFSO.FolderExists(sDestinationPath) = False Then

MsgBox sDestinationPath & " Does Not Exists"

Exit Sub

End If

'*****

' HERE I HAVE INCLUDED TWO DIFFERENT METHODS.

' I HAVE COMMENTED THE SECOND METHOD. TO THE SEE THE RESULT OF THE

' SECOND METHOD, UNCOMMENT IT AND COMMENT THE FIRST METHOD.

' METHOD 1) - USING "CopyFile" METHOD TO COPY THE FILES.

objFSO.CopyFile Source:=sSourcePath & Range("B" & CStr(iRow)).Value & _

sFileType, Destination:=sDestinationPath

' METHOD 2) - USING "MoveFile" METHOD TO PERMANENTLY MOVE THE FILES.

'objFSO.MoveFile Source:=sSourcePath & Range("B" & CStr(iRow)).Value & _

sFileType, Destination:=sDestinationPath

'*****

End If

iRow = iRow + 1 ' INCREMENT ROW COUNTER.

Wend

End Sub

How To Move Selected Bulk Files Using Macro In Excel