Excel VBA MkDir Run Time error

Question

I want to create a folder at the address that is stored in the cell B6. The address is: H:\jpDesk\Desktop\Test Project

Sub SetUpLocalFolder()

Workbooks("Robot Model.xlsm").Activate
LocalPath = ActiveWorkbook.Worksheets("Preparation").Range("B6").Value
Debug.Print LocalPath
If Right(LocalPath, 1) <> "\" Then LocalPath = LocalPath & "\"

'check if the folder is already created
If Len(Dir(""" & LocalPath & """, vbDirectory)) = 0 Then
    MkDir """ & LocalPath & """ 'Error shows here
    MsgBox ("The local folder is successfully created.")
End If

End Sub

The error shows "runtime error '76'. Path not found" but Debug.Print shows the correct address. If I change the code into MkDir "H:\jpDesk\Desktop\Test Project", then everything works. Can someone please let me know why?

I'd highly recommend using the Scripting.FileSystemObject instead - it is much more robust than the ancient legacy file handling methods. — Comintern
– Comintern, Commented Oct 31, 2016 at 19:49

Rdster · Accepted Answer · 2016-10-31 19:45:40Z

2

Because you don't need the """ & and the & """.

OR that folder already exists.

answered Oct 31, 2016 at 19:45

Rdster

1,8722 gold badges19 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

vivi11130704 Over a year ago

Oops that's right. But why? All the online examples have double quotes around the path address.

YowE3K Over a year ago

@vivi11130704 - The online examples probably say something like Dir("C:\Temp", vbDirectory). In that case "C:\Temp" is a String literal which refers to a memory location containing the characters C:\Temp. In your case you have a String variable called LocalPath which refers to a memory location containing characters C:\Temp. So you would replace the literal "C:\Temp" in the example with your variable of LocalPath.

vivi11130704 Over a year ago

I see what you say. Thanks!

Collectives™ on Stack Overflow

Excel VBA MkDir Run Time error

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related