Excel macro MkDir syntax problem 1

[Dawber]

I am trying to create folders on my "C Drive" from an excel sheet, but the macro keeps terminating at the following line:

MkDir "C:\Water\Local Partners\Contracts\01 Tenders\" & site & " - " & project & " - " & ref & "\01 - Client"

I have used

Dim ref As Variant
ref = ActiveCell.Value

for the three variants "site", "project" & "ref"

I am obviously missing something, any assistance would be greatly appreciated.

 

[longhair]

Dawber,
did you try activecell.text?
if you hard code the mkdir statement does it work?
regards,
longhair

 

[GlennUK]

And what are the values of "site", "project" & "ref"?

Have you looked at what
"C:\Water\Local Partners\Contracts\01 Tenders\" & site & " - " & project & " - " & ref & "\01 - Client"
results in?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[GlennUK]

Actually, I'v just thought ... you probably need to create one level before the lower one, like this:

MkDir "C:\Water\Local Partners\Contracts\01 Tenders\" & site & " - " & project & " - " & ref
MkDir "C:\Water\Local Partners\Contracts\01 Tenders\" & site & " - " & project & " - " & ref & "\01 - Client"


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Dawber]

You are absolutely right, I split the task into two stages and it worked perfectly.

Thnks for the help.

 

[nasar]

Dawber,

This is how I would do this:

Sub test_mkdir()
Dim ref As Variant
Dim project As Variant
Dim site As Variant

With ActiveSheet
    ref = .Cells(2, 3)
    project = .Cells(2, 2)
    site = .Cells(2, 1)
    MkDir "C:\Water" 
    ChDir "C:\Water"
    MkDir site & " - " & project & " - " & ref
    MsgBox "Created sub dir " & site & " - " & project & " - " & ref
End With
End Sub

This assumes that you enter the site, project and ref data in cells A,B,C in row 2.

The MkDir command has a problem creating a directory tree and so you will need to create each level, Chdir and then create the next level. I have only shown two levels.

Good luck

 

[longhair]

GlennUK,
good catch - did not think of that. here's a star for helping me learn somthing new.
regards,
longhair