VBA & File Comparison

[newbieemployee]

I am new to VBA and Beyond Compare 2. I have written a VBA script to generate multiple file comparisons. For some reason the File-Differences Report is not being generated. The following is the VBA Code and Script I am using. Pleae forgive the VBA code, I have not cleaned it.

VBA
Sub BC2_Script2()
Dim Script1
Dim BC2
Dim WSHShell
Dim Result
Dim Script
Dim rightside
Dim leftside
Dim rightside2
Dim leftside2
Dim comparehtml
Dim comparehtml2
Dim szResult
Dim a
Dim dRow
Dim Result2

Sheets("Record Counts").Select
Calculate

BC2 = """C:\Program Files\Beyond Compare 2\BC2.exe"""
Script = "C:\TEMP\script.txt"
Set WSHShell = CreateObject("WScript.Shell")

dRow = Range("a" & Rows.Count).End(xlUp).Row

For a = 2 To dRow
    Let leftside = Range("K" & a)
    Let rightside = Range("L" & a)
    Let leftside2 = " " + leftside
    Let rightside2 = " " + rightside
    Let comparehtml = Range("j" & a)
    Let comparehtml2 = Range("i" & a)
    Call tempfile(comparehtml, leftside, rightside)
    Script1 = "@" & Script + " " + leftside + rightside + comparehtml

    Result2 = WSHShell.Run(BC2 + " /silent" + " /quickcompare" + leftside2 + rightside2, 0, True)
    Result = WSHShell.Run(BC2 + Script1, 0, True)
    
    
    If Result2 = 0 Then
    szResult = "Match"
    End If
 
    If Result2 = 1 Then
    szResult = "Similar"
    End If
 
    If Result2 = 2 Then
    szResult = "Mismatch"
    End If
 
    If Result2 = 3 Then
    szResult = "Error"
    End If
 
    Range("H1048576").Select
    Selection.End(xlUp).Offset(1, 0).Select
    Selection = szResult
 
    Range("j" & a).Select
    Selection = Trim(comparehtml2)
 
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
        comparehtml2, TextToDisplay:= _
        comparehtml2
        
        Next a
       
End Sub

Sub tempfile(comparehtml, leftside, rightside)
    Dim fso As New FileSystemObject
    Dim flds As Files
    Dim fld As folder
    Dim txtstr As TextStream
    
    Set flds = fso.GetFolder("C:\Windows\system").Files
    Set fld = fso.GetFolder("C:\Temp")
    Set txtstr = fld.CreateTextFile("script.txt", True)
    
    With txtstr
        .WriteLine "select files"
        .WriteLine "file-report layout:side-by-side options:display-mismatches output-to:""%3""" + " " + leftside + " " + rightside
        .Close
    End With
End Sub

When I use the following VBA, the compare works.

Sub BC3_Script()
Dim Script1
Dim BC2
Dim WSHShell
Dim Result
Dim Script
Dim rightside
Dim leftside
Dim comparehtml
Dim comparehtml2
Dim szResult
Dim szdate
Dim a
Dim dRow
Dim szload1
Dim szload2


szdate = Range("b5")

Sheets("Record Counts").Select

BC2 = """C:\Program Files\Beyond Compare 2\BC2.exe"""
Script = "C:\TEMP\script.txt"

Set WSHShell = CreateObject("WScript.Shell")

        
leftside = " ""T:\Accounting/ACCOUNT-2011-03-31.txt"""
rightside = " ""T:\Accounting/ACCOUNT-2010-12-31.txt"""
comparehtml = " ""C:\WINDOWS\Desktop\TEST.html"""
comparehtml2 = "C:\WINDOWS\Desktop\TEST.html"

Call tempfile(comparehtml, leftside, rightside)

Script1 = "@" & Script + leftside + rightside + comparehtml

Result = WSHShell.Run(BC2 + Script1, 0, True)

Result = WSHShell.Run(BC2 + " /silent" + " /quickcompare" + leftside + rightside, 0, True)

 If Result = 0 Then
 szResult = "Match"
 End If
 
 If Result = 1 Then
 szResult = "Similar"
 End If
 
 If Result = 2 Then
 szResult = "Mismatch"
 End If
 
 If Result = 3 Then
 szResult = "Error"
 End If
 
 MsgBox szResult
 
End Sub

Script
select files
file-report layout:side-by-side options:display-mismatches output-to:"%3"" + leftside + rightside

Any assistance would be greatly appreciated.

Thanks!

[Zoë]

I think you're missing some whitespace in your script command. Try changing line 35 to:

Script1 = " @" & Script & " " & leftside & " " & rightside & " " & comparehtml

You also don't need to regenerate a temp file every time. Just make your script this:

file-report layout:side-by-side options:display-mismatches output-to:"%3" "%1" "%2"

Then save it to a known location.

With those changes leftside2, rightside2, and the tempfile subroutine shouldn't be necessary.

[newbieemployee]

Craig,
Thank you for the quick response. I was able to get the code to work by doing the following. I atempted to create a permanent script, but that did not work, so I continued to create a temp file with the script. I know the code probably isn't the most efficient, but a working automation is required as soon as possible. I appreciate the help and am grateful for the forum.

Code:

Sub BC2_Script2()
Dim Script1
Dim BC2
Dim WSHShell
Dim Result
Dim Script
Dim rightside
Dim leftside
Dim rightside2
Dim leftside2
Dim comparehtml
Dim comparehtml2
Dim szResult
Dim a
Dim dRow
Dim result2
Dim rightside3
Dim leftside3
Dim comparehtml3

Sheets("Record Counts").Select
Calculate

BC2 = """C:\Program Files\Beyond Compare 2\BC2.exe"""
Script = "C:\TEMP\script.txt"
Set WSHShell = CreateObject("WScript.Shell")

dRow = Range("a" & Rows.Count).End(xlUp).Row



For a = 2 To dRow
    Let leftside = Range("F" & a)
    Let rightside = Range("g" & a)
    Let leftside2 = " " & """" & leftside & """"
    Let rightside2 = " " & """" & rightside & """"
    Let comparehtml = Range("i" & a)
    Let comparehtml2 = " " & """" & comparehtml & """"
    Let leftside3 = """" & leftside & """"
    Let rightside3 = """" & rightside & """"
    Let comparehtml3 = """" & comparehtml & """"
    
    Call tempfile(comparehtml3, leftside3, rightside3)
    
    Script1 = "@" & Script & " " & leftside2 & " " & rightside2 & " " & comparehtml

    result2 = WSHShell.Run(BC2 + " /silent" + " /quickcompare" + leftside2 + rightside2, 0, True)

    'Result = WSHShell.Run(BC2 + Script1 + leftside + rightside + comparehtml, 0, True)
    
    Result = WSHShell.Run(BC2 & Script1, 0, True)
    
    If result2 = 0 Then
 szResult = "Match"
 End If
 
 If result2 = 1 Then
 szResult = "Similar"
 End If
 
 If result2 = 2 Then
 szResult = "Mismatch"
 End If
 
 If result2 = 3 Then
 szResult = "Error"
 End If
 
 Range("H1048576").Select
 Selection.End(xlUp).Offset(1, 0).Select
 Selection = szResult
 
 Range("f" & a).Select
 Selection = Trim(leftside)
 
 ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
        leftside, TextToDisplay:= _
        leftside
        
 Range("g" & a).Select
 Selection = Trim(leftside)
 
 ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
        rightside, TextToDisplay:= _
        rightside
        
        Next a
       
End Sub

Code:
Sub tempfile(comparehtml3, leftside3, rightside3)
    Dim fso As New FileSystemObject
    Dim flds As Files
    Dim fld As folder
    Dim txtstr As TextStream
    
    Set flds = fso.GetFolder("C:\Windows\system").Files
    Set fld = fso.GetFolder("C:\Temp")
    Set txtstr = fld.CreateTextFile("script.txt", True)
    
    With txtstr
        .WriteLine "select files"
        .WriteLine "file-report layout:side-by-side options:display-mismatches output-to:" & comparehtml3 + " " + leftside3 + " " + rightside3
        .Close
    End With
End Sub