[svn.haxx.se] · SVN Dev · SVN Users · SVN Org · TSVN Dev · TSVN Users · Subclipse Dev · Subclipse Users · this month's index

Re: Excel diff script (extended version)

From: Tommy Petersson <tommy_at_bogleboo.com>
Date: Mon, 2 Jul 2012 02:35:36 -0700 (PDT)

Hi,

I found this in a Google search and I wonder if this can solve our problem
(and how to implement it)?

We need to be able to merge an Excel document including a VBA macro. At the
moment we need to check out the document exclusively, make the changes, run
the macro which creates 5 source files, build and test the program - during
which time no-one else can change the Excel document. We need merge
functionality.

We use Visual Studio and TFS, but diffMerge could be replaced with
TortoiseMerge, I guess. What else need to be done? caveats?

kippspan wrote:
>
> I've finally gotten around to doing this simple little update. Here is
> the unified difference:
>
> Index: C:/Program Files/TortoiseSVN/Diff-Scripts/diff-xls.vbs
> ===================================================================
> --- C:/Program Files/TortoiseSVN/Diff-Scripts/diff-xls.vbs (revision 19)
> +++ C:/Program Files/TortoiseSVN/Diff-Scripts/diff-xls.vbs (revision 21)
> @@ -38,10 +38,8 @@
> 'Create a compare side by side view
> objExcelApp.Windows.CompareSideBySideWith(objExcelApp.Windows(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
> + objExcelApp.Application.WindowState = xlMaximized
> + objExcelApp.Windows.Arrange(-4128)
> End If
>
> 'Mark differences in sNewDoc red
>
>
>
>
>
> Here is the script again in it's current state and entirety:
> <BEGIN SCRIPT>
> 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 = CreateObject("Scripting.FileSystemObject")
> 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.CreateObject("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.CompareSideBySideWith(objExcelApp.Windows(2).Caption)
> If Err.Number <> 0 Then
> objExcelApp.Application.WindowState = xlMaximized
> objExcelApp.Windows.Arrange(-4128)
> End If
>
> 'Mark differences in sNewDoc red
> objExcelApp.Workbooks(2).Sheets(1).Cells.FormatConditions.Delete
> objExcelApp.Workbooks(1).Sheets(1).Copy
> ,objExcelApp.Workbooks(2).Sheets(objExcelApp.Workbooks(2).Sheets.Count)
> objExcelApp.Workbooks(2).Sheets(objExcelApp.Workbooks(2).Sheets.Count).Name
> = "Dummy_for_Comparison"
> objExcelApp.Workbooks(2).Sheets(1).Activate
> 'To create a local formula the cell A1 is used
> original_content = objExcelApp.Workbooks(2).Sheets(1).Cells(1,1).Formula
> String sFormula
> objExcelApp.Workbooks(2).Sheets(1).Cells(1,1).Formula =
> "=INDIRECT(""Dummy_for_Comparison" & "!""&ADDRESS(ROW(),COLUMN()))"
> sFormula = objExcelApp.Workbooks(2).Sheets(1).Cells(1,1).FormulaLocal
> objExcelApp.Workbooks(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.Workbooks(2).Sheets(1).Cells.FormatConditions.Add xlCellValue,
> xlNotEqual, sFormula
> objExcelApp.Workbooks(2).Sheets(1).Cells.FormatConditions(1).Interior.ColorIndex
> = 3
>
> <END SCRIPT>
>
>
>
>
>
>
>
> Jared Silva wrote:
>>
>> Stefan K√ľng wrote:
>>> kippspan wrote:
>>>
>>> > I've updated the script to work with Excel pre-2003 (see below). This
>>> is
>>> > similar to side-by-side comparison as it will horizontally tile two
>>> > workbooks. It is not the quite same because you have to scroll in
>>> both
>>> > workbooks.
>>>
>>> Great, thanks!
>>> But could you add a check in the script for the Excel-Version and then
>>> do the corresponding actions based on that version info? It's because we
>>> only have one diff script per file extension.
>>
>> Kipp, here is an example of what Stefan is looking for (diff-doc.js)...
>>
>> http://tortoisesvn.tigris.org/source/browse/tortoisesvn/trunk/contrib/other/diff-scripts/diff-doc.js?rev=8957&r1=8418&r2=8543
>>
>> + // Microsoft Office versions for Microsoft Windows OS
>> + var vOffice2000 = 9;
>> + var vOffice2002 = 10;
>> + var vOffice2003 = 11;
>> + var vOffice2007 = 12;
>>
>> // Compare to the base document
>> + if (Number(word.Version) <= vOffice2000)
>> + {
>> + // Compare for Office 2000 and earlier
>> + destination.Compare(sBaseDoc);
>> + }
>> + else
>> + {
>> + // Compare for Office XP (2002) and later
>> destination.Compare(sBaseDoc, "", wdCompareTargetNew, true, true);
>> + }
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: users-unsubscribe_at_tortoisesvn.tigris.org
>> For additional commands, e-mail: users-help_at_tortoisesvn.tigris.org
>>
>>
>>
>
>

-- 
View this message in context: http://old.nabble.com/Excel-diff-script-%28extended-version%29-tp10187740p34100700.html
Sent from the tortoisesvn - users mailing list archive at Nabble.com.
------------------------------------------------------
http://tortoisesvn.tigris.org/ds/viewMessage.do?dsForumId=4061&dsMessageId=2974357
To unsubscribe from this discussion, e-mail: [users-unsubscribe_at_tortoisesvn.tigris.org].
Received on 2012-07-02 12:31:00 CEST

This is an archived mail posted to the TortoiseSVN Users mailing list.

This site is subject to the Apache Privacy Policy and the Apache Public Forum Archive Policy.