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, Feb 17 '16 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, Feb 17 '16 at 19:22
Using `Call` makes no difference whatsoever. It's just an obsolete keyword. — Mathieu Guindon, Feb 17 '16 at 19:44

Gary's Student · Answer 1 · 2016-02-17T19:56:27.847

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 '16 at 19:56

answered Feb 17 '16 at 19:48

Gary's Student

95,722
10
59
99

basaltanglia · Accepted Answer · 2016-02-24T15:40:36.803

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.

MkDir not accepting variable argument (EXCEL-VBA)

2 Answers2