Comparing data directories

  • Apr. 05, 2010

Introduction

Ben Hill recently posted some VBA-code on his nice tm1tutorials website to compare the contents of 2 TM1 data directories. Here is the code, you have to be logged in to view it. Basically, his code will loop through both folders and see whether the filename exists in the other folder.

Slowness

I tried his suggested code on 2 TM1 data directories on my PC. The code worked fine, but was rather slow to execute. The culprit is a double loop over the files in both folders. In fact, you only need 1 loop. When I rewrote his code, it executed in 1 second compared to 85 seconds previously. The TM1 data directories contain about 300 files.

Updated code

Sub btn_CheckDir()


' Wim Gielis
' https://www.wimgielis.com


'''''
' Custom VBA-code to compare 2 TM1 database directories
' See http://www.tm1tutorials.com/article.asp?ID=30
' 05/04/10
'''''

    [B7].CurrentRegion.Offset(1).ClearContents

    With CreateObject("scripting.filesystemobject")
        Set Fol1 = .Getfolder([B2])
        Set Fol2 = .Getfolder([B4])
    End With

    If Fol1 <> Fol2 Then

        For Each Fil In Fol1.Files
            If Dir(Fol2 & "\" & Fil.Name) = "" Then Range("B" & Rows.Count).End(xlUp). _
                Offset(1).Resize(, 3) = Array(Fil.Name, "Production", "Development")
        Next

        For Each Fil In Fol2.Files
            If Dir(Fol1 & "\" & Fil.Name) = "" Then Range("B" & Rows.Count).End(xlUp). _
                Offset(1).Resize(, 3) = Array(Fil.Name, "Development", "Production")
        Next

    End If

    MsgBox "Execution Complete"

End Sub




Homepage

Section contents

About Wim

Wim Gielis is a Business Intelligence consultant and Excel expert

Other links