Comparing data directories

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