contrib/other/diff-scripts/diff-xls.vbs

   1 ' extensions: xls;xlsx;xlsm;xlsb;xlam
   2 '
   3 ' TortoiseSVN Diff script for Excel files
   4 '
   5 ' Copyright (C) 2004-2008 the TortoiseSVN team
   6 ' This file is distributed under the same license as TortoiseSVN
   7 '
   8 ' Last commit by:
   9 ' $Author: tortoisesvn $
  10 ' $Date: 2009-10-18 09:25:24 +0200 (So, 18. Okt 2009) $
  11 ' $Rev: 17472 $
  12 '
  13 ' Authors:
  14 ' Michael Joras <michael@joras.net>, 2008
  15 ' Suraj Barkale, 2006
  16 '
  17
  18 dim objExcelApp, objArgs, objScript, objBaseDoc, objNewDoc, objWorkSheet, i
  19
  20 Set objArgs = WScript.Arguments
  21 num = objArgs.Count
  22 if num < 2 then
  23    MsgBox "Usage: [CScript | WScript] compare.vbs base.doc new.doc", vbExclamation, "Invalid arguments"
  24    WScript.Quit 1
  25 end if
  26
  27 sBaseDoc = objArgs(0)
  28 sNewDoc = objArgs(1)
  29
  30 Set objScript = CreateObject("Scripting.FileSystemObject")
  31 If objScript.FileExists(sBaseDoc) = False Then
  32     MsgBox "File " + sBaseDoc +" does not exist.  Cannot compare the documents.", vbExclamation, "File not found"
  33     Wscript.Quit 1
  34 End If
  35 If objScript.FileExists(sNewDoc) = False Then
  36     MsgBox "File " + sNewDoc +" does not exist.  Cannot compare the documents.", vbExclamation, "File not found"
  37     Wscript.Quit 1
  38 End If
  39
  40 Set objScript = Nothing
  41
  42 On Error Resume Next
  43 Set objExcelApp = Wscript.CreateObject("Excel.Application")
  44 If Err.Number <> 0 Then
  45    Wscript.Echo "You must have Excel installed to perform this operation."
  46    Wscript.Quit 1
  47 End If
  48
  49 'Open base excel sheet
  50 objExcelApp.Workbooks.Open sBaseDoc
  51 'Open new excel sheet
  52 objExcelApp.Workbooks.Open sNewDoc
  53 'Show Excel window
  54 objExcelApp.Visible = True
  55 'Create a compare side by side view
  56 objExcelApp.Windows.CompareSideBySideWith(objExcelApp.Windows(2).Caption)
  57 If Err.Number <> 0 Then
  58         objExcelApp.Application.WindowState = xlMaximized
  59         objExcelApp.Windows.Arrange(-4128)
  60 End If
  61
  62 'Mark differences in sNewDoc red
  63 i = 1
  64
  65 For Each objWorkSheet In objExcelApp.Workbooks(2).Worksheets
  66
  67                 objworksheet.Cells.FormatConditions.Delete
  68
  69                 objExcelApp.Workbooks(1).Sheets(i).Copy ,objExcelApp.Workbooks(2).Sheets(objExcelApp.Workbooks(2).Sheets.Count)
  70
  71                 objExcelApp.Workbooks(2).Sheets(objExcelApp.Workbooks(2).Sheets.Count).Name = "Dummy_for_Comparison" & i
  72
  73                 objworksheet.Activate
  74                 'To create a local formula the cell A1 is used
  75                 original_content = objworksheet.Cells(1,1).Formula
  76                 String sFormula
  77                 'objworksheet.Cells(1,1).Formula = "=INDIRECT(""" & objExcelApp.Workbooks(2).Sheets(i).name & " (2)"& "!""&ADDRESS(ROW(),COLUMN()))"
  78                 objworksheet.Cells(1,1).Formula = "=INDIRECT(""Dummy_for_Comparison" & i & "!""&ADDRESS(ROW(),COLUMN()))"
  79                 sFormula = objworksheet.Cells(1,1).FormulaLocal
  80
  81                 objworksheet.Cells(1,1).Formula = original_content
  82                 'with the local formula the conditional formatting is used to mark the cells that are different
  83                 const xlCellValue = 1
  84                 const xlNotEqual = 4
  85                 objworksheet.Cells.FormatConditions.Add xlCellValue, xlNotEqual, sFormula
  86                 objworksheet.Cells.FormatConditions(1).Interior.ColorIndex = 3
  87
  88         i = i + 1
  89 next