Excel VBA - MkDir 在使用变量时返回“找不到路径”

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

提问by M.Banerjee

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

所以这是我的代码的相关片段（COPSFolder 是在别处定义的常量）：

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.

在指示的行上，ProjFolder & "InProgress\"是一个现有目录。我正在尝试根据字符串数组中的值在其中创建一个文件夹。

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.

这就是让我感到困惑的地方。如果我用字符串（例如“12345”）替换“InfoArray(3)”，它工作正常，但尝试使用数组中的元素会引发错误。该数组在它被引用的任何地方都被定义为一个字符串，并且模块中的其他地方没有类型不匹配。

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

编辑： 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.

编辑2：这是另一件奇怪的事情 - 如果我InfoArray(3)用Str(InfoArray(3))它替换似乎有效。我不明白的是 InfoArray(3) 的值已经定义为一个字符串。此外，它还在值前面添加了一个空格。我可以使用Right(Str(InfoArray(3)), 5)我猜，但想弄清楚这里的真正问题是什么。

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

edit3：根据要求，InfoArray() 的填充方式如下：

    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

稍后在代码中 - InfoArray = GetPartInfo(File.Path). （File.Path 工作正常，运行 GetPartInfo 时没有错误

回答by Siddharth Rout

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)

问题是您正在使用Chr(10)This 不是删除空格进行拆分。因此当你打电话时ProjFolder = COPSFolder & "InProgress\" & InfoArray(3)，你有空格InfoArray(3)

You have 3 options

你有3个选择

1. When you are creating the array, remove the spaces there OR

2. When you are assigning InfoArray = GetPartInfo(File.Path), remove the spaces there OR

3. Change the line ProjFolder = COPSFolder & "InProgress\" & InfoArray(3)to ProjFolder = COPSFolder & "InProgress\" & Trim(InfoArray(3))

1. 创建数组时，删除那里的空格或

2. 分配时InfoArray = GetPartInfo(File.Path)，删除那里的空格或

3. 将行更改ProjFolder = COPSFolder & "InProgress\" & InfoArray(3)为ProjFolder = COPSFolder & "InProgress\" & Trim(InfoArray(3))