Excel macro to create directories?

T

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!
 
B

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)
 
T

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


Bob said:
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
[quoted text clipped - 29 lines]
Any help would be greatly appreciated!
 
T

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]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Filling in some blanks to a data table 3
List of Direct and Indirect Reports 7
Acronym Macro 11
Acronym Macro 2 7
Deleting EXACT duplicate rows 9
macro question 0
Macro question 1
Macro question 0

Top