Home / Excel / Creating macros to move or copy files

Creating macros to move or copy files

Macros to copy or move files from one folder to another

Creating macros to move or copy files from one folder to another should be easy but it turned out to be problematic.

I'm working on a new project called the Paperless Office project (I named it that). I've been doing a lot of research and some of that research is trying to see if I could automate things in case I have to do it myself. I'm trying to give myself as much options as possible. One thing I've been looking at is how we are going to store the files and how we are going to search for them. This past week I've wondered "what if I had to create a program to move the files?" So I decided to have copying and moving macro on hand in case I need it.

One thing you have to be really careful of - how you write down the directory path. One character off and the macro doesn't work.

This exercise actually got me a little closer to understanding the CreateObject("Scripting.FileSystemObject"). I don't understand it % but I'm just a little bit closer. To me, it's like creating a vehicle to do some actions on files and folders.

All of the macros I'm showing are versions found by Googling. Most of them are from Stack Over Flow.


The first one is creating a folder. Every week I save weekly reports from Dropbox onto my hard drive in case we need to refer back to old reports,  so I'm creating a new folder each week.

Where I found the code: http://stackoverflow.com/questions/31973435/vba-to-create-a-folder-based-on-the-save-path-located-in-a-cell.

My version uses a sheet called "Start Page" that contains the directory path in cell D16 to which I want to append a folder called "reports". I could have designated cell D17 as the folder name "reports" so that I could change it to a different name in the future. Cell D16 is actually a formula that contains the main directory catenated to a formula calculating the weekly date, so cell D16 looks something like ="directory path" & text(NOW()+8-WEEKDAY(NOW(),1),"mm.dd.yyyy"). Or , as an example "directory path12.18.2016" (the date will change each week). I want to append a folder called "reports" at the end each week.

Note how I am dimensioning fsoFSO as an object and then setting it as CreateObject("Scripting.FileSystemObject") before I can use it to create a folder with CreateFolder (ToFolder). ToFolder is a variable I personally created. This macro is the basic rock-bottom what you need to create a folder.

Sub B_MakeMyFolderMMR()
Dim ToFolder As String
Dim fsoFSO As Object
ToFolder = Worksheets("Start Page").Range("D16") & "reports"
Set fsoFSO = CreateObject("Scripting.FileSystemObject")
fsoFSO.CreateFolder (ToFolder)
MsgBox ("Reports folder created")
End Sub

After I have created the folder, now I want to move the files (which are reports) from Dropbox (the "from path") to my hard drive (the "to path"). This code works with a folder that contains subfolders and then stops there. If there are subfolders within the subfolders, the macro won't go there - the additional subfolders would require a recursive macro which I have a faint idea of how that works. But I'm not tackling that yet.

Where I found the code: https://social.msdn.microsoft.com/Forums/office/en-US/59df5bf4-ff74-4ae9-9c5a-11d4ff233cf0/how-to-move-file-from-one-folder-to-another-using-vba.

Note I create a Folder and SubFolder as Object variables to kind of hold their name (or maybe their essence as objects). MyObj was created to do the job of getting the folders and FSO was created to do the job of moving files. It looks like because MyObj and FSO do something with folders and files, they are set using CreateObject("Scripting.FileSystemObject"). Because I have 2 different kinds of files in Dropbox, I have an If statement that searches and moves only those files that start with "MMR -".

I tried to put in the indents in the code but this CSS code does strange things. I haven't figured it out yet.

Sub C_MoveMMRs_WithSubFolders()
'This version requires the "reports" folder to be created first (MakeMyFolderMMR macro).
'MMR files only will be moved to hard drive, according to path listed on "Start Page"
'This macro version will work with subfolders.
Dim FSO As Object
Dim FromPath As String
Dim ToPath As String
FromPath = Worksheets("Start Page").Range("D15")
ToPath = Worksheets("Start Page").Range("D16") & "reports"
Dim File As Variant
Dim MyObj As Object
Dim Folder As Object
Dim SubFolder As Object
Set FSO = CreateObject("scripting.filesystemobject")
Set MyObj = CreateObject("scripting.filesystemobject")
Set Folder = MyObj.GetFolder(FromPath)
For Each SubFolder In Folder.SubFolders
For Each File In SubFolder.Files
If Left(File.Name, 5) = "MMR -" Then
FSO.MoveFile Source:=FromPath & "" & SubFolder.Name & "" & File.Name, Destination:=ToPath & "" & File.Name
End If
MsgBox ("MMRs moved")
End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *