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
– Siddharth Rout, Commented Dec 18, 2013 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
– M.Banerjee, Commented Dec 18, 2013 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
– Siddharth Rout, Commented Dec 18, 2013 at 15:06
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
– Siddharth Rout, Commented Dec 18, 2013 at 16:00
@user2471474 You said you can navigate manually to the folder location, but can you manually create a new folder in that location? — aphoria
– aphoria, Commented Dec 18, 2013 at 16:17

Siddharth Rout · Accepted Answer · 2013-12-18 16:23:46Z

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, 2013 at 16:23

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

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

2 Comments

user2140173 Over a year ago

+1 for beating me... just tried it myself with a chr(10). So (4) use GetPartInfo = Split(txtstream.ReadAll, Chr(10))(0)

M.Banerjee Over a year ago

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!

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related