Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-07-2020, 04:21 AM
nmkhan3010 nmkhan3010 is offline Browse, search and copy files from one location to another based on the list given in excel sheet us Windows 10 Browse, search and copy files from one location to another based on the list given in excel sheet us Office 2016
Novice
Browse, search and copy files from one location to another based on the list given in excel sheet us
 
Join Date: Feb 2020
Posts: 17
nmkhan3010 is on a distinguished road
Default Browse, search and copy files from one location to another based on the list given in excel sheet us

Hi,



Previously i posted in this forum & Excel Help Forum but it was marked as cross posting and closed the job without any rsolution, but please understand my need and help me, recently it was not posted in any forum.


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.docx , 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()                          ''                  Code
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
  #2  
Old 09-07-2020, 10:07 AM
Purfleet Purfleet is offline Browse, search and copy files from one location to another based on the list given in excel sheet us Windows 10 Browse, search and copy files from one location to another based on the list given in excel sheet us Office 2019
Competent Performer
 
Join Date: Jun 2020
Location: Essex
Posts: 172
Purfleet is a jewel in the roughPurfleet is a jewel in the roughPurfleet is a jewel in the roughPurfleet is a jewel in the rough
Default

It will only do rtf files as you are changing the varible from pdf, to docx to rtf with out doing anything with it.


so the code will execute with .rtf all the time

sFileType = ".pdf"

sFileType = ".docx"
sFileType = ".rtf"
Reply With Quote
  #3  
Old 09-07-2020, 10:27 AM
NoSparks NoSparks is offline Browse, search and copy files from one location to another based on the list given in excel sheet us Windows 10 Browse, search and copy files from one location to another based on the list given in excel sheet us Office 2010
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 799
NoSparks has a spectacular aura aboutNoSparks has a spectacular aura aboutNoSparks has a spectacular aura about
Default

also cross posted at Chandoo
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy files from one location to another nmkhan3010 Excel Programming 2 04-24-2020 12:14 AM
Browse, search and copy files from one location to another based on the list given in excel sheet us How to copy excel sheet withe HEADER and Paste into new sheet? cloudforgiven Excel Programming 6 01-05-2017 07:30 PM
Copy data from one sheet to another based on a certain criteria shina67 Excel Programming 2 12-28-2016 07:32 AM
VBA Word - Search Within Files Containing A String - Copy Files to New Folder jc491 Word VBA 0 01-09-2016 12:00 PM
Browse, search and copy files from one location to another based on the list given in excel sheet us Browse to a specific sheet in an excel workbook Sinister Excel 4 04-08-2014 05:53 PM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 06:38 AM.


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