Path not found when creating folder in Excel VBA [duplicate]

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
– Chic, Commented Dec 9, 2014 at 16:01
Might be worth keeping if search terms don't interchange well. Have to see what the Mod says. — Chrismas007
– Chrismas007, Commented Dec 9, 2014 at 16:05

Community · Accepted Answer · 2017-03-20 10:04:24Z

5

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>"

edited Mar 20, 2017 at 10:04

CommunityBot

11 silver badge

answered Dec 9, 2014 at 15:40

Chic

10.7k5 gold badges36 silver badges63 bronze badges

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

Collectives™ on Stack Overflow

Path not found when creating folder in Excel VBA [duplicate]

1 Answer 1

Tip:

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Tip:

Comments

Linked

Related