Creating macros to move or copy files

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.

[ap_divider color="#CCCCCC" style="double" thickness="3px" width="100%" mar_top="20px" mar_bot="20px"]

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

[ap_divider color="#CCCCCC" style="double" thickness="3px" width="100%" mar_top="20px" mar_bot="20px"]

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.

[ap_divider color="#CCCCCC" style="double" thickness="3px" width="100%" mar_top="20px" mar_bot="20px"]

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

[ap_divider color="#CCCCCC" style="double" thickness="3px" width="100%" mar_top="20px" mar_bot="20px"]