VBA code - Moving files not working

superfb

Active Member
Joined
Oct 5, 2011
Messages
251
Office Version
  1. 2007
Platform
  1. Windows
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?
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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:
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
Col E does have a formula in it but even when i copy and past special as value it still does not work?
 
Upvote 0
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?
 
Upvote 0
[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.....
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top