Login | Register
My pages Projects Community openCollabNet

Discussions > users > Excel diff script (extended version)

Project highlights: :. Download .: :. Support .: :. FAQ .: :. Translations .: :. Donate .: :. Report Bug .:

tortoisesvn
Discussion topic

Back to topic list

Excel diff script (extended version)

Author cytrynski at web dot de
Full name cytrynski at web dot de
Date 2007-04-25 12:16:07 PDT
Message Hello,

I have extended the Excel diff script (.vbs) that was created by Suraj Barkale. In the new version the cells in the first worksheet of workbook 1, that are different to the cells in the first worksheet of workbook 2, are marked in red. Therefore it is much easier to find the differences in the side-by-side view. I have tested the script in Excel 2003 and it works fine. I use conditional formatting to mark the cells that are different. This method is very fast (I tested several ways). The script also works with two Excel workbooks that are dragged&dropped to the script's icon in the Windows Explorer. I hope you enjoy it!

Stefan Cytrynski
Stuttgart, Germany

--------------------​--------------------​--------------------​--------------------​--------------------​---

dim objExcelApp, objArgs, objScript, objBaseDoc, objNewDoc

Set objArgs = WScript.Arguments
num = objArgs.Count
if num < 2 then
   MsgBox "Usage: [CScript | WScript] compare.vbs base.doc new.doc", vbExclamation, "Invalid arguments"
   WScript.Quit 1
end if

sBaseDoc = objArgs(0)
sNewDoc = objArgs(1)

Set objScript = CreatexObject("Scrip​ting.FileSystemxObje​ct")
If objScript.FileExists(sBaseDoc) = False Then
    MsgBox "File " + sBaseDoc +" does not exist. Cannot compare the documents.", vbExclamation, "File not found"
    Wscript.Quit 1
End If
If objScript.FileExists(sNewDoc) = False Then
    MsgBox "File " + sNewDoc +" does not exist. Cannot compare the documents.", vbExclamation, "File not found"
    Wscript.Quit 1
End If

Set objScript = Nothing

On Error Resume Next
Set objExcelApp = Wscript.CreatexObjec​t("Excel.Application​")
If Err.Number <> 0 Then
   Wscript.Echo "You must have Excel installed to perform this operation."
   Wscript.Quit 1
End If

'Open base excel sheet
objExcelApp.Workbooks.Open sBaseDoc
'Open new excel sheet
objExcelApp.Workbooks.Open sNewDoc
'Show Excel window
objExcelApp.Visible = True
'Create a compare side by side view
objExcelApp.Windows.​CompareSideBySideWit​h(objExcelApp.Window​s(2).Caption)
If Err.Number <> 0 Then
   objExcelApp.DisplayAlerts = False
   objExcelApp.Quit()
   Wscript.Echo "You must have Excel 2003 or later installed to use compare side-by-side feature."
   Wscript.Quit 1
End If

'Mark differences in sNewDoc red
objExcelApp.Workbook​s(2).Sheets(1).Cells​.FormatConditions.De​lete
objExcelApp.Workbook​s(1).Sheets(1).Copy ,objExcelApp.Workboo​ks(2).Sheets(objExce​lApp.Workbooks(2).Sh​eets.Count)
objExcelApp.Workbook​s(2).Sheets(objExcel​App.Workbooks(2).She​ets.Count).Name = "Dummy_for_Comparison"
objExcelApp.Workbook​s(2).Sheets(1).Activ​ate
'To create a local formula the cell A1 is used
original_content = objExcelApp.Workbook​s(2).Sheets(1).Cells​(1,1).Formula
String sFormula
objExcelApp.Workbook​s(2).Sheets(1).Cells​(1,1).Formula = "=INDIRECT(""Dummy_f​or_Comparison" & "!""&ADDRESS(ROW​(),COLUMN()))"
sFormula = objExcelApp.Workbook​s(2).Sheets(1).Cells​(1,1).FormulaLocal
objExcelApp.Workbook​s(2).Sheets(1).Cells​(1,1).Formula = original_content
'with the local formula the conditional formatting is used to mark the cells that are different
const xlCellValue = 1
const xlNotEqual = 4
objExcelApp.Workbook​s(2).Sheets(1).Cells​.FormatConditions.Ad​d xlCellValue, xlNotEqual, sFormula
objExcelApp.Workbook​s(2).Sheets(1).Cells​.FormatConditions(1)​.Interior.ColorIndex​ = 3





____________________​____________________​____________________​___
SMS schreiben mit WEB.DE FreeMail - einfach, schnell und
kostenguenstig. Jetzt gleich testen! http://f.web.de/?mc=021192

« Previous message in topic | 1 of 21 | Next message in topic »

Messages

Show all messages in topic

Excel diff script (extended version) cytrynski at web dot de cytrynski at web dot de 2007-04-25 12:16:07 PDT
     Re: Excel diff script (extended version) steveking Stefan Küng 2007-04-26 11:07:21 PDT
     Re: Excel diff script (extended version) Andy Levy <andy dot levy at gmail dot com> Andy Levy <andy dot levy at gmail dot com> 2007-04-26 19:37:50 PDT
         Re: Excel diff script (extended version) jared_silva Jared Silva 2007-04-26 19:58:50 PDT
     Re: Excel diff script (extended version) jared_silva Jared Silva 2007-04-30 15:09:46 PDT
         Re: Excel diff script (extended version) schaefer Tobias Schaefer 2007-05-04 11:18:24 PDT
         Re: Excel diff script (extended version) jared_silva Jared Silva 2007-05-10 15:03:41 PDT
             Re: Excel diff script (extended version) lorenznl lorenz 2007-05-10 23:41:15 PDT
                 Re: Excel diff script (extended version) jared_silva Jared Silva 2007-05-11 06:52:14 PDT
                     Re: Excel diff script (extended version) Andy Levy <andy dot levy at gmail dot com> Andy Levy <andy dot levy at gmail dot com> 2007-05-11 07:00:22 PDT
                     Re: Excel diff script (extended version) lorenznl lorenz 2007-05-13 23:32:43 PDT
                         Re: Excel diff script (extended version) jared_silva Jared Silva 2007-05-24 15:21:53 PDT
                             Re: Excel diff script (extended version) schaefer Tobias Schaefer 2007-05-24 15:49:06 PDT
                                 Re: Excel diff script (extended version) kippspan <kippspanbauer at yahoo dot com> kippspan <kippspanbauer at yahoo dot com> 2007-05-29 10:36:24 PDT
                                     Re: Excel diff script (extended version) steveking Stefan Küng 2007-05-30 10:56:37 PDT
                                         Re: Excel diff script (extended version) jared_silva Jared Silva 2007-05-30 11:40:01 PDT
                                             Re: Excel diff script (extended version) kippspan <kippspanbauer at yahoo dot com> kippspan <kippspanbauer at yahoo dot com> 2007-07-19 19:01:52 PDT
                                                 Re: Excel diff script (extended version) steveking Stefan Küng 2007-07-26 10:36:07 PDT
                                                 Re: Excel diff script (extended version) tommypeters Tommy Petersson 2012-07-02 02:35:38 PDT
                                                     Re: Excel diff script (extended version) simonlarge Simon Large 2012-07-02 04:29:52 PDT
                                                         Re: Excel diff script (extended version) tommypeters Tommy Petersson 2012-07-02 04:52:05 PDT
Messages per page: