PDA

View Full Version : [SOLVED] Make File Folders using data from column range



Zlerp
08-21-2014, 11:59 AM
Hello All!

I need help with using the VBA on excel to create folders with specific names in the file loaction in which the excel workbook is saved.

For example:

If i have my excel workbook saved in the file location C:\Users\zlerp\Desktop

--And in this workbook contains data in column A from A2 to Last used Row in A (the range changes)

the data is as follows:
A2 = 1234
A3 = ABCD
A4 = XXXX
A5 = YYYY

What macro can be created that will Make Directories (Folders) in C:\Users\zlerp\Desktop (where the workbook is saved)
and have folders named:
1234
ABCD
XXXX
YYYY


So the final result is to have 4 different folders in the same file location as the workbook (which will be a delimited .txt in my situation) named whatever is in Column A from A2 to the last row used by column A.

PS: THERE WILL BE DUPLICATE VALUES IN COLUMN A.

Thank you for your help!

ranman256
08-21-2014, 12:36 PM
Sub MakeMyFolders()
Dim vDir, vRoot
vRoot = ActiveWorkbook.Path & "\"
If vRoot = "\" Then
MsgBox "No folder given", vbCritical, "Save the File"
Exit Sub
End If

Range("A2").Select
While ActiveCell.Value <> ""
vDir = vRoot & ActiveCell.Value
MakeDir vDir

ActiveCell.Offset(1, 0).Select 'next row
Wend
End Sub
Private Sub MakeDir(ByVal pvDir)
Dim fso
On Error Resume Next
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FolderExists(pvDir) Then fso.CreateFolder pvDir
Set fso = Nothing
End Sub

Zlerp
08-21-2014, 12:45 PM
Wow! Thank you soo much! It works perfectly!