Excel VBA - MkDir返回“找不到路径”使用变量时 [英] Excel VBA - MkDir returns "Path not Found" when using variable

问题描述

所以这里是我的代码的相关代码段（COPSFolder是其他地方定义的常量）：

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

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

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.

这是什么让我失望。如果我用字符串（例如12345）替换InfoArray（3），它的工作正常，但尝试使用数组中的一个元素将抛出错误。该数组被定义为引用的任何地方的字符串，并且模块中的其他地方没有类型不匹配。

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.

编辑： Public Const COPSFolder As String =\\ktch163\COPS\

edit2：另外一个奇怪的事情 - 如果我替换 InfoArray（3）与 Str（InfoArray（3））似乎工作。我不知道的是InfoArray（3）的值已经被定义为一个字符串。此外，它在值前添加一个空格。我可以使用 Right（Str（InfoArray（3）），5）我想，但想知道这里真正的问题。

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：根据要求，以下是InfoArray（）的填充方式：

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

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

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

推荐答案

问题是您正在使用 Chr（10）这不是删除空格，因此当你调用 ProjFolder = COPSFolder&InProgress\& InfoArray（3），您在 InfoArray（3）中有空格

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)

您有3个选项

1. 当您创建阵列时，请删除那里的空格或

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

当您分配 InfoArray = GetPartInfo（File.Path）时，删除那里的空格或

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

更改ProjFolder = COPSFolder&InProgress\& InfoArray（3）到 ProjFolder = Trim（InfoArray（3））

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

这篇关于Excel VBA - MkDir返回“找不到路径”使用变量时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！