Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-25-2021, 04:21 PM
ranjan ranjan is offline Copy a files based on excel value Windows 10 Copy a files based on excel value Office 2019
Advanced Beginner
Copy a files based on excel value
 
Join Date: May 2021
Posts: 76
ranjan is on a distinguished road
Default Copy a files based on excel value

Hi

Am having file names in excel column a and status in column b.

I want to copy a files from one to location to another based on the excel value and update status as found or else not found in column b.

Ex: 123 file name is in excel
123 to be search in source location and 123 will have 3 files with different format's 123.docx. 123.rtf 123.pdf and all these copied into target location and update status as found.

456 is in excel and not found in source location and update status as not found in column b.



Range is column A
Status column B
Each file has 3 format's (docx pdf rtf)
If a file name matches then copy all the 3 formats into a target location.

Source and target location is dynamic.
Like input (enter a source path)
Output (select a target folder).

Your help is appreciated ....
Do the needful.
Reply With Quote
  #2  
Old 08-25-2021, 09:05 PM
Purfleet Purfleet is offline Copy a files based on excel value Windows 10 Copy a files based on excel value Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

you want a macro to do this?

Have you started it and need help or do you expect us to create it all?

'do the needfull'?

Good luck
Reply With Quote
  #3  
Old 08-25-2021, 09:27 PM
ranjan ranjan is offline Copy a files based on excel value Windows 10 Copy a files based on excel value Office 2019
Advanced Beginner
Copy a files based on excel value
 
Join Date: May 2021
Posts: 76
ranjan is on a distinguished road
Default

Hi,

I want to copy the list of files from excel column A (file names list) and column B (Msg Box). Below macro was copyinng only anyone format like pdf, docx or rtf, even i declare sFileType = ".pdf" sFileType = ".docx" sFileType = ".rtf" but it was not working, can anyonce please help me in reviewing the below code.

Additionally Add:

Can you please add if any file already exists in destination folder it should ask for confirmation as "Overwrite" or "Keep the both files" ...

Range is "Column A" and Msg box is "Column B"

Format types .doc, .rtf,.docx , .pdf ,html

Column "A" Heading as "File Name" & Column "B" Heading as "Staus".

If a file copied successfully msg as "Process Executed"

If a file is not available in source path msg as "Does Not Exists"

Same file name is having diferent versions like 123.doc , 123.rtf, 123.pdf , 123.Html but it was copying only one format.

Please find the below code and review and please insert the above additional notes..

Code:
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 = ".pdf"
sFileType = ".docx"
sFileType = ".rtf"



' 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
Reply With Quote
  #4  
Old 08-27-2021, 02:17 AM
ranjan ranjan is offline Copy a files based on excel value Windows 10 Copy a files based on excel value Office 2019
Advanced Beginner
Copy a files based on excel value
 
Join Date: May 2021
Posts: 76
ranjan is on a distinguished road
Default

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...

Last edited by ranjan; 08-27-2021 at 01:13 PM. Reason: Modification in query...
Reply With Quote
  #5  
Old 08-29-2021, 11:32 AM
ranjan ranjan is offline Copy a files based on excel value Windows 10 Copy a files based on excel value Office 2019
Advanced Beginner
Copy a files based on excel value
 
Join Date: May 2021
Posts: 76
ranjan is on a distinguished road
Default

hi,

anyone modified the above code:

VBA macro to copy files from one folder (and all it's subfolders, and their subfolders, and their subfolders....) to another folders...

Your help is highly appreciated.
Reply With Quote
Reply

Thread Tools
Display Modes


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

Other Forums: Access Forums

All times are GMT -7. The time now is 02:41 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft