Excel macro to create directories?

[TomK]

Hi,

New around here and not much of a VBA programmer, but hope you can help me
out.

I want to create a macro in Excel that will use the MkDir function to create
directories based on values contained in two columns. The values in Column A
define the 1st level directory and those in Column B define the 2nd level
directory.

For example:

The following data in columns A and B of the spreadsheet:

A B
one aaaa
one bbbb
one cccc
two dddd
two eeee
two ffff

Would create the following directories:

…\one\aaaa
…\one\bbbb
…\one\ccc

…\two\dddd
…\two\eeee
…\two\ffff

Any help would be greatly appreciated!

 

[Bob Phillips]

For i = 1 To Cells(Rows.Countr,"A").End(xlUp).Row
On Error Resume Next
MkDir "C:\" & Cells(i,"A").Value
On Error Goto 0
MkDir "C:\" & Cells(i,"A").Value & "\" & Cells(i,"B"),Value
Next i

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[TomK]

Thanks for the reply Bob. I tried your code, but got a runtime error on the
first line.

“Runtime error 438: Object doesn’t support this property or method.â€

Here is what I ran:

Sub test()

For i = 1 To Cells(Rows.Countr, "A").End(xlUp).Row
On Error Resume Next
MkDir "C:\test\" & Cells(i, "A").Value
On Error GoTo 0
MkDir "C:\test\" & Cells(i, "A").Value & "\" & Cells(i, "B").Value
Next i

End Sub

For i = 1 To Cells(Rows.Countr,"A").End(xlUp).Row
On Error Resume Next
MkDir "C:\" & Cells(i,"A").Value
On Error Goto 0
MkDir "C:\" & Cells(i,"A").Value & "\" & Cells(i,"B"),Value
Next i

Hi,

[quoted text clipped - 29 lines]

Any help would be greatly appreciated!

 

[TomK]

Never mind, I found the typo (doh!).
Thanks Bob! Works like a champ!

Thanks for the reply Bob. I tried your code, but got a runtime error on the
first line.

“Runtime error 438: Object doesn’t support this property or method.â€

Here is what I ran:

Sub test()

For i = 1 To Cells(Rows.Countr, "A").End(xlUp).Row
On Error Resume Next
MkDir "C:\test\" & Cells(i, "A").Value
On Error GoTo 0
MkDir "C:\test\" & Cells(i, "A").Value & "\" & Cells(i, "B").Value
Next i

End Sub

For i = 1 To Cells(Rows.Countr,"A").End(xlUp).Row
On Error Resume Next

[quoted text clipped - 8 lines]