Excel VBA - MkDir returns "Path not Found" when using variable

Question

So here's the relevant snippet of my code (COPSFolder is a constant defined elsewhere):

Sub CreateReport(ByRef InfoArray() As String)

Dim BlankReport As Workbook
Dim ReportSheet As Worksheet

Dim ProjFolder As String

ProjFolder = COPSFolder & "InProgress\" & InfoArray(3) 
If Not Dir(ProjFolder, vbDirectory) = vbNullString Then
   Debug.Print ProjFolder
   MkDir ProjFolder <-----ERROR 76 HAPPENS HERE
End If

On the line indicated, ProjFolder & "InProgress\" is an existing directory. I'm trying to create a folder within it based on a value in an array of strings.

Here's what boggles me. If I replace "InfoArray(3)" with a string (ex. "12345") it works fine, but trying to use an element in the array will throw the error. The array is defined as a string everywhere it is referenced, and there are no type mismatches elsewhere in the Module.

edit: Public Const COPSFolder As String = "\\ktch163\COPS\"

edit2: here's another weird thing - if I replace InfoArray(3) with Str(InfoArray(3)) it seems towork. What I don't get is that the value of InfoArray(3) is already defined as a string. Also, it adds a space in front of the value. I can use Right(Str(InfoArray(3)), 5) I guess, but would like to figure out what the real issue is here.

edit3: as requested, here's how InfoArray() is populated:

    Public Function GetPartInfo(ByRef TextFilePath As String) As String()
'Opens text file, returns array with each element being one line in the text file
'(Text file contents delimited by line break character)

   Dim fso As FileSystemObject: Set fso = New FileSystemObject
   Dim Info As Variant
   Dim txtstream As Object
   Dim item as Variant

   Debug.Print TextFilePath

   Set txtstream = fso.OpenTextFile(TextFilePath, ForReading, False)

   GetPartInfo = Split(txtstream.ReadAll, Chr(10))

   For Each item In GetPartInfo
      item = Trim(item)
   Next
    End Function

Later on in the code - InfoArray = GetPartInfo(File.Path). (File.Path works fine, no errors when running GetPartInfo

What is the value of `COPSFolder` and `InfoArray(3)`? Also why `If Not Dir(strFullPath, vbDirectory)` and not `If Not Dir(CProjFolder, vbDirectory)`. Lastly that have 2 `thens` in the if line — Siddharth Rout, Dec 18 '13 at 15:00
COPSFolder is an existing directory. The value of InfoArray(3) is "33733", with type String. The If Not part was copy/pasted out of another subroutine that literally has that one line in it, so I forgot to change the parameters of Dir() in a way that would make sense. Same for the double Thens. — M.Banerjee, Dec 18 '13 at 15:05
`COPSFolder is an existing directory – user2471474 19 secs ago` I know you said that in the post but what is the `value`? — Siddharth Rout, Dec 18 '13 at 15:06
is it a network location? you may need to prefix network locations with `\\\` double backslashes. If it's not a network location then you need to specify the full path along with the drive. Also, how are you calling your `CreateReport` sub? — , Dec 18 '13 at 15:11
Yes, sorry there is a \\ there, didn't select the whole thing before pasting :P I can navigate manually to `"\\ktch163\COPS\InProgress\"` just fine. I am calling CreateReport via `Call CreateReport(InfoArray)` — M.Banerjee, Dec 18 '13 at 15:14
Run-time error '76': Path not found. Also, see edit2 in original post. — M.Banerjee, Dec 18 '13 at 15:17
It's impossible for the error to happen at the link you've pointed. Take the `ProjFolder` (the one debugged just before MkDir) and stick in the box in `Run` command from start menu ( or Explorer path ). Remove the last bit 33733 and see if you can navigate to that path — , Dec 18 '13 at 15:19
haha, I am not on point today. Error happens at mkdir line, very sorry for the confusion. Original post corrected. I can navigate to that path fine. As I mentioned, when ProjFolder is defined as `ProjFolder = COPSFolder & "InProgress\" & "33733"` it works fine ("33733" would be the actual value of `InfoArray(3)`). — M.Banerjee, Dec 18 '13 at 15:22
also while replying can you add "@" and the name like mehow has done above? This will ensure that we can get the alert when you post back — Siddharth Rout, Dec 18 '13 at 16:00
@SiddharthRout Noted. InfoArray() is populated by reading a text file line by line and adding each line to an element. Should I just post the entirety of my code? It's a few subroutines working together. — M.Banerjee, Dec 18 '13 at 16:06
@user2471474 You said you can navigate manually to the folder location, but can you manually create a new folder in that location? — aphoria, Dec 18 '13 at 16:17
@aphoria got a great point. Create a simple Sub like `Sub TEST()` new line `MkDir "\\ktch163\COPS\InProgress\33733"` new line `end sub` — , Dec 18 '13 at 16:23

score 4 · Accepted Answer · answered Dec 18 '13 at 16:23

4

The problem is that you are splitting using Chr(10) This is not removing the spaces. And hence when you are calling ProjFolder = COPSFolder & "InProgress\" & InfoArray(3), you have spaces in InfoArray(3)

You have 3 options

When you are creating the array, remove the spaces there OR
When you are assigning InfoArray = GetPartInfo(File.Path), remove the spaces there OR
Change the line ProjFolder = COPSFolder & "InProgress\" & InfoArray(3) to ProjFolder = COPSFolder & "InProgress\" & Trim(InfoArray(3))

answered Dec 18 '13 at 16:23

Siddharth Rout

147,039
17
206
250

1

+1 for beating me... just tried it myself with a chr(10). So (4) use `GetPartInfo = Split(txtstream.ReadAll, Chr(10))(0)` – Dec 18 '13 at 16:25
1

This is exactly what's going on! I'm going with 3) because I can't control the output of the text file at the moment and it's the easiest one. Quick note - I'm having to use `Trim(Str(InfoArray(3)))`, as just `Trim` causes the "path not found" error. But it will work for now and I'm ready to move on after spending hours on one line of code :P Thanks again! – M.Banerjee Dec 18 '13 at 17:42

Excel VBA - MkDir returns "Path not Found" when using variable

1 Answers1

Linked

Related