VBA code - Moving files not working

superfb

Board Regular
Joined
Oct 5, 2011
Messages
99
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?
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,175
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...

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

Code:
FileSystemObject.CopyFile SourcePath, dstPath
...is not defined.

Hope this helps!
 
Last edited:

Mentor82

Active Member
Joined
Dec 30, 2018
Messages
307
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

Board Regular
Joined
Oct 5, 2011
Messages
99
Hi Sebastian

Thank you for the reply

Old File NameOld File LocationMove to file Location
CRWS-20170608.PDFC:\Users\User\Desktop\Test\CRWS-20170608.PDFC:\Users\User\Desktop\Test\20170608
222CRWS.PDFC:\Users\User\Desktop\Test\222CRWS.PDFC:\Users\User\Desktop\Test\20170608
3333CRWS.PDFC:\Users\User\Desktop\Test\3333CRWS.PDFC:\Users\User\Desktop\Test\20170608
44444CRWS.PDFC:\Users\User\Desktop\Test\44444CRWS.PDFC:\Users\User\Desktop\Test\20100101
999CRWS.PDFC:\Users\User\Desktop\Test\999CRWS.PDFC:\Users\User\Desktop\Test\20170608
CRWS-20170608.PDFC:\Users\User\Desktop\Test\CRWS-20170608.PDFC:\Users\User\Desktop\Test\20121108
CRWS-20180208.PDFC:\Users\User\Desktop\Test\CRWS-20180208.PDFC:\Users\User\Desktop\Test\20180608

<tbody>
</tbody>

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

superfb

Board Regular
Joined
Oct 5, 2011
Messages
99
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

Board Regular
Joined
Oct 5, 2011
Messages
99
Col E does have a formula in it but even when i copy and past special as value it still does not work?
 

superfb

Board Regular
Joined
Oct 5, 2011
Messages
99
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?
 

superfb

Board Regular
Joined
Oct 5, 2011
Messages
99
[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.....
 

Watch MrExcel Video

Forum statistics

Threads
1,099,699
Messages
5,470,250
Members
406,686
Latest member
BNR_ 1980

This Week's Hot Topics

Top