1 hour for $100k value - technical steps

As mentioned in the attached post, the problem we came across is as follows:

Each engagement requires a list of folders and sub-folders in a specific structure for the audit team to manage audit information.

Staff used to copy and paste all then go into each folder and clear out prior year files, or manually create each folder and sub-folder. This process took an estimated 30-60 minutes for each and every engagement.

So how did we solve it?

A simple line in the cmd was able to execute the desired result (all folders, without any files)

The line is robocopy "source" "destination" /e /xf *"

e.g.

Now, this is not very palatable to the average user, so we opted for a simple Excel UI + VBA code to execute the above.

Start off with a new Excel file, add an input for the source and one for the destination, and add a button (developer tab) to run the code.

Going into Visual basic, we added a new module and started our simple procedure.

Our goal is as follows: Extract the file paths from the user, and send those paths with the required code to the cmd.

The code is as follows:

Option Explicit

Dim varFrom As String

Dim varTo As String

Dim Command As String

???varFrom = Range("F11").Value

??varTo = Range("F12").Value

??Command = """robocopy " + varFrom + " " + varTo + " /e /xf *"""

??Call Shell("cmd.exe /S /K" & Command, vbNormalFocus)

End Sub

Finally, assign the Sub Click to the button you placed on your document earlier.

A caveat on the above, some refinement is likely necessary, and I'm sure some of you can find other solutions/ suggestions to this problem.

That is the beauty of this, with the correct mindset, manual work like this should not be possible.

DM and I'll happily share the Excel file with you.