We help IT Professionals succeed at work.

How to rearrange script that utilizes Scripting.FileSystemObject that will close out the object with Nothing

stephenlecomptejr
on
How to rearrange the following code to where it closes out the Scripting.FileSystemObject?
I pulled this code from the following website:

https://stackoverflow.com/questions/31033820/filesystemobject-createfolder-to-create-directory-and-subdirectories

To me it's the best code to create a folder/ subfolder in VBA - I don't want to use any other suggestion, I don't want to get into any debates.

I'm not asking for another suggestion on how to create subdirectories within a directory if it doesn't exist - PLEASE!
I'm asking how to rearrange the script where I use  Set CFO = CreateObject("Scripting.FileSystemObject") but still have it close out with a Set CFO = Nothing

I keep trying and my adjusted script doesn't work.

Public Function CreateFolderRecursive(path As String) As Boolean
On Error GoTo Err_Proc

  Dim CFO As Object
  'If the path exists as a file, the function fails.
  Set CFO = CreateObject("Scripting.FileSystemObject")
  If CFO.FileExists(path) Then
    CreateFolderRecursive = False
    Exit Function
  End If

  'If the path already exists as a folder, don't do anything and return success.
  If CFO.FolderExists(path) Then
    CreateFolderRecursive = True
    Exit Function
  End If

  'recursively create the parent folder, then if successful create the top folder.
  If CreateFolderRecursive(CFO.GetParentFolderName(path)) Then
    If CFO.CreateFolder(path) Is Nothing Then
      CreateFolderRecursive = False
    Else
      CreateFolderRecursive = True
    End If
  Else
    CreateFolderRecursive = False
  End If
      
Exit_Proc:
  Exit Function
  
Err_Proc:
  Call LogError_feo(Err, Err.Description, "modCommon @ CreateFolderRecursive")
  Resume Exit_Proc

End Function

Open in new window

Comment
Watch Question

Bill PrewTest your restores, not your backups...
Expert of the Year 2019
Top Expert 2016

Commented:
What problem are you trying to solve.  Since you define the variable CFO in the procedure, as soon as it exits that variable will go out of scope and no longer be accessible / valid automatically.


»bp

Author

Commented:
"as soon as it exits that variable will go out of scope and no longer be accessible / valid automatically."

Are you sure?  lol.

Is there a way to prove that scientifically?  If you use CFO within another procedure and pass it to another one - I've seen it at times change the value.   I want to be sure CFO is closed in memory and no longer in use.
Test your restores, not your backups...
Expert of the Year 2019
Top Expert 2016
Commented:
And if you really do want to set that variable to Nothing for some reason, you could do the following...

Public Function CreateFolderRecursive(path As String) As Boolean
    On Error GoTo Err_Proc

    Dim CFO As Object
    Set CFO = CreateObject("Scripting.FileSystemObject")

    'If the path exists as a file, the function fails.
    If CFO.FileExists(path) Then
      CreateFolderRecursive = False
      Goto Exit_Proc:
    End If

    'If the path already exists as a folder, don't do anything and return success.
    If CFO.FolderExists(path) Then
      CreateFolderRecursive = True
      Goto Exit_Proc:
    End If

    'recursively create the parent folder, then if successful create the top folder.
    If CreateFolderRecursive(CFO.GetParentFolderName(path)) Then
      If CFO.CreateFolder(path) Is Nothing Then
        CreateFolderRecursive = False
      Else
        CreateFolderRecursive = True
      End If
    Else
      CreateFolderRecursive = False
    End If
          
    Exit_Proc:
      Set CFO = Nothing
      Exit Function
          
    Err_Proc:
      Call LogError_feo(Err, Err.Description, "modCommon @ CreateFolderRecursive")
      Resume Exit_Proc
      
End Function

Open in new window


»bp

Author

Commented:
Yea and they also say try to avoid GoTos too.... so that's why I didn't want to do above also...
but I guess I'll take it as the answer if there is not a better way.
Bill PrewTest your restores, not your backups...
Expert of the Year 2019
Top Expert 2016

Commented:
Are you sure?  lol.

Is there a way to prove that scientifically?  If you use CFO within another procedure and pass it to another one - I've seen it at times change the value.   I want to be sure CFO is closed in memory and no longer in use.

I'm sure that's how it should work, yes.  What goes on behind the scenes is another story, and if you have stored a reference to that object someplace that of course is a different matter as well.

You mention "pass it to another", do you mean as a parm then?  Because in your posted example there was none of that going on.

Typically if you will need to use the filesystem object then a good practice is to make it a public variable outside of all procedures, and then at the start of each procedure check if it Nothing and if so then create and assign the object.  But not delete it.  That way only one instance of the filesystem object is instantiated for performance reasons.  If you want an example let me know...


»bp

Author

Commented:
You mention "pass it to another", do you mean as a parm then? "

That's probably what happened to me in the past... and thus forcing me to the extreme of me now in practice making sure everything set to nothing when no longer needed.

"Typically if you will need to use the filesystem object then a good practice is to make it a public variable outside of all procedures, and then at the start of each procedure check if it Nothing and if so then create and assign the object.  "

When I've done that in a code module - in the past I've had issues with the global module keeping code from being ran like the error log....
Mark EdwardsChief Technology Officer

Commented:
One "best practice" tip I've gotten over the years is to have a "single point of exit" in your procedures so you can make sure you execute "cleanup code" before exiting the procedure.

Bill's recoded procedure above with the "GoTo Exit_Proc" does that.  Bill is an experienced developer and this practice is followed by a lot of top-level developers around the world, and it's even recommended in major development technique publications.

I don't know where you are getting all this "bad advice" like do not use "GoTo" (could only be said by an idiot) that's keeping you from being able to resolve your issues, but you better plan on having a lot of these if you insist on following "bad advice".

Author

Commented:
No I think this is great advice you both giving me.  I appreciate it very much.