preload

Checking structure of created folders (Excel/VBA)

Posted by Albert Gareev on May 05, 2010 | Categories: Back-endFile System OperationsSource code

In the previous post I warned about possible synchronization issues while copying a complex and/or large file-folder structure. To ensure reliability of the operation performed we may verify the structure that was created.

We need to answer the following questions before designing the script.

  • Which folders and files should it verify?
  • Which way should it verify specified object?
  • Where this information is stored?
  • What is the format of data provided?

 

For this example, we make the following assumptions.

  • A file named “folders.txt” is stored in the root folder of a template and automatically copied with it while creating a user folder.
  • The file is in raw text format
  • Only folders are specified

Assuming that we use sample structure from the previous example as a baseline, “folders.txt” file will contain the following lines.

Application Data\Application1
Application Data\Application2
Application Data\Application1\INI
Documents
Documents\Back-up

Order of the folders specified is not important.

CheckFolderStructure function provided below performs a sequential checking.


Public Function CheckFolderStructure(ByVal sTargetFolder)
Dim boolRC
Dim sCheckFile, objCheckFile
Dim sCheckLine, sCheckPath

Set objFSO = CreateObject("Scripting.FileSystemObject")

boolRC = objFSO.FolderExists(sTargetFolder)
If Not boolRC Then
    Set objFSO = Nothing
    CheckFolderStructure = False
    Exit Function
End If

sCheckFile = sTargetFolder & "\folders.txt"
boolRC = objFSO.FileExists(sCheckFile)
If Not boolRC Then
    Set objFSO = Nothing
    CheckFolderStructure = False
    Exit Function
End If

Set objCheckFile = objFSO.OpenTextFile(sCheckFile, 1)

Do While (Not objCheckFile.AtEndOfStream)
    sCheckLine = objCheckFile.ReadLine()
    sCheckPath = sTargetFolder & "\" & sCheckLine
    boolRC = objFSO.FolderExists(sCheckPath)
    If Not boolRC Then
        objCheckFile.Close
        Set objCheckFile = Nothing
        Set objFSO = Nothing
        CheckFolderStructure = False
        Exit Function
    End If
Loop

objCheckFile.Close
Set objCheckFile = Nothing
Set objFSO = Nothing
CheckFolderStructure = True

End Function


  • One response to "Checking structure of created folders (Excel/VBA)"

  • Tom
    8th May 2010 at 11:41

    Great post as usual!

Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported
This work by Albert Gareev is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported.