VBA code - Moving files not working

[superfb]

Sub copy()
Dim r As Long
Dim SourcePath As String
Dim dstPath As String
Dim myFile As String
Set FSO = CreateObject("Scripting.FileSystemObject")

On Error GoTo ErrHandler
For r = 2 To Range("D" & Rows.Count).End(xlUp).Row
SourcePath = Range("D" & r)
dstPath = Range("E" & r)
myFile = Range("A" & r)
FileSystemObject.CopyFile SourcePath, dstPath
If Range("D" & r) = "" Then
Exit For
End If
Next r
MsgBox "The file(s) can found in: " & vbNewLine & dstPath, , "COPY COMPLETED"
ErrHandler:
MsgBox "Copy error: " & SourcePath & vbNewLine & vbNewLine & _
"File could not be found in the source folder", , "MISSING FILE(S)"
'Range("A" & r).copy Range("E" & r)
Resume Next
End Sub

i have a list of files in column d that i would like to move as per col e however nothing is copying over......can someone please help?

 

[Domenic]

First, while you've created an instance of the FileSystemObject object and assigned it to the variable FSO, you later try to refer to it using FileSystemObject instead of FSO. You should be referring to it using FSO.

Secondly, it looks like SourcePath and dstPath each have been assigned only the path and don't include the filename. If so, you'll need to include the filename when copying from one folder to another.

By the way, I noticed that you haven't declared FSO. Always declare your variables. It will help you catch potential errors. You can force the explicit declaration of variables by adding the following line at the very top of your module...

Option Explicit

In your case, it would have flagged that FSO is not declared, and that FileSystemObject on the following line...

FileSystemObject.CopyFile SourcePath, dstPath

...is not defined.

Hope this helps!

 

[Mentor82]

i have a list of files in column d that i would like to move as per col e however nothing is copying over......can someone please help?

Hi,
It'd be helpfull if you'd share with your file to show the data. However pls check whether the directories in column d and e are correct. As a source path you should have file path including its extenstion ex C:\source\test.xlsx whereas the destination path should include destination folder directory only ex C:\destination\

Regards,
Sebastian

 

[superfb]

Hi Sebastian

Thank you for the reply

[TABLE="width: 1092"]
<tbody>[TR]
[TD]Old File Name[/TD]
[TD]Old File Location[/TD]
[TD]Move to file Location[/TD]
[/TR]
[TR]
[TD="align: left"]CRWS-20170608.PDF[/TD]
[TD="align: left"]C:\Users\User\Desktop\Test\CRWS-20170608.PDF[/TD]
[TD="align: left"]C:\Users\User\Desktop\Test\20170608[/TD]
[/TR]
[TR]
[TD="align: left"]222CRWS.PDF[/TD]
[TD="align: left"]C:\Users\User\Desktop\Test\222CRWS.PDF[/TD]
[TD="align: left"]C:\Users\User\Desktop\Test\20170608[/TD]
[/TR]
[TR]
[TD="align: left"]3333CRWS.PDF[/TD]
[TD="align: left"]C:\Users\User\Desktop\Test\3333CRWS.PDF[/TD]
[TD="align: left"]C:\Users\User\Desktop\Test\20170608[/TD]
[/TR]
[TR]
[TD="align: left"]44444CRWS.PDF[/TD]
[TD="align: left"]C:\Users\User\Desktop\Test\44444CRWS.PDF[/TD]
[TD="align: left"]C:\Users\User\Desktop\Test\20100101[/TD]
[/TR]
[TR]
[TD="align: left"]999CRWS.PDF[/TD]
[TD="align: left"]C:\Users\User\Desktop\Test\999CRWS.PDF[/TD]
[TD="align: left"]C:\Users\User\Desktop\Test\20170608[/TD]
[/TR]
[TR]
[TD="align: left"]CRWS-20170608.PDF[/TD]
[TD="align: left"]C:\Users\User\Desktop\Test\CRWS-20170608.PDF[/TD]
[TD="align: left"]C:\Users\User\Desktop\Test\20121108[/TD]
[/TR]
[TR]
[TD="align: left"]CRWS-20180208.PDF[/TD]
[TD="align: left"]C:\Users\User\Desktop\Test\CRWS-20180208.PDF[/TD]
[TD="align: left"]C:\Users\User\Desktop\Test\20180608[/TD]
[/TR]
</tbody>[/TABLE]

This is how it is, the file exists also the folder i want to move it to

 

[superfb]

thank you so much for the reply - i have changed the coding as you pointed out......the file name already exits in Col D ...........hence why i havent put myFile in the coding

Sub copy()
Dim r As Long
Dim SourcePath As String
Dim dstPath As String
Dim myFile As String
Dim FSO As Object

Set FSO = CreateObject("Scripting.FileSystemObject")

On Error GoTo ErrHandler
For r = 2 To Range("D" & Rows.Count).End(xlUp).Row
SourcePath = Range("D" & r)
dstPath = Range("E" & r)
myFile = Range("A" & r)
FSO.CopyFile SourcePath, dstPath
If Range("D" & r) = "" Then
Exit For
End If
Next r
MsgBox "The file(s) can found in: " & vbNewLine & dstPath, , "COPY COMPLETED"
ErrHandler:
MsgBox "Copy error: " & SourcePath & vbNewLine & vbNewLine & _
"File could not be found in the source folder", , "MISSING FILE(S)"
'Range("A" & r).copy Range("E" & r)
Resume Next
End Sub

 

[superfb]

Col E does have a formula in it but even when i copy and past special as value it still does not work?

 

[superfb]

I think it is working now apart from picking up one file??? The file is definitely there!

also can i change the message box to say how many files have been copied over?

 

[Domenic]

Do you get an error for that particular file?

 

[superfb]

Yes it gets picked up via the msg box

 

[superfb]

[Back]

Option Explicit


Sub MakeFolders()
Dim xdir As String 'give the directory a variable (xdir) and DIM it as String
Dim fso As Object 'DIM fso as a variable representing the FileSystemObject
Dim destfol As String 'DIM destfol (destination folder) as String
Dim lstrow As Variant 'DIM lstrow (last row) as Variant. Not certain how many rows will have data in Col A
'The total number will vary from time to time
Dim i As Long 'DIM the integer "i" as Long to accommodate an large number

Set fso = CreateObject("Scripting.FileSystemObject") 'FileSystemObject allows creation of folders.


destfol = Range("C4").Value 'destFol is the path entered in cell C4
lstrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row 'Finds last used row in Col A

Application.ScreenUpdating = False 'Turn off ScreenUpdating. Makes things run faster.

For i = 2 To lstrow '<-- reads list beginning from A2

'Range("C4").Value tells Excel what the path is .. Range("A" & i).Value tells Excel to use the folder name
'from each cell in Col A with a name
xdir = Range("C4").Value & Range("A" & i).Value

If Not fso.FolderExists(xdir) Then 'If the folder doesn't already exist then
fso.CreateFolder (xdir) 'Using the FileSystemObject, create the folder named in xdir
End If
Next 'Began at row 2, now go to row 3 and repeat / row 4 and repeat, etc. until end of list Col A

Application.ScreenUpdating = True 'Turn ScreenUpdating back on to show all the changes created

MsgBox i - 2 & " folders created in Directory : C:\Users\My\Desktop\Test" 'Display MsgBox showing how many
'folder were created and in what Folder they were created.

End Sub


Also to pick your brain, I have the above code that helps to create folders. But if I have two file names it will not create a second folder with the same name.

Is there a way I can edit this code so if I have to folder names with

20190204
20190204

The second file would be created as

20190204 - 2 .....or ending with -3 if there are three file names the same.....