1

I need to automate comparison between two sheets and then email if mismatch during a batch process. My sheet looks like this:

                                       Head1    Head1   Head1
                                       Column1  Column2 Column3
                                       Head2    Head2   Head2
                                       Head3    Head3   Head3
                                       Head4    Head4   Head4
Info1   Info2   Info3   Info4   Row1    100     1,001   2,001 
Info1   Info2   Info3   Info4   Row2    101     1,002   2,002 
Info1   Info2   Info3   Info4   Row3    102     1,003   2,003 
Info1   Info2   Info3   Info4   Row3    103     1,004   2,004 
Info1   Info2   Info3   Info4   Row4    104     1,005   2,005

So while I have the vba code (which currently uses msgbox but will be amended to email recipients):

Option Explicit
Dim ws As Worksheet
Dim rw As Long, col As Long, lastrw As Long, lastcol As Long

Sub CompareData()
For Each ws In Workbooks("Book1.xlsm").Worksheets
    lastrw = ws.Cells.SpecialCells(xlCellTypeLastCell).Row
    lastcol = ws.Cells.SpecialCells(xlCellTypeLastCell).Column
    rw = 6
    With Workbooks("Book2.xlsm").Sheets(ws.Name)
        Do Until rw > lastrw
            col = 6
            Do Until col > lastcol
                If ws.Cells(rw, col).Value <> .Cells(rw, col).Value Then
                    MsgBox "Values don't match!"
                    Exit Sub
                End If
                col = col + 1
            Loop
            rw = rw + 1
        Loop
    End With
Next
MsgBox "All sheets match"
End Sub

Now I am not sure how to achieve this via vbscipt. Then I can invoke the script using "cscript myvbscript.vbs" in batch to do this automatically.

Nona Shah
  • 59
  • 1
  • 10
  • 2
    Translating VBA to VBScript is [relatively easy](http://www.planetcobalt.net/sdb/vba2vbs.shtml). – Ansgar Wiechers Jan 06 '15 at 11:00
  • Here's a question with an answer about how to send email using VBScript without relying on Outlook: http://stackoverflow.com/questions/7041938/vbscript-to-send-email-without-running-outlook – James L. Jan 07 '15 at 04:07

0 Answers0