Path not found when creating folder in Excel VBA

Question

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).

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?

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.

Dang, I wish I'd seen that sooner. I think I didn't search through the `MkDir` questions as thoroughly as the `CreateFolder` questions — Chic, Dec 09 '14 at 16:01
Might be worth keeping if search terms don't interchange well. Have to see what the Mod says. — Chrismas007, Dec 09 '14 at 16:05

score 5 · Accepted Answer · edited Mar 20 '17 at 10:04

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.

The fix is to call Trim() around your directory name:

  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)

rename "\\?\c:\<PATH HERE>\ 1 " "<NEW FILE NAME>"

Path not found when creating folder in Excel VBA

1 Answers1

Tip: