Announcement

Collapse
No announcement yet.

VBA & File Comparison

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA & File Comparison

    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.

    Code:
    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.

    Code:
    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!
    Last edited by ZoŽ; 24-Mar-2011, 03:43 PM. Reason: Added [CODE] tags

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

    Code:
    Script1 = " @" & Script & " " & leftside & " " & rightside & " " & comparehtml
    You also don't need to regenerate a temp file every time. Just make your script this:

    Code:
    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.
    ZoŽ P Scooter Software

    Comment


    • #3
      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:
      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
      Last edited by ZoŽ; 28-Mar-2011, 12:44 PM.

      Comment

      Working...
      X