MkDir not accepting variable argument (EXCEL-VBA)

Question

Someone else asked a similar but distinct question. But their methods did not work for me.

I am writing a Sub to iterate over a list, turning the plaintext into hyperlinks based on the cell contents. It just appends the String "CellContents" to the end of a Constant "MyPath".

I wrote an If statement to make the linked directory if it doesn't exist, but I am getting myriad errors when I introduce it. The usual one at the moment is "Error 76: path not found".

            Path = Trim(MyPath & CellContents & "\")
            If Dir(Path, vbDirectory) = "" Then
                Call MkDir(Path)
            End If

I've also tried

             If Dir(Path, vbDirectory) = "" Then
                MkDir Path
             End If

I know for certain that the containing folder exists, and I've tried using Call MkDir and MkDir alone. I've also tried Trim()/no Trim() on Path, which does not solve it (and the inputs are strings so Str() causes its own error). I've also tried Len(Dir(Path)=0 in the conditional, none of which helps MkDir recognize that Path is a valid path! Why is it not being recognized correctly?

I'd start by putting the result of path into explorer and see if it pulls up the file — justkrys
– justkrys, Commented Feb 17, 2016 at 19:19
Note that this doesn't happen on every entry, just some (seem to be the same cells each time, but often they're the exact same String being used as one that DOES work!).Only SOME are causing the "no path" BS. And the Path is sometimes there, sometimes not, when the error throws; the ones I actually want it to trigger on WOULDN'T exist by definition however. — basaltanglia
– basaltanglia, Commented Feb 17, 2016 at 19:22
Using Call makes no difference whatsoever. It's just an obsolete keyword. — Mathieu Guindon
– Mathieu Guindon, Commented Feb 17, 2016 at 19:44

Gary's Student · Accepted Answer · 2016-02-17 19:56:27Z

0

This works for me:

Sub ytrewq()
    Dim s As String, s2 As String

    s = "C:\TestFolder\zzzz\"
    On Error Resume Next
        MkDir s
    On Error GoTo 0
End Sub

Examine the argument to Dir() before using it.

edited Feb 17, 2016 at 19:56

answered Feb 17, 2016 at 19:48

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

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

Comments

basaltanglia · Accepted Answer · 2016-02-24 15:40:36Z

My problem was that some of my input strings contained illegal characters under windows file system rules, I just turned them into spaces with Replace() and the problem resolved (This worked as a function or a sub)

      Function Replacement(Clean As String)
           Clean = Replace(Clean, Chr(10), " ")
           Clean = Replace(Clean, Chr(13), " ")
           Clean = Replace(Clean, Chr(13) & Chr(10), " ")
           Clean = Replace(Clean, "?", " ")
           Clean = Replace(Clean, "/", " ")
           Clean = Replace(Clean, """", " ")
           Clean = Replace(Clean, ":", " ")
           Clean = Replace(Clean, "<", " ")
           Clean = Replace(Clean, ">", " ")
           Clean = Replace(Clean, "|", " ")
           Clean = Replace(Clean, "*", " ")
           Replacement = Clean
      End Function

Thanks for your help (There's probably a better way to Replace() across all of those with some regex, but [] weren't working in my Replace() statements)

EDIT NOTE: To get best results, I added the Chr10/13/10&13 lines to remove various kinds of line-return metamarks, they are also disallowed in Windows directory names.

Collectives™ on Stack Overflow

MkDir not accepting variable argument (EXCEL-VBA)

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related