在 Excel VBA 中创建文件夹时找不到路径
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27382081/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Path not found when creating folder in Excel VBA
提问by Chic
I have a VBA subroutine to loop through the rows of an excel spreadsheet and copy a file from the path stored in one cell to a path made up of information from several other cells. Much of the time a folder will need to be created for the file but it's only one level deeper (not trying to However, when I run it I sometimes will get a runtime error 76 path not found. When I look at the folder in Windows Explorer the folder appears, but is slightly transparent (like a file that is being written to).
我有一个 VBA 子例程来遍历 Excel 电子表格的行，并将文件从存储在一个单元格中的路径复制到由来自其他几个单元格的信息组成的路径。很多时候需要为文件创建一个文件夹，但它只是更深一层（不尝试但是，当我运行它时，我有时会收到运行时错误76 path not found。当我在 Windows 资源管理器中查看文件夹时，文件夹出现，但稍微透明（就像正在写入的文件）。
Why am I running into this error at fso.Createfolder strDirPath? I'm guessing this has to do with timing because when I run the script again it can pass over the file just fine. Is there some way to check that the folder is ready?
为什么我在 遇到这个错误fso.Createfolder strDirPath？我猜这与时间有关，因为当我再次运行脚本时，它可以很好地传递文件。有什么方法可以检查文件夹是否准备好？
Sub CopyFiles()
' Copy to location [root_folder]\company_name\contract_no'_'file_name
Dim strRootFolder, strCompany, strContract, strFileName, strDirPath
Dim strFullPath, strFromPath, intRow
strRootFolder = "C:\...\DestinationFolder\"
intRow = 2
Dim fso As New FileSystemObject
'Loop through rows
Range("C" & 2).Select 'First row to check (column always filled)
Do Until IsEmpty(ActiveCell) ' Loop through till end of spreadsheet
strFromPath = objSheet.Range("C" & intRow).Value
' Replace "/" characters in company names with "_"
strCompany = Replace(objSheet.Range("E" & intRow).Value, "/", "_")
strContract = objSheet.Range("A" & intRow).Value & "_"
' Replace "#" in file names with "0"
strFileName = Replace(objSheet.Range("B" & intRow).Value, "#", "0")
strDirPath = strRootFolder & strCompany & "\"
strFullPath = strDirPath & strContract & strFileName
' Create directory if it does not exist
If Not fso.FolderExists(strDirPath) Then
fso.Createfolder strDirPath ' !!! This is where the error is !!!
End If
' Copy file
fso.CopyFile strFromPath, strFullPath, False
intRow = intRow + 1
ActiveCell.Offset(1, 0).Select ' drop one to check if filled
Loop
End Sub
Note:This is not because of a backslash in the directory name. The code replaces backslashes and there are no forward slashes in the input.
注意：这不是因为目录名称中有反斜杠。代码替换了反斜杠，输入中没有正斜杠。
回答by Chic
The issue is that the directory that is being created ends in a space. In Windows explorer, if you create a folder with a space at the end it automatically trims the name. However, in VBA it isn't automatically done.
问题是正在创建的目录以空格结尾。在 Windows 资源管理器中，如果您创建一个末尾有空格的文件夹，它会自动修剪名称。但是，在 VBA 中它不会自动完成。
The fix is to call Trim()around your directory name:
解决方法是调用Trim()您的目录名称：
strDirPath = Trim(strRootFolder & strCompany) & "\"
Tip:
提示：
The folders with trailing spaces were created but will cause issues in Windows. To rename or remove them you will need to use the command line with a network path syntax. (See Rename/Delete Windows (x64) folder with leading and trailing space)
带有尾随空格的文件夹已创建，但会导致 Windows 出现问题。要重命名或删除它们，您需要使用带有网络路径语法的命令行。（请参阅重命名/删除带有前导和尾随空格的 Windows (x64) 文件夹）
rename "\?\c:\<PATH HERE>\ 1 " "<NEW FILE NAME>"
