PDA

View Full Version : VBA to copy files ( list ) from location ( different locations ) to another folder



nour_3d
09-29-2016, 07:17 AM
Hi,

I have list of file names in Sheet 1 ( Main) col C without extn, and against each cell in this col I have the source path of this file listed in Col D, and against each cell I have list of destination which i need to copy this file to and listed in Col E.

so what i need to do is; copy each file from its specific source path to its specific destination path. ex: copy the file named in C1 and its source in D1 to the destination in E1
and so on.

this is the draft of what i tried to do but I am not aware of VBA, I do not know how to make the for loop which count properly



Sub exa() Dim FSO As Object
Dim rCell As Range
Dim Rdir As Range
Dim rngFileNames As Range
Dim FOL_FROM As Range
Dim FOL_TO As Range

Dim i As String

Const EXT As String = ".pdf"


Set rngFileNames = ThisWorkbook.Worksheets("Main").Range("C5:C50")
Set FOL_FROM = ThisWorkbook.Worksheets("Main").Range("D5:50")
Set FOL_TO = ThisWorkbook.Worksheets("Main").Range("E5:E50")

Set FSO = CreateObject("Scripting.FileSystemObject")

i = 1

For Each rCell In rngFileNames

FSO.CopyFile FOL_FROM & rCell.Value & EXT, FOL_TO

i = i + 1
Next
End Sub

mana
09-29-2016, 07:56 AM
files & folders always exist?

FSO.CopyFile rCell.Offset(, 1).Value & rCell.Value & EXT, rCell.Offset(, 2).Value

nour_3d
09-29-2016, 08:08 AM
not workink bro.

I developed another vba code is working fine but i need to tell the program to run i from starting range of the filenames to ending names

quote

Sub A7a()

Dim FSO As Object
Dim rCell As Range
Dim Rdir As Range
Dim rngFileNames As Range
Dim FOL_FROM As Range
Dim FOL_TO As Range
Const EXT As String = ".pdf"

Set rngFileNames = ThisWorkbook.Worksheets("Main").Range("C5:C50")
Set FOL_FROM = ThisWorkbook.Worksheets("Main").Range("D5:D50")
Set FOL_TO = ThisWorkbook.Worksheets("Main").Range("E5:E50")
Set FSO = CreateObject("Scripting.FileSystemObject")




For X = 1 To 44

FSO.CopyFile FOL_FROM(X, 1) & rngFileNames(X, 1).Value & EXT, FOL_TO(X, 1)
Next X
End Sub

mana
09-29-2016, 03:20 PM
for x=1 to rngFilenames.connts