Copy a files based on excel value

ranjan · #4 08-27-2021, 02:17 AM

Hi

Correction has made and now its working fine. Now again i want to search , copy from folder & subfolders based on a excel value, can anyone add this into a below code.

Quote:

Sub CopyFiles1()
Dim iRow As Integer ' ROW COUNTER.
Dim SourcePath As String
Dim DestinationPath As String
Dim sFileType As String

Dim bContinue As Boolean

bContinue = True
iRow = 2

' THE SOURCE AND DESTINATION FOLDER WITH PATH.

SourcePath = InputBox("PLEASE ENTER PATH", "SOURCE PATH") & ""
DestinationPath = InputBox("PLEASE ENTER PATH", "DESTINATION PATH") & ""

sFileType = "*.*"

' LOOP THROUGH COLUMN "A" TO PICK THE FILES.
While bContinue

If Len(Range("A" & 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(SourcePath & Range("A" & CStr(iRow)).Value & sFileType)) = 0 Then

Range("B" & CStr(iRow)).Value = "Does Not Exists"
Range("B" & CStr(iRow)).Font.Bold = True
Else
Range("B" & CStr(iRow)).Value = "Copied"
Range("B" & CStr(iRow)).Font.Bold = False

If Trim(DestinationPath) <> "" Then
Dim objFSO
Set objFSO = CreateObject("scripting.filesystemobject")

' CHECK IF DESTINATION FOLDER EXISTS.
If objFSO.FolderExists(DestinationPath) = False Then
MsgBox DestinationPath & " 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:=SourcePath & Range("A" & CStr(iRow)).Value & _
sFileType, Destination:=DestinationPath

' METHOD 2) - USING "MoveFile" METHOD TO PERMANENTLY MOVE THE FILES.
'objFSO.MoveFile Source:=sSourcePath & Range("B" & CStr(iRow)).Value & _
sFileType, Destination:=sDestinationPath
'*****
End If
End If
End If

iRow = iRow + 1 ' INCREMENT ROW COUNTER.
Wend
Set objFSO = Nothing

End Sub

Please make a edit to search and copy a files from a folder & sub folders...

Please do the needful.
Your help is highly appreciated...

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Browse, search and copy files from one location to another based on the list given in excel sheet us	nmkhan3010	Excel Programming	2	09-07-2020 10:27 AM
Might it be possible to emulate the worksheet-based structure of Excel files in MS Word?	Arabiflora	Word	1	05-27-2016 11:50 PM
a macro that can copy data from copy.xls to our current excel macro.xls based on criteria:	udhaya	Excel Programming	1	11-12-2015 10:12 AM
Can a macro rename Excel files based on a cellname?	chrisd2000	Excel Programming	1	06-23-2014 06:50 PM
How to copy linked Excel and Word files and retain links	ashleynpeters1	Word	1	05-30-2013 02:25 PM