Thursday, May 26, 2011

How do you create folders with an Excel Spreadsheet macro?

At work I often find myself having to create multiple folders before beginning a Project. Many others may take the time consuming method of doing this by hand but you can actually save yourself a lot of time by using a simple VBA macro in an Excel Spreadsheet. One method of doing this is to start a new spreadsheet and save it as a macro-enabled workbook in the location where you want to create the multiple folders (such as C:\Work Directory\Parts List)

Next, in column A list all the names of the folders you want to create. Now, hold the "Alt" key down and press "F8" to open the Macros window. Enter "CreateFolders" and click the Create button which will open the VBA editor. You can copy and paste the following code:

Sub CreateFolders()

'create the folders where-ever the workbook is saved

Dim Rng As Range

Dim maxRows, maxCols, r, c As Integer

Set Rng = Selection

maxRows = Rng.Rows.Count

maxCols = Rng.Columns.Count

For c = 1 To maxCols

r = 1

Do While r <= maxRows

If Len(Dir(ActiveWorkbook.Path & "\" & Rng(r, c), vbDirectory)) = 0 Then

MkDir (ActiveWorkbook.Path & "\" & Rng(r, c))

On Error Resume Next

End If

r = r + 1


Next c

End Sub


Now all you have to do is highlight the cells and run the macro. Your folders are automatically created just like that! Save the macro and you can use it over and over again, saving you lots of time and impressing your fellow employees.