Home

- Synkronizer Excel Compare

1. lt PairCount gt 3 lt PairCount gt lt Pair ID 1 gt lt SheetName0 gt Addresses lt SheetName0 gt lt SheetNamel gt Addresses lt SheetNamel gt lt Pair gt lt Pair ID 2 gt lt SheetName0 gt Controlling lt SheetName0 gt lt SheetNamel gt Controlling lt SheetNamel gt lt RangeAddr0 gt SA 4 SAC 75 lt RangeAddr0 gt lt RangeAddr1 gt A 4 HAC 75 lt RangeAddr1 gt lt DBRow gt 5 lt DBRow gt lt DBKeys gt 1 lt DBKeys gt lt DBOptionsMask gt 14 lt DBOptionsMask gt lt Pair gt lt Pair ID 3 gt lt SheetName0 gt Budget lt SheetName0 gt lt SheetNamel gt Budget lt SheetNamel gt lt Pair gt lt Pairs gt lt Project gt lt Synkronizer gt 6 Problems Errors 6 1 Problems Errors If you should have problems with the software error messages unable to activate etc please proceed as follows 1 Open a new e mail without entering any content 2 Start the Synkronizer application 3 Select Project Help About 2000 2014 XL Consulting GmbH Switzerland Synkronizer 10 6 2 S Synkronizer License Professional Edition 10 0 312 1124 Computer BESITZER PC 4 Double click with the mouse on the red Synkronizer symbol A diagnostics file will then be created on the desktop of your pc Attach this file to the e mail 5 Open the following directory C Program Files Synkronizer Synkronizer 10 0 and attach the
2. k Highlight Differences left button With the left button it is determined whether differences are to be highlighted or not The following options exist e No Highlighting No differences are highlighted e Highlight Differences The differences are highlighted in the worksheets e Clear and Highlight The differences are highlighted in the worksheets Before highlighting all the background highlighting are removed Create Difference Report right button With the right button you can specify whether a difference report should be created The following options are available e No Report No difference report is generated e Difference Report An Excel difference report is generated e Hyperlinked Report An Excel difference report with hyperlinks is generated e XML Report An XML difference report is generated 393 If all settings and actions have been taken the comparison of the worksheets can be started with the button shown on the left 4 1 6 1 Highlight Differences If you choose this action the differences in all underlying worksheets are highlighted 2000 2014 XL Consulting GmbH Switzerland so Synkronizer 10 B D 4 C NE Teilnamen ez MI Nr TE AG vr v vr v Gi012001_17_00 G1012001_17_00 G1012001_17_00 G1012001_17_00 Gi012001_17_00 G1012001_17_00 G1012001_17_00 10 Gi012001_17_00 11 G1012001_17_00 12 Gi012001_17_00 13 Gi012001_17_00 K GI01Z001_17_00 E Gt0120
3. Example 5 Compare one master file against a series of updates Sub Example Dim oProj As Synkronizer Project Dim sFolderNew As String Dim sFolderRep As String Dim sFileMaster As String Dim aFiles As String Dim i As Integer Dim sFile As String Dim sFileNew As String Dim sFileRep As String Dim n 1 As Long define master file and folders must end with backslash sFileMaster D Documents Old Master xlsx sFolderNew D Documents New sFolderRep D Documents Reports Debug Assert Len Dir sFolderNew vbDirectory Debug Assert Len Dir sFolderRep vbDirectory read new files i 1 2000 2014 XL Consulting GmbH Switzerland Synkronizer 10 sFile Dir sFolderNew amp xls Do While Len sFile gt 0 ReDim Preserve aFiles 1 To i aFiles i sFil i i 1 sFile Dir Loop loop all files For i 1 To UBound aFiles sFileNew sFolderNew aFiles i sFileRep sFolderRep amp Difference Report amp aFiles i sFileRep Left sFileRep InStrRev sFileRep 1 amp xlsx Set oProj SynkProject With oProj With Settings CompareType syCompareFormulas Formats D Filters 0 HighlightType syHighlightNone ShowHide 0 ReportType syReportStandard End With Files Load sFileMaster sFileNew With Pairs MatchInclude 0 MatchType syMatchFirstByName AddMatched End With Execute If Results Sum
4. 130 Synkronizer 10 Endnotes 2 after index 2000 2014 XL Consulting GmbH Switzerland Back Cover
5. Return Value none Remarks Minimizes windows of all other workbooks See Also e Project Object e Project Object Members 8 2 4 Properties 8 2 4 1 Settings Property Container for all Settings of the project Read Only Settings object Syntax expression Settings expression A variable representing a Project object See Also e Project Object e Project Object Members 2000 2014 XL Consulting GmbH Swizerland 92 Synkronizer 10 8 2 4 2 Files Property Container for the Files workbooks of the project Read Only Files object Syntax expression Files expression A variable representing a Project object See Also e Project Object e Project Object Members 8 2 4 3 Pairs Property Container for the Pairs worksheets of the project Read Only Pairs object Syntax expression Pairs expression A variable representing a Project object See Also e Project Object e Project Object Members 8 2 4 4 Results Property Container for the combined Results of all Pairs Read Only Results object Syntax expression Results expression A variable representing a Project object See Also e Project Object e Project Object Members 8 2 4 5 ActivePair Property Pair that is currently active Pair object Syntax expression ActivePair oPair 2000 2014 XL Consulting GmbH Switzerland Object Model Reference s expression A variable representing a Project object Parameters oPair Pair
6. Dir sFolderOld amp Do While Len sFile gt 0 2000 2014 XL Consulting GmbH Switzerland xls Synkronizer 10 ReDim Preserve aFiles 1 To 1 sFil aFiles i d dd sFile Loop Dir loop all old files For i 1 To UBound aFiles sFile0ld sFileNew check if new is there If Len Dir sFileNew Set oProj With oProj SynkProject With Settings CompareType Formats 0 Filters 0 HighlightType ShowHide 0 ReportType End With Files Load FILE FILE sFolderOld aFiles 1 sFolderNew amp aFiles 1 gt 0 Then syCompareFormulas syHighlightNone syReportNone With Pairs MatchInclude MatchType AddMatched nd With Execute 0 E Print 1 aFiles i amp vbTab amp syMatchFirstByName Results Sum Unload CloseFiles True End With Set oProj Nothing Else Print 1 aFiles i amp vbTab error End If Next i Print 1 Print 1 Comparison time Format Timer t0 00 00 s Close MsgBox finished amp vbLf amp UBound aFiles amp vbOKOnly vbInformation files compared see log file Synkronizer 2000 2014 XL Consulting GmbH Switzerland End Sub Example 8 compare all files with the same names of two folders does the following 1 highlight differences 2 creates a difference report of each filepair 3 creates a log file with the tota
7. Files Synkronizer 10 New Project ex Project Help Workbooks rr gt EI In the main form the files to be compared are selected This step is performed for each file by clicking on the open file icon Alternatively the files can also be selected with the drop down buttons If you click that button a list of all recently compared and recently opened Excel files appears Projects Files can also be opened by the use of a project using the menu Project Open For more detailed information see chapter Projects Supported file types All files which are supported by Microsoft Excel can be opened with Synkronizer 10 Worksheets Worksheets EEE E ID ist sheet 2nd sheet Ce Th Addresses 2 Case Sensitive Case Sensitive EA la 3 Number Formats Number Formats a 4 Numbers Strings Numbers Strings EA 5 Errors Errors vy 6 Formulas Formulas EA 8 0123 0123 EA 9 One Cell One Cell EA IO DB DB 7 11 Controlling Controlling Y 12 R430 R430 EA 14 ColSort Col Sort EA 15 DBL DB1 EA Lis DEO nR ES 2000 2014 XL Consulting GmbH Swizerland This form lists all worksheet pairs that are to be compared The worksheets can be paired automatically or manually Match remove worksheets automatically The following buttons are available to add remove the worksheet couples ae All worksheets of the workbooks are matched by name EO All worksheets of the files are matched by index T
8. Synkronizer 10 8 2 3 6 8 2 3 7 e Project Object e Project Object Members DoReport Method Creates a Report specified in ReportType settings Syntax expression DoReport ActivePairOnly expression A variable representing a Project object Parameters ActivePairOnly Boolean Optional When True only the ActivePair is processed Return Value none See Also e Project Object e Project Object Members DoHighlight Method Performs Highlighting specified in HighlightType settings Syntax expression DoHighlight ActivePairOnly expression A variable representing a Project object Parameters ActivePairOnly Boolean Optional When True only the ActivePair is processed Return Value none See Also e Project Object e Project Object Members 2000 2014 XL Consulting GmbH Swizerland Object Model Reference s 8 2 3 8 DoShowHide Method Performs ShowHide specified in ShowHide settings Syntax expression DoShowHide ActivePairOnly expression A variable representing a Project object Parameters ActivePairOnly Boolean Optional When True only the ActivePair is processed Return Value none See Also e Project Object e Project Object Members 8 2 3 9 UndoHighlight Method Undoes changes made by DoHighlight Syntax expression UndoHighlight ActivePairOnly expression A variable representing a Project object Parameters ActivePairOnly Boolean Optional When True only the ActivePair is pr
9. You could create a LicenseDistribution file for each computer and save them under separate names Then EITHER copy the actual xml file to the local installation folder as LicenseDistribution xml OR create edit a windows shortcut file named LicenseDistribution pointing to the generated file Install licenses on computers To enable the installation on the workstations computers laptops make sure that the license file is ona shared directory alternatively the distribution files are to be included in the installation packet If this is the case then install the software using the method Administrative Install In the step Select directory for distribution license you must select the directory in which contains the license files The software is then activated and registered as soon as Synkronizer is started Once the Synkronizer program has been started and successfully activated the license info is stored in the local computer s license xml and the shortcut or distribution file can be deleted Scripted installations Following are the custom command line switches accepted by the setup executable Choose the installation type TYPE must be one of the following upper or lowercase standard network distrib Set the network or distribution folder FOLDER a valid file path or URL for the network folder e g X folder subfolder 2000 2014 XL Consulting GmbH Switzerland Install Uninstall 19 server share fo
10. ma Synkronizer 10 8 6 3 5 8 6 3 6 Add db1 dbl1 Item 1 RangeAddr muMaster A1 H50 Item 1 RangeAddr muUpdate A1 H50 End With See Also e Pair Object e Pair Object Members SheetName Property Returns the name of the worksheet for the specified member of the pair Read Only String value Syntax expression SheetName MUid expression A variable representing a Pair object Parameters MUid muMasterUp Selects either member of the Pair datelD See Also e Pair Object e Pair Object Members Sheet Property Returns the Worksheet object for the specified member of the pair Read Only Worksheet object Syntax expression Sheet MUid expression A variable representing a Pair object Parameters MUid muMasterUp Selects either member of the Pair datelD See Also 2000 2014 XL Consulting GmbH Switzerland Object Model Reference 115 e Pair Object e Pair Object Members 8 6 3 7 Range Property Returns the Range object for the specified member of the pair Read Only Range object Syntax expression Range MUid expression A variable representing a Pair object Parameters MUid muMasterUp Selects either member of the Pair datelD Remarks To change the Range use the RangeAddr property See Also e Pair Object e Pair Object Members 8 6 3 8 Results Property Returns the number of differences found Read Only Results object Syntax expression Results expression A va
11. RangeAddr Returns or sets an optional Range address for the specified member of the pair SheetName Returns the name of the worksheet for the specified member of the pair Sheet Returns the Worksheet object for the specified member of the pair Range Returns the Range object for the specified member of the pair Results Returns the number of differences found ID Returns the key of the Pair object in the Pairs collection Index Returns the index of the Pair object in the Pairs collection IsDB Returns True when DBKeys property is correctly specified Pair will be compared as database Highlighted Returns True when differences in the Pair are currently Highlighted and can be Undone Events Progress Raised during compare execution to indicate running subprocess 8 6 3 Properties 8 6 3 1 DBRow Property Returns or sets the index for the row which contains the headers Long value Syntax 2000 2014 XL Consulting GmbH Switzerland 112 Synkronizer 10 8 6 3 2 expression DBRow expression A variable representing a Pair object Example This example defines the 3rd row as database heading With oProj Pairs 1 row number of database heading DBRow 3 create primary key column no of master DBKeys 2 3 group records DBOptions syDatabaseGroup True End With See Also e Pair Object e Pair Object Members DBKeys Property Returns or sets a semicolon delimited list of Column indices used as primary key
12. Synkronizer 10 4 2 3 from US 30 00 to US 30 50 Comparison by Formulas Comparison by Formulas Old Turnover January IOI February 11 00 March 9 00 ist Quarter 30 00 Comparison by Values Old Turnover January 10 00 February 11 00 March 9 00 1st Quarter 30 00 New Turnover January 10 50 February 11 00 March 9 00 1st Quarter 30 50 New Turnover January 10 50 February 11 00 March 9 00 1st Quarter 30 50 If you compare the worksheets by formula only the month of January is recognized as a difference because the cell 1st Quarter contains an identical formula Comparison by Values If however a comparison by values is performed Synkronizer recognizes two differences as the month of January and the 1st Quarter have different values Filter In this area you can determine whether you want to apply a filter to the data or not 2000 2014 XL Consulting GmbH Switzerland Forms 55 Synkronizer 10 Settings bedel Main Form Filter results Enabled Compare Jr pg Navigator Colors User Interface sgt System Reset preferences l Save preferences OK Cancel You can define multiple filters by clicking on the desired check boxes With the checkbox Enabled the filters will be activated Important If you have selected filters but Enabled is not checked the cell values formulas are not filtered Ignore case If this field contains a
13. finished amp vbLf _ n 0 workbooks without differences amp vbLf _ 2000 2014 XL Consulting GmbH Switzerland n 1 End Sub Visual Basic for Applications VBA 77 workbooks with differences s reports vbOKOnly vbInformation Synkronizer Example 7 compare all files with the same names of two folders a log file with the total differences of each file pair will be created Sub Exampl n 1 ZG GG Ss SB Ss Ss Ss ss 8 5 5 e7 oProj As Synkronizer Project sFile As String aFiles As String i As Integer sFolderOld As String sFolderNew As String sFolderLog As String sFileOld As String sFileNew As String sFileLog As String As Long tO As Date define folders must end with backslash sFolderOld sFolderNew sFolderLog D Documents Old D Documents New D Documents Log Debug Assert Len Dir sFolderOld Debug Assert Len Dir sFolderNew Debug Assert Len Dir sFolderLog tO Timer create log file sFolderLog amp synkronizer log txt Open sFileLog For Output As 1 sFileLog Print 1 Pri Pri Pri Pri Pri PP YP zd YD ed Pri He de Se He He SH om i Ey do Er Synkronizer Logfile Date amp Format Date Time amp Format Time vbDirectory vbDirectory vbDirectory YYYY MM DD hh nn ss Filepair amp vbTab Differences read old files i 1 sFile
14. 0800 6 Worksheet a Gre 30 GI917001_17_00 n003 1 1800 25 USFroen SO 4 07 ix OHO 26 31 G1012001_17_00 0004 00 0400 bete gt BOX 0200 32_G1912001_17_00 0004 00 0800 GE e pea e ne BOK 0800 33 G1012001_17_00 0002 00 1009 4 ea A 12 BOX 7000 wx 0800 34 0101200117 00 000401 0800 35 G1012001_17_00 0002 07 0800 guzur 0004 02 0500 54 SS za BEGITU ur pe giria ea 02 28 TEN 29 Crocoisie 38 6101200117 00 0008 00 Do PARA ern e 39 Gi012001_17_00 1004 00 w00 E 40 610120011700 0008 00 0400 E IAN e b 41 G1012001_14_00 mun 1000 dike 42 G1012001_17_00 0008 00 Index 0400 F24 193 1285 43 G1012001_17_00 0003 00 100 dE 1355 44 G1012001_17_00 0009 00 0809 45 G1912001_17_00 0003 00 4000 45 G1012001_17_00 0009 03 0900 47 G1012001 17 00 0009 03 0809 48 G1012001_17_00 0009 IK 0809 49 G1012001_17_00 0009 04 0500 GO G1012001_17_00 0009 05 0800 51 G1012001_17_00 0009 05 0509 57 G1012001_17_00 1009 06 080 53 G1012001_17_00 0009 06 0500 54 G1012001_17_00 0003 ar 080 55 G1012001_17_00 0009 07 0809 55 G1012001_17_00 0009 08 080 E G1012001_17_00 0009 00 0900 0009 08 58 61012001 47 00 0012 00 0400 708 10012 00 59 G1012001_17_00 0012 00 0809 1096 mo XI e440 Slamars ISOren Eudgat Faull y PDI Cnocosta pa Le E151 0430 1960 ron ESO 40 GORE 4181 A wia wien ma IKO noaoo vx 0800 ix D 0009 06 Mx 0800 630120011700 0009 07 IO 0800 G1012001_17_00 0009 07 vx 0000 31012001_17_00 0009 08 ENE
15. 09 2009 2009 09 01 D2 D2 800 800 E2 E2 6283 21 6283 21491604209 l G C3 05 01 2010 2010 01 05 D3 D3 600 600 E3 E3 743955 94 743955 938735648 C4 C4 15 11 2009 2009 11 15 D4 D4 2500 2500 PA E4 972183 12 972183 118134266 c5 CS 25 09 2009 2009 09 25 Different content 297 13 Now look at the differences You ll notice that some cells are displayed with the sign This is an indicator for cell content that is stored as text but can be converted to a Number a Date or a Boolean These differences can be filtered by ignoring the data type Repeat the steps 7 10 and add a check next to the filter option Ignore data type You should now have 99 differences left Remark Ignore Datatype will only work if the text can be converted using the current system locale regional settings Synkronizer 10 Navigator Project Window Help IZ SIE E ZIE BO OO J Worksheet du 3 pe WE aaas ae Addri Addr2 Formulai Formula 2 E2 E 6 283 21 6283 21491604209 B E3 743955 94 743955 938735648 E4 E4 972183 12 972183 118134266 E5 E5 781651 63 781651 634550048 E6 E6 4953 5 4953 49760130707 E7 E7 381378 42 381378 417010028 B E8 89423 96 89423 9582114751 E9 E9 78947 09 78947 094744537 E10 E10 1230 68 1230 6819348978 E11 Pu 877605 68 877605 684836557 Different content 99 2000 2014 XL Consulting GmbH Switzerland s Synkronizer
16. 135 42 42 1 rows 36 200 100 1 rows 37 10202 1 rows EN e AA 40 cells cells formula formula lt j E Ba 1486 1486 421 Ef9 gs 83 85 43 E28 EA 138 138 5 14 El El 138 138 5 45 EE t 000800 Index 0008 00 INDEX 46 BUZ EZ 0400 1000 47 PS FISE 68s 40 as aso Gi F R B s H151 HISI A Pw E usi us n 5 sz TE D 45 487 52 C52 C152 003400 0034 00 FERTIG 53 as 1 0034 00 Index 0034 00 INDEX gt 154 a5 C134 0034 00 Index 0034 00 INDEX 851 178 f SE de lt 156 as 195 005601 0056 01 0056 02 S2 cise at 00 04 0056 04 0056 05 58 00 ZE 006200 0062 00 FERTIG 59 C203 C203 0062 01 0062 01 FERTIG 160 3 224 006400 0064 00 FERTIG GA EE CIN 0064 01 0064 01 FERTIG e TS Ey gt n gt me 4 2 Settings In the settings form the remaining settings be defined See below sections for descriptions of the various settings Save Reset Preferences You can configure Synkronizer to your needs With the buttons Save Preferences and Reset Preferences you can save or reset the settings to the default set up Differences between Application and Project Settings 2000 2014 XL Consulting GmbH Switzerland 52 Synkronizer 10 4 2 1 In this form you can define application and project settings Below are the differences of those explained e If no project is active the application settings are displayed in all forms e If a project is active the first three menus Main Form Compare and
17. 8 1 3 5 VersionName Property 8 2 8 2 1 8 2 2 Returns the version and build number of the application Read Only Syntax expression VersionName expression A variable representing a Application object See Also e Application Object e Application Object Members Project Object Project Object The project is the central object for working with Synkronizer See Also e Project Object e Project Object Members Project Object Members Methods Load Save Unload Execute DoReport DoHighlight DoShowHide UndoHighlight UndoShowHide Reset ProtectionRemove ProtectionValidate ArrangeWindows SelectPairSheets Loads the specified projectfile into memory Saves the project s configuration to the specified path Unloads the current project Optionally closes the files Executes the compare process Creates a Report specified in ReportType settings Performs Highlighting specified in HighlightType settings Performs ShowHide specified in ShowHide settings Undoes changes made by DoHighlight Undoes changes made by DoShowHide Resets specified objects or results Removes protection for pairs that have protected worksheets using the PasswordList Verifies that protected worksheets in pairs can be unprotected with current PasswordList Arranges tiles windows for the active Files Activates the worksheets of the ActivePair 2000 2014 XL Consulting GmbH Switzerland Object Model Reference es Pr
18. ArrangeWindows 91 Automatic Installation 7 automation 69 C cell content 53 cell formatting 53 Clear most recently used lists 59 Close 86 colors 49 colours 49 COM add in 6 commandline 18 command line switches 7 commandline 18 compare 53 CompareDescription 97 CompareType 97 connect 69 constants 54 Count 109 create difference report 49 Customer 83 customize colors 58 D database 35 44 database mode 44 database structure 44 datatype 54 db mode 44 DBKeys 112 DBOptions 113 DBRow 111 default values 52 DefaultsLoad 96 DefaultsReset 96 DefaultsSave 96 Delete missing rows 62 details 62 determine colors 58 Difference Report 51 Differences 53 Different formulas 61 different rows 35 Different values 61 DifferentContent 119 DifferentFormats 119 directory 42 DisplayStatus 83 Dock Navigator form 61 DoHighlight 88 DoReport 88 DoShowHide 89 Duplicate records 61 DuplicateKeys 118 DuplicateRecords 119 Duplicates 61 A EditionName 83 email 21 24 e mail 21 24 empty worksheets 42 example code 70 Excel windows 58 Execute 87 existing project 41 Exists 109 2000 2014 XL Consulting GmbH Switzerland exit navigator 57 Ex field name 44 field names 44 file 42 FileName 104 Files 42 92 102 filter 54 FilterPattern 100 Filters SO FiltersDescription 99 FiltersOption 99 FilterTolerance 100 first worksheets 42 folder 42 Formats 61 97 FormatsDescription 98 FormatsOption 98 formulas 5
19. EAN Pairs Object Mem DE GANA LD Aaa EO Add Method a AddMatched MKO aian iin aT e aa Remove Method Hache ces eee cee el o KEELA E ATE EE ResetPairs Method AAA Sl d AENA A iE arra Count Propere o cl it karka dtu EEA EE EA Sl E o A O AN 109 2000 2014 XL Consulting GmbH Switzerland haein aO Akaa na E ETE EEE AEE E AEAEE 110 MatchType Property IT GEA Pair OD Je CE aia e E aa ata E a aaa ak eitte a kr ariete Pair Object Mem dA O DBROW PrOpPeri Viviana aia A AA AAA AAA AAA A da DBKSYS POPE eiii A as DBOptions Froperty AAA RangeAddr Property SheetName Property GO Sheet POPE Vii ai aiar atai bakardadeari aei Range PODER a E AT A TO Gk Aa Ealo aie GE E ak a etar ReSUItS daiela Prop AARE AA EO Uv cede E EEE GT ETA AE E A UE EE Index Property GO T Results Object AAO RESUItSODjO CE AAA 116 Results Object Members wesseccisecscsesseveccccsstsssseversscesscedenseevsivececacseueceusetcsencavenscevsiccuscrerstesesseteveececetaceschvedtensseness 116 EROS EUS E errada a e E a dete E 117 MissingTabs Propecia a dida es 117 MissinQGols Property aranaren ean aa ig ea Eee 117 APOITA 118 DuplicateKeys Property AAA 118 DuplicateRecords POST garra dar ia adar 119 DifterentGontent Property cocos iga a ka anta ga aT Or eaten 119 DifferentFormats Property cion A Ed aA aa 119 Sum Property ZA eau Ee ala A alea AAA SummaryMessage elea 120 TextDis play Property anu a ia aate 121 3 S
20. ES e EE A A D Oe nr Ready mum Synkronizer displays both worksheets next to each other and as such lets you compare the differences directly At this point you may decide with which version you wish to continue With mouse clicks you can transfer values from spreadsheet 1 to spreadsheet 2 or vice versa This is the most efficient way to eliminate all differences Your Excel worksheet is perfectly updated very quickly The Navigator form is divided into two parts The upper part shows all differences per pair of worksheets the lower part displays the details 2000 2014 XL Consulting GmbH Switzerland Actions The navigator can perform the following actions e Overview of differences e Synchronize worksheets transfer differences e Delete unwanted differences e Show hide differences 4 3 1 Overview In the upper part of the Navigator the worksheets and their differences are displayed At one glance you can see how many differences exist aa aja 1 8 Ao og Worksheet I 3 ES 27 Feuil 14 21 66 28 FDM in 23131 ME 29 Chocolate 41 12 30 Page Fields 3 24 20 13110 31 Leading Zeros 28 For example in that screenshot you can see that the following differences were detected in worksheet FDM Missing columns 1 1 one missing column in Master and one missing column in Update Missing rows 23 31 23 missing rows in Master and 31 missing rows in Update Different values 79 different for
21. ID ist sheet 2nd sheet ES A Numbers Strings 5 Errors 6 Formulas La nm a Numbers Strings Errors Formulas nina EA EA EA Options Edit ranges SAS1 5C 394 N If you do not want to compare the entire table you can define the desired range Proceed as follows e 1 Select the worksheet which contains the range you want to compare 2 Select the button Edit ranges and database options icon to the right or double click on the pair of worksheets you want to edit The option field appears 3 Select Edit ranges to set the desired range 4 The range icon is now displayed to the right of the worksheet Note You can also enter named ranges Multiarea ranges are not allowed 4 1 3 2 Databases Worksheets lz bl 22 ID ist sheet 2nd sheet E EA E Addresses 3 Number Formats 5 Errors Options Edit ranges Enable DB mode Row for headers Sort records Group records E Add SynkID Addresses Number Formats Errors GEA ID Fieldname 2 Family Name l3 First Name E GA If the worksheets you want to compare are in a database structure we recommend to employ a database comparison If you do not know exactly what is a database and how it works please read first the chapter General information on databases 2000 2014 XL Consulting GmbH Switzerland Proceed as follows to enter the database options 1 Selec
22. Pair to be activated See Also e Project Object e Project Object Members 8 2 4 6 FullName Property The full path and name of the project file Read Only String value Syntax expression FullName expression A variable representing a Project object See Also e Project Object e Project Object Members 8 2 4 7 Name Property The name of the project file Read Only String value Syntax expression Name expression A variable representing a Project object See Also e Project Object e Project Object Members 8 2 4 8 ReportWorkbook Property Report workbook when created Read Only Workbook object Syntax expression ReportWorkbook expression A variable representing a Project object 2000 2014 XL Consulting GmbH Switzerland o Synkronizer 10 See Also e Project Object e Project Object Members 8 2 5 Events 8 2 5 1 Progress Event Raised during compare execution to indicate running subprocess Remarks You can use this event to show progress indicator to the user See Also e Project Object e Project Object Members 8 2 5 2 PairActivate Event Raised when is Pair is activated Remarks You can use this event to select the pair s worksheets See Also e Project Object e Project Object Members 8 2 5 3 PairDeactivate Event Raised before a Pair is deactivated See Also e Project Object e Project Object Members 2000 2014 XL Consulting GmbH Swizerland Object Model Reference os 8 3
23. Project File 64 HEA GUO ALA GA 64 Part VI Problems Errors 65 Hadi Problems Ga 65 2 Known Problems GALAN 66 Part VII Visual Basic for Applications VBA 69 Ws A ooo E a ENE deca sects ht de a EE ches cond EN coves a UN E a E E ete 69 2 Exam AUEN 70 Part VIII Object Model Reference 81 1 Application Obje Ct AAA 81 Application Objet ANANA 81 Application Object MemberS ss ara 82 A AENA 82 ACtIVEPFOJSCUIFTOPEMLY etat ti atta anaiarik ariari tad dita deda EEE 82 Dis play Status POST aN aaa atar cias 83 CUSTOM HEROE agu cicc s cccesesssassesdssccacuneseenavatedcncuthnceditessnsesatevdssesavenstsunssaunsadeseiasutesashiedeaneecuth dhetueskbusschchtacnie 83 Edition Name Property agai daaa a de 83 V SFSIOMNaIME P Ope TIV tato atadi aik abrea AEK OEE 84 2 Project ODje Chi EAEE 84 Project Object 84 Project Object ME IO HE EE a aa aa E aa ga a aa Eate 84 Methods euan EA EE EEEa EE L Oad A Gula EAEE Sav Method AAA Unload Method AAE Reset Methodi AEA Execute Method DoReport Method Doblighlight AGAR EAEE 88 DoShow Hide Method EAEE 89 WadoHighlight Method AAA 89 WndoShow Hide Method AAA 89 ProtectionRemove Method scout arated 90 ProtectionV alidate Method rabera iaren aiid aie ee needa tata tta 90 ArrangeWindow s Method girada aaa araka arkai atakatua EN Properties 0 Settings Property Files Property a E eh a aietako niet ea dia in ain ahah ned PAS POPE iii eda A AA A rruetan Re
24. Settings Object 8 3 1 Settings Object Container for a group of properties that specify how files will be compared and what actions will be performed See Also e Settings Object e Settings Object Members 8 3 2 Settings Object Members Methods DefaultsLoad Loads user preferences DefaultsSave Saves settings as user preference DefaultsReset Removes user preference reloads default values Properties CompareType Returns or sets a constant specifying which content property to compare CompareDescription Returns a descriptive text for the CompareType Formats Returns or sets a series of options specifying which formatting properties to compare FormatsOption Returns or sets the specified formats compare option FormatsDescription Returns a descriptive text for the enabled Formats options Filters Returns or sets a series of options specifying which differences to filter ignore FiltersOption Returns or sets the specified filter option FiltersDescription Returns a descriptive text for the enabled Filters options FilterPattern Returns or sets a pattern for either Regex of Like filtering of string differences FilterTolerance Returns or sets a number below which numeric differences will be ignored ReportType Returns or sets a constant specifying which Report to create HighlightType Returns or sets a constant specifying which Highlighting to perform HighlightDescription Returns a descriptive text for the HighlightType ShowHi
25. Then n 1 n 1 If Len Dir sFileRep gt 0 Then Kill sFileRep ReportWorkbook Close True sFileRep Else n 0 n 0 ReportWorkbook Close False End If Unload CloseFiles True End With Set oProj Nothing Next i MsgBox finished amp vbLf _ n 0 workbooks without differences amp vbLf _ n 1 amp workbooks with differences s reports vbOKOnly vbInformation Synkronizer 2000 2014 XL Consulting GmbH Switzerland Visual Basic for Applications VBA 75 End Sub Example 6 compare all files with the same names of two folders a difference report will be created for each set of files The differences are highl Sub Example6 Dim oProj As Synkronizer Project Dim sFile As String Dim aFiles As String Dim i As Integer Dim sFolderOld As String Dim sFolderNew As String Dim sFolderRep As String Dim sFileOld As String Dim sFileNew As String Dim sFileRep As String Dim sWorkbooks 0 To 1 As String Dim n 0 To 1 As Long define folders must end with backslash sFolderOld D Documents Old sFolderNew D Documents New sFolderRep D Documents Reports Debug Assert Len Dir sFolderOld vbDirectory Debug Assert Len Dir sFolderNew vbDirectory Debug Assert Len Dir sFolderRep vbDirectory read old files i l sFile Dir sFolderOld amp xls Do While Len sFile gt 0 ReDim Preserve aFiles 1 To i aFiles i s
26. comparison process see cursor 2000 2014 XL Consulting GmbH Switzerland a Synkronizer 10 Td Synkronizer 10 New Project bete Project Help Workbooks Master xis E E Update xis E Worksheets de 29 29 22 ID ist sheet 2nd sheet HO Customers 2009 1031 Tumnover lt Options Edit ranges V Enable DB mode ID Fieldname Row for headers 1 be l 1 Customer No F F Sort records E Group records Add SynkiD Actions Highight Ditterences ba bo Report EE ET 11 The Navigator form is now displayed As you can see there are 4 missing columns in Master file and 12 missing columns in the Update spreadsheet found see highlighted cell below E Synkronizer 10 Navigator Project Window Help EREHE ee Worksheet a 9 14 1 Customers 2009 KE E BIK e maj de Uk Addri Addr2 Formula 1 Formula 2 ne BB Customer ac Phone D D Address z EE City B B January ac February D D March EE April EE May G G June 3 Missing columns 4 12 12 Now we want to merge the spreadsheets by transferring the missing columns from Update to the Master spreadsheet If you want to transfer just one column click on the desired column and the transfer button see illustration below 2000 2014 XL Consulting GmbH Switzerland Tutorials 35 is Synkronizer 10 Navigator Project Window Help als ela b SIE ao m a Worksheet a a 3 14 1 Custom
27. file License xml to the e mail 6 Send the files to support synkronizer com together with any further comments or observations you may have Please include information about your set up e g network Citrix server terminal server subnets etc to help in the evaluation and resolution of the problem During business days you will get an answer from our support team within 24 hours Known Problems Errors Synkronizer 10 0 is installed but does not appear on the toolbar under tools or on the ribbon under review The most likely cause is that Synkronizer ended up under Disabled addins This may happen is you stop or crash excel while Synkronizer is busy Proceed as follows to re enable Synkronizer Excel 2007 2010 1 Click the Microsoft Office Button image click Excel Options and then click Add Ins 2 Check if Synkronizer 10 0 is listed under Disabled Application Add ins it should be listed under Active Application Add ins 3 Select amp remove Synkronizer from the disabled category It will then appear in either Active Application Add ins or Inactive Application Add ins If it is listed under inactive then go to the ComAddin dropdown and activate it Excel 2003 2000 2014 XL Consulting GmbH Swizerland Problems Errors 67 1 Select Help About Microsoft Office Excel Disabled Items 2 Select the Synkronizer add in and click Enable 3 Re start Excel 2003 Excel XP and also Excel 2003 Clo
28. for Database String value Syntax expression DBKeys expression A variable representing a Pair object Remarks The column no of the primary keys of the Master file have to be entered separated by semicolons Example To use the 2nd and 3rd column as primary key With oProj Pairs 1 row number of database heading DBRow 3 create primary key column no of master DBKeys 2 3 group records DBOptions syDatabaseGroup True End With See Also 2000 2014 XL Consulting GmbH Switzerland Object Model Reference 113 e Pair Object e Pair Object Members 8 6 3 3 DBOptions Property Returns or sets options for sorting or grouping database records Boolean value Syntax expression DBOptions expression A variable representing a Pair object Example This example groups the records With oProj Pairs 1 row number of database heading DBRow 3 create primary key column no of master DBKeys 2 3 group records DBOptions syDatabaseGroup True End With See Also e Pair Object e Pair Object Members 8 6 3 4 RangeAddr Property Returns or sets an optional Range address for the specified member of the pair String value Syntax expression RangeAddr MUid expression A variable representing a Pair object Parameters MUid muMasterUp Selects either member of the Pair datelD Example This example sets the range address With Pairs 2000 2014 XL Consulting GmbH Switzerland
29. lag Constants used to by the the Filters property in Settings syFiltersENAB Enable or Disable all other Filters options 2000 2014 XL Consulting GmbH Swizerland Object Model Reference 123 LED syFiltersCase Text compare Case Insensitive ABC vs abc will not result in a differences syFiltersTrim Text compare Trim text ABC vs ABC will not result in a differences syFiltersType Differences in Datatype are ignored Use to ignore differences between Date values and String values representing Dates syFiltersConst All constants are ignored Only formulas are compared ant syFiltersForm All formulas are ignored Only constants are compared ula syFiltersHidC Data in Hidden Columns will be ignored ols syFiltersHidR Data in Hidden Rows will be ignored ows 8 8 7 syShowHideFlag Constants used to by the the ShowHide property in Settings syShowHideEN Enable or Disable all other ShowHide options ABLED syHideRowslde Hide identical rows linked without cell differences n syHideRowsDif Hide different column linked some cells are different f syHideRowsMis Hide missing rows not occurring in the other worksheet s syHideRowsDu Hide rows with either duplicate keys or records ps syHideColsMiss Hide missing columns not occurring in the other worksheet 8 8 8 syMatchType List of valid types of Matching syMatchManu Pairs were modified or added with Add al syMatchFirstB The first Matched
30. of differences the data in the first files is Proper in the other it is UPPER 8 Let s set a filter to Ignore the case Open the menu Project Settings 2000 2014 XL Consulting GmbH Switzerland Synkronizer 10 Navigator Project Window Help Save Save as Reload workbooks PassWords Close Sea El A2 A2 FFICBillingPayorA FF B2 c2 C2 01 09 2009 D2 D2 800 Different content 500 Addri Addr2 Formulai Formula 2 Al Al Name NAME d Bi B1 Page PAGE a Ci Eff Date EFF DATE Di D1 Sequence SEQUENCE El Value VALUE KEA ICBILLINGPAYOR B2 Billing Information BILLING INFORMA 2009 09 01 800 E2 E2 6283 21 6283 21491604209 Tutorials 9 On the form Select Filters First check Enabled then check the Option Ignore Case Close the form by clicking the Ok button Synkronizer 10 Settings bidezi Form Filter results V Enabled Compare 29 Ignore datatype Navigator Ignore cells with constants Ignore cells with formulas Colors Ignore hidden columns User Interface _JIgnore hidden rows 0 Numeric tolerance System Text filter s with RegEx syntax y Cop ze 10 You go back to the Navigator form Click the Button Run the comparison again 2nd button in the top row see cursor 2000 2014 XL Consulting GmbH Switzerland 28 Synkronizer 10 Synkronize
31. of the Files datelD See Also e Files Object e Files Object Members 8 5 Pairs Object 8 5 1 Pairs Object Collection of Pair objects Remarks You can add pairs individually with the Add Method or use the AddMatched method to automatically add pairs for all worksheets that have matching names or indexes You can retrieve a specific pair with Pairs Index or Pairs Item index You can remove a specific pair with Pairs Remove Index You can remove all pairs by using the ResetPairs Method See Also e Pairs Object e Pairs Object Members 2000 2014 XL Consulting GmbH Switzerland 106 Synkronizer 10 8 5 2 8 5 3 8 5 3 1 Pairs Object Members Methods Add Adds a pair to the Pairs collection AddMatched Creates Pairs for all matched worksheets Remove Removes specified Pair from the Collection ResetPairs Resets specified objects and properties from Pairs Properties Item Returns a single item Pair from the collection Exists Returns True if specified ID or Index exists in the collection Count Returns the number of members in the collection MatchInclude Returns or sets options specifying types of worksheets to include by AddMatched method MatchIncludeOption Returns or sets options specifying types of worksheets to include by AddMatched method MatchType Returns or sets a constant specifying how worksheets will be matched by AddMatched method PasswordList Returns or sets a semicolon delimited list of
32. this a textdocument and paste the datastring using the keys CTRL V 6 Open the internet and enter the following link http www synkronizer com scripts activate php lang en The software activation page is displayed Software Activation 1 Enter the data string containing the activation deactivation data in the field below ACT le 0001 00000000 0000 0000 0000 001FD0551DCB 7E1EBEFF Gigabyte Technology Co Ltd EP35 DS3 28444E0D 1 MILLER The data string contains your registration key license number hardware identification and the answer to the secret question For local installations the data string is specific to each computer For network installation it is specific to the shared folder The data string is generated with the license form of the Synkronizer software 2 Generate the activation deactivation key ia 7 Paste the datastring in the input field 1 and click on the activate symbol 2000 2014 XL Consulting GmbH Swizerland Install Uninstall 23 Software Activation 1 Enter the data string containing the activation deactivation data in the field below ACTI AAA 7000 1 00000000 0000 0000 The data string contains your registration key license number hardware identification and the answer to the secret question For local installations the data string is specific to each computer For network installation it is specific to the shared folder The data string is generated with the license form of the
33. xml red is located on a shared folder The installation folder of the Synkronizer users contain a shortcut license Ink blue to the license file Permissions At least one user must have write modify permission on the license file while all others only need read permissions The user s with write permission can register activate the license and manage the list of allowed computers 2 3 1 Adding allowed workstations Once the license is registered and activated it is necessary to add the workstation s to the list of allowed computers This can be done from any computer where Synkronizer is installed Note that the install folder must contain a shortcut to the network license file and the user making the changes must have write permissions 1 Start Synkronizer also shown when you select the menu Project Settings System Registration Activation 2 Click the Manage tab 3 Select workstations in the Category dropdown and the edition in the License dropdown 4 Now you can enter the computers users which are allowed to work with Synkronizer Just enter the correct computer names and optional description in the input fields and click on the Plus button 2000 2014 XL Consulting GmbH Switzerland ta Synkronizer 10 Pe Synkronizer License LE beei peer e oo Computer Description CH MKT DESKO5 Roger Milla 5 No nodes for this license The allowed computer will then be added to the
34. 01_17_00 16 Gt012001_17_00 17 Gi012001_17_00 18 G1012001_17_00 19 Gi012001_17_00 20 Gi012001_17_00 21 G1012001_17_00 22 Gi012001_17_00 23 G1012001_17_00 24 G1012001_17_00 25 G1012001_17_00 26 G1012001_17_00 27 G1012001_17_00 28 G1012001_17_00 29 G1012001_17_00 30 Gi012001_17_00 31_ G1012001_17_00 32 Gi012001_17_00 TO MIMA 17 AN OO u GG de wje 0001 00 0001 00 0001 00 0001 02 0001 02 0001 11 0001 11 0001 12 0001 12 0001 13 0001 13 0001 14 0001 14 0001 15 0001 15 0001 15 0002 00 0002 00 0002 00 0002 00 0002 00 Gummi 0003 00 0003 00 0003 00 0003 01 0003 01 0003 02 0003 02 0004 00 0004 00 nana AN BDX BDX EFX JMi IK I I I 114 JI Ji BSF BSF BDX BDX BDX BDX 101 I I I BDX BDX PIK 0400 0300 1000 0400 0800 0800 0800 0800 0800 0800 0800 0800 0300 0800 0800 1000 0400 0400 0400 0800 0400 0400 0800 1000 0800 0300 0800 0800 0400 0300 inn 143 6 150 The differences are highlighted in the following colors e light blue for deleted columns rows light green for added columns rows yellow for different cell values formulas orange for different cell formattings e lilac for duplicate keys only database mode gray for duplicate records redundant records only database mode GORO onom omom nng go 7a A K_ L ITE Aen ese o d ba Yv vr wv A 46 n 501 502 560 87 503 504 506 507 509 510 512 513 127 SEa H
35. 10 3 2 14 We now have only differences in column E remaining You ll quickly see that the numbers in Master are rounded but the numbers in Update are not although in the worksheet the extra precision is not displayed due to a number format These differences can be ignored by setting a numeric tolerance Go to the Filters page on the Settings form and set a value of 0 01 in the box for Numeric Tolerance b Synkronizer 10 Navigator axe Project Window Help ap ea b IE Bo 00 e Worksheet I 3 a L mo gt gt ul Be d EAK KE Addri Addr2 Formula 1 Formula 2 No differences found for this selection Missing columns 15 No more differences found Merge spreadsheets This tutorial explains how to merge different Excel spreadsheets 1 Download the example files from the webpage http www synkronizer com files examplefiles zip 2 Extract the zip file and copy the Excel files to a folder of your preference 3 Open Excel and start Synkronizer 4 Select the files Master xls and Update xls 2000 2014 XL Consulting GmbH Switzerland Tutorials 31 Td Synkronizer 10 New Project E Project Help Workbooks Master xis B eee Worksheets de ie P 22 d ID ist sheet 2nd sheet Select sheets then click to add to the list 5 In the section Worksheets select the worksheets Customers 2009 10 31 and Turnover Now click then on the che
36. 3 54 FullName 93 functionality 6 Ge Group cell differences 62 Group records 44 H hidden 54 hidden worksheets 42 hide 35 highlight 49 Highlight remove differences 61 HighlightDescription 101 HighlightType 101 horizontally 58 Hyperlinked 49 hyperlinks 49 icon 58 icons 61 ID 115 2000 2014 XL Consulting GmbH Switzerland identical names 44 ignore 54 ignore case 54 ignore datatype 54 Index 116 installation 10 IsValid 104 Item 108 ls Language 58 like operator 54 link 42 link worksheets 42 Load 85 103 location 7 lock 47 main form 41 Manual Activation 21 manually 42 match 42 match worksheets 42 Matchinclude 110 MatchType 110 merge 30 message 120 Missing columns 61 Missing rows 35 61 MissingCols 117 MissingRows 118 MissingTabs 117 modify permission 10 muMasterUpdatelD 121 N Name 93 Navigator 35 network 10 no internet connection 21 Non Admin Setup 7 numeric tolerance 54 Index 127 128 Synkronizer 10 P Pair 110 PairActivate 94 PairDeactivate 94 Pairs 92 105 Password 47 PasswordList 109 Password protected worksheets 47 perform 49 Preferences 51 primary key 44 Problems 65 Progress 94 Project 41 47 84 Project Settings 51 projects 6 41 protected 47 protected worksheets 42 47 ProtectionRemove 90 ProtectionValidate 90 R Range 44 115 RangeAddr 113 Re compare worksheets 61 redundant records 61 reference 69 RegEx 54 reg
37. EMUMEFAtlOn Seis ae occa ete cas cov ceed Aran sao 121 MUM aste rUp date BANANA 121 sSyCompare ALEAN 121 SYRE POM ATEREA syHighlightType syFormats Flag SYP OGE KANA SYSNOW Hide Flag EEA 123 SYM ath Ty TZANA 123 SYM atchl MUA a e Ea e e aae aaa Eae a aa a a Aa eS Aa a Eaa En a a N aaae iadaaa taa 124 SyDatabas e Flag ANANA 124 SYRe SUC Ode iii o 124 SALAS de EEA AAA 124 syResetType GAUZ dU AAA Index 2000 2014 XL Consulting GmbH Switzerland 6 Synkronizer 10 1 1 Welcome Dear User Welcome to Synkronizer the most powerful Excel comparison tool in the market Synkronizer will help you find differences in your Excel files Fast reliable and with a clear interface to browse the results on screen But there s more we don t just show you the differences we ll help you maintain your valuable excel data and formulas and provide you the tool to get your files back in sync You can update missing or different data and move it from one file to the other fully transparent and with full control e Comparison of Excel workbooks and highlighting of detected differences in worksheets e Project configurations can be saved to disk for easy retrieval of your favorite settings or repetitive tasks e Production of a user friendly difference report e Transfer of missing data and updating of differences e Merging of Excel spreadsheets databases e Programmatic access with Visual Basic for Application VBA developer editi
38. Fil i i 1 sFile Dir Loop loop all old files For i 1 To UBound aFiles sFileOld sFolderOld amp aFiles i sFileNew sFolderNew aFiles i sFileRep sFolderRep Difference Report amp aFiles i sFileRep Left sFileRep InStrRev sFileRep 1 xlsx check if new is there If Len Dir sFileNew gt 0 Then 2000 2014 XL Consulting GmbH Switzerland Synkronizer 10 Set oProj SynkProject With oProj With Settings CompareType syCompareFormulas Formats D Filters 0 HighlightType syHighlight ShowHide D ReportType syReportStandard End With Files Load sFileOld sFileNew With Pairs MatchInclude 0 MatchType syMatchFirstByName AddMatched nd With E Execute If Results Sum Then n 1 n 1 1 If Len Dir sFileRep gt 0 Then Kill sFileRep ReportWorkbook Close True sFileRep Else n 0 n 0 1 ReportWorkbook Close False End If store workbook names sWorkbooks 0 Files Workbook 0 Name sWorkbooks 1 Files Workbookx 1 Name close project Unload CloseFiles Fals End With Set oProj Nothing save and close workbooks With Application DisplayAlerts False Workbooks sWorkbooks 0 Close SaveChanges Tru Workbooks sWorkbooks 1 SaveAs sFileNew Workbooks sWorkbooks 0 Close SaveChanges Fals DisplayAlerts True End With End If Next i MsgBox
39. Filter contain the project settings in the other menus the applications are displayed e If settings are changed in the first three menus and you press then the Ok button these settings are saved in the project Changes in the other menus have no effect on the projects Main Form Synkronizer 10 Settings bedel Main Form Worksheet matching Select how sheets are matched Compare Match all by name nA Filter Select to include in selection and lists Navigator Y Empty sheets V Protected sheets Colors 7 Hidden sheets User Interface Actions Highlight differences System sg Highlight Differences e Create difference report No Report bd Reset preferences f Save preferences j OK Cancel On this page the default values of the main form can be defined You can determine how the main form should behave when Synkronizer is started or a new project is opened Select how sheets are matched Here you specify how the worksheets should be matched when a new project is created Select to include in selection and lists In this section you can determine which worksheet types can be compared Actions Highlight differences Create difference report Use these buttons to set the actions to be executed by default Important If no project is active or empty the default settings are displayed If a project is active then the project settings are shown 2000 2014
40. Formula property of the cells are compared rmulas syCompareVa The Value2 property of the cells are compared lues 8 8 3 syReportType Constants used by the ReportType property in Settings syReportNon No report is created 2000 2014 XL Consulting GmbH Swizerland 122 Synkronizer 10 8 8 4 8 8 5 8 8 6 e syReportStan A workbook is created range addresses are plain text dard syReportHype A workbook is created range addresses are hyperlinks to the original document rLinks syReportXML An xml files is created uses XSL transformation Needs IE and MSXML syHighlightType Constants used by the HighlightType property in Settings syHighlightN Differences are not Highlighted one syHighlight Differences are Highlighted No Cell coloring for all cells if not removed before processing syHighlightW Differences are Highlighted Cell coloring for all cells is removed before processing ithReset syFormatsFlag Constants used to by the the Formats property in Settings syFormatsEN Enable or Disable all other Formats options ABLED syFormatsNu Compare Number Numberformat properties of cells mber syFormatsAli Compare Alignment properties of cells gnment syFormatsFon Compare Font properties of cells t syFormatsBor Compare Border properties of cells der syFormatsPat Compare Pattern Interior Fill properties of cells tern syFormatsPro Compare Protection properties of cells tection syFiltersF
41. GmbH Switzerland Same function as above A EA Delete missing rows of 1st worksheet TT With these buttons you can delete the missing rows of the 1st worksheet Either all or only the selected line will be deleted These commands are available in different columns duplicate keys and duplicate records E E3 Delete missing rows of 2nd worksheet same function as above ra Transfer cell differences from 1st to 2nd worksheet With these buttons you can transfer different cell values formulas or formats from the 1st to the 2nd file transfer from left to right There will be transferred either all or only the selected cell difference Once the cell difference was transferred it is shown in red EA These commands are also available in different cell formats le 83 Transfer cell differences from 2nd to 1st worksheet same function as above 3 Sort cell differences Tr The cell differences are by default sorted by rows and within rows by columns Use this button to change the sorting This command is also available in different cell formats 3 Group cell differences Each cell difference is individually presented When you click this button the cell differences in each row or column are grouped depending on the sorting see above This comes very handy when you want to transfer all cell differences of a column with one click This command is also available in different cell formats EN Show hide grouped cell differ
42. Select the add ins menu 3 With the drop down button select COM Add ins 4 Remove Synkronizer 10 Excel 2003 XP A bit more complex as the ComAddins menu item is not installed by default Select the menu command View Toolbars Customize Select the Tools category Search COM Add ins in the Commands list Select the COM add ins with the mouse and move it into the Excel Tools menu Close the Customize window Select the menu Tools COM Add Ins Remove the check of Synkronizer 10 YN GO JO db JI b KO Network Installation Application Only in case you have purchased a network license registration key the files needed by the application can be installed to a shared network drive although it is not mandatory The network license file must be installed on a shared network drive 2000 2014 XL Consulting GmbH Switzerland Install Uninstall 11 The network license The network license file is a single file located on a shared folder which contains the registration key for the edition s you purchased That license file will be activated once and locked to the server path The client PC s can then be allocated or removed based on their network name without further need for activation Initial Installation 1 Create a shared network drive 2 Install the software using the method Network Installation Setup Synkronizer Lo bi Select Components Which components should be inst
43. Show hide When enabled Synkronizer shows or hides selected entries after comparison Allow overwriting data by empty cells If this option is selected all differences are updated Unselect this option to prevent cell contents being overwritten with blank empty content from the other file Use entire rows columns when inserting deleting Normally only the rows within the data range are inserted deleted by transferring deleting rows or columns If this checkbox is active the whole row column are inserted deleted Remove highlighting on exit Here you can specify whether Synkronizer highlighting shall be removed or not when you exit the Navigator The following options are available e Ask to undo highlighting on exit A message box appears whether Synkronizer should remove the highlighting e Never undo highlighting on exit The highlightings remain preserved e Always undo highlighting on exit The Synkronizer highlightings are removed when closing the form with the old background colors restored Your background colors won t be lost 2000 2014 XL Consulting GmbH Switzerland s Synkronizer 10 4 2 5 Colors Synkronizer 10 Settings beei Main Form Highlight System Compare Ca Missing columns Oz Missing rows Filter mr Duplicate keys Navigator iv Duplicate records cotos i E Different content z Different formats User Interface Difference Report System aay e My Ziz Report he
44. ShowHideENABLED syHideRowsIden End With ArrangeWindows Execute MsgBox Results SummaryMessage vbOKOnly vbInformation Synkronizer Unload CloseFiles True DisplayUndo False End With End Sub ua gt gt aaa a a a ln i i i a A gl A a e sg sa is Example 2 Compare all sheets highlight differences Public Sub Example2 Dim oProj As Synkronizer Project Set oProj With oProj SynkProject If oProj Is Nothing Then Exit Sub FILE1 Files Load FILEO With Ignore empty Pairs hidden or protected sheets MatchInclude 0 MatchType syMatchAllByName AddMatched With End With Settings CompareType syCompareFormulas Formats D Non strict Ignore Case Whitespace and Type differences Filters syFiltersENABLED syFiltersCase syFiltersTrim syFiltersType HighlightType syHighlightWithReset ReportType syReportNone ShowHide D End With ArrangeWindows Execute MsgBox Results SummaryMessage vbOKOnly vbInformation Synkronizer Unload CloseFiles False DisplayUndo False 2000 2014 XL Consulting GmbH Switzerland Synkronizer 10 End With End Sub Example 3 Compare first sheets Public Sub Example3 Dim oProj As Synkronizer Set oProj SynkProject If oProj Is Nothing Then With oProj create a report Project Exit Sub Files Load FILE With Pairs M
45. Synkronizer 10 User Manual 2000 2014 XL Consulting GmbH Switzerland 2 Synkronizer 10 Table of Contents Part 2 Part Il Part Ill Part IV 1 2 Foreword Welcome 6 MAIDEN SGAE 6 EGOI OD E are a eai a aa ea Aa aa E ek aaa sie fesse e ave ieee 7 Install Uninstall 7 HSO e cs ces AE A iE idan canta caves a dada tats E E E erie ezazie 7 ULE SALBEA 10 Network lar Eau 10 Adding alowed WOrk ALA EAEN 13 Administrative Installa f ONA a a idad aia 15 SZ Aa BAEZ EA ULE 18 RO GIS TOM ENE 19 Manual ACtivat iO iia AAA 21 De activate Softw are 000 AA aan 24 Tutorials 25 Too Many differe NCO EAE 25 Merge spreadsheets ices Eka a alte a Eak a EE sess 30 Determine MISSING BUL LANA 35 Forms 41 PROG Cts AAA 41 AAZ A DOANA UEA AUSAZ E Ranges Databases General information about databases oooccccccccnccnococannnoooncnnnnonnnnnnnnnnnnnnnnnnnnnnnonnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnns 45 Linksdata On R AE E EE E PEE E EEE E A EEE ld Mi eee verse en nee eee keee SOTEINGS EE E TE E snes a aie ake aerea EEA d EAEAN AGOZ rssszzur anea Highlight Differences Create a Difference Report SUA EEAO MainiForm AAO 52 JELA EAEAN 53 Differences betw een formulas and uSSz rra 53 eU dA Navigator Colors Dy User Interface 2000 2014 XL Consulting GmbH Switzerland ENSEIU e a A Ek E Ae 59 3 AENA EAEE 60 AO E A a a a aa O a a a A a ae aaa E 61 Detail INTORIM ation KAE 62 Part V
46. Synkronizer software 2 Generate the activation deactivation key a 3 Enter the activation key in the form Please copy the activation key below and paste it in the Activation field of the Synkronizer software see picture Activation Key U2KUYQI D3SONWG FEQFIRQ cd A erre rr rr pren ra The internet connection to the server could not be established Please click this link and paste the datastring above in the field on the webpage The server will generate an activation key which must be entered in the field below Enter M Synkronizer License ZN hh Your mothers maiden name e mier I ere 0001 00000000 0000 0000 0000 001FD0551 DCB TELEBEFF Gigabyte Technology Co Ltd EP35 DS3 28444E0D 1 MILLER beze E Ge E E Please click this link and paste the datastring above in the field on the webpage The server will generate an activation key which must be entered in the field below U2KUYQI D3SONWG FEQFIRQ Enter 9 Enter the activation key in the input field and press Enter 10 The software is now activated 2000 2014 XL Consulting GmbH Switzerland z Synkronizer 10 If you have any problems with the registration activation process please read chapter Problems with registration 2 6 2 Deactivate Software If you should purchase a new computer or should have problems with your current computer you can transfer your license anytime to another computer Just deactivat
47. XL Consulting GmbH Switzerland Forms 53 4 2 2 Compare In this form you can specify how Synkronizer shall compare the worksheets Synkronizer 10 Settings A Main Form Compare cell contents ini Formulas Filter Values Navigator Compare cell formatting Enabled Colors User Interface Alignment System Reset preferences Save preferences OK Cancel Compare cell contents Here you can determine whether Synkronizer compares the data by formulas or values If you do not know the difference between these comparison types please first read the chapter Differences between formulas and values Compare cell formatting If cell formats should be compared you can select the desired format by clicking the appropriate check boxes Comments on the use of comparing cell formats e Only cell formats within the data range is compared e The comparison works properly only if both files use the same style e The comparison by format slows the comparison process Important If no project is active or empty the default settings are displayed If a project is active then the project settings are depicted 4 2 2 1 Differences between formulas and values Below example illustrates the difference between values and formulas The sales for the month of January changed from US 10 00 to US 10 50 Therefore the turnover of the 1st Quarter raises also 2000 2014 XL Consulting GmbH Switzerland s
48. ad Only Long value Syntax expression DuplicateKeys MUid expression A variable representing a Results object Parameters MUid muMasterUp Selects either member of the Pair datelD See Also e Results Object e Results Object Members 2000 2014 XL Consulting GmbH Switzerland Object Model Reference no 8 7 3 5 DuplicateRecords Property Returns the number of Duplicate Records database only Read Only Long value Syntax expression DuplicateRecords MUid expression A variable representing a Results object Parameters MUIid muMasterUp Selects either member of the Pair datelD See Also e Results Object e Results Object Members 8 7 3 6 DifferentContent Property Returns the number of Cells with Different Content Read Only Long value Syntax expression DifferentContent expression A variable representing a Results object See Also e Results Object e Results Object Members 8 7 3 7 DifferentFormats Property Returns the number of Cells with Different Formats Read Only Long value Syntax expression DifferentFormats expression A variable representing a Results object See Also e Results Object e Results Object Members 2000 2014 XL Consulting GmbH Switzerland 120 Synkronizer 10 8 7 3 8 8 7 3 9 8 7 3 10 Sum Property Returns the total number of differences found Read Only Long value Syntax expression Sum expression A variable representing a Results object See Als
49. ader1 BB vy Ziz Report header 2 ma gt Striping color Save preferences ok I cancer In this section you can determine the colors with which Synkronizer highlights the differences In addition you can customize the colors of the difference report 4 2 6 User Interface Synkronizer 10 Settings em Main Form User interface Arrange Excel windows Compare Vertical y GE Appearance of forms Navigator Standard operating system y ot Excel menu options ZE F Show button on the standard toolbar Language System a English E Check our website if your language is not listed Save preferences OK Cancel Arrange Excel windows Here you can determine whether the worksheets should be tiled vertically or horizontally Appearance of forms 2000 2014 XL Consulting GmbH Swizerland With this drop down field you can determine how the layout of the forms background buttons etc should look like Synkronizer offers a number of different layout styles Excel menu options If you select this check box the Synkronizer icon appears on the Standard toolbar of Excel Language With this field you can specify the language Important If your language is not included we offer a developer version for free if you translate the language of the Synkronizer GUI Graphical User Interface to a not yet available language Please contact us via the contact form to receive detailed instruct
50. aging differences LoadBehavior dword 00000003 CommandLineSafe dword 00000000 for 64bit windows HKEY LOCAL MACHINE SOFTWARE Wow6432Node Microsoft O0ffice Excel Addins Synkronizer FriendlyName Synkronizer 10 0 Description Tool for finding and managing differences LoadBehavior dword 00000003 CommandLineSafe dword 00000000 Language data is not found If you work with Windows 7 or Windows Vista it may occur that not all language data will be displayed The reason is that Windows saves automatically the data in the following hidden folder C Users User XY AppData Local VirtualStore Program Files x86 Synkronizer You must remove this directory and then re install Synkronizer after that the latest language data is 2000 2014 XL Consulting GmbH Switzerland e Synkronizer 10 visible WMI Windows Management Instrumentation service is not installed or not working If you get this error you ll need to verify that WMI service is installed and runs properly Please run install the following utilities WMI Diagnosis Utility http www microsoft com downloads details aspx familyid d7ba3cd6 18d1 4d05 b11e 4c64192ae97d amp displaylang en WMI Repair Instructions http windowsxp mvps org repairwmi htm Known Error Messages e Microsoft Visual Basic Invalid Picture e Object library invalid or contains references to object definitions that could not be found e Ca
51. alled P Select the components you want to install dear the components you do not want to install Click Next when you are ready to continue Network Installation for network licenses only X eba bez comes 3 Then select the network folder in which the license should be copied Note Synkronizer requires a UNC path 2000 2014 XL Consulting GmbH Switzerland 12 Synkronizer 10 ZE e e Setup Synkronizer bo a bi Select Network License folder a folder on a network share designated for the shared license file P Select the network folder containing the shared license file If the folder is empty a blank license file will be created but existing files will not be overwritten Click Next when done Browse Te Cana 4 Complete the installation 5 Start Excel and Synkronizer 6 Register and activate the software 7 Add the workstations which are allowed use Synkronizer see below Installations on workstations The installation on the workstations is very easy Perform a network installation and select the network directory which contains the license file If the software is activated and the workstation is assigned the user can work with Synkronizer immediately Schema Network 2000 2014 XL Consulting GmbH Switzerland Install Uninstall 13 E B license ink license ink b 2 ps license bk license ink license ink license bk The license file license
52. allowed computer list Pe Synkronizer License LK beei ete EI Professional Edition SI 1 10 Computer Description CH MKT DESKO5 Roger Milla pa Add a series of workstations You can prepare a list of workstations and descriptions and paste it into the application The list can be created from a script or typed in a text editor or excel worksheet Proceed as follows 1 Start Excel 2 Create a list with the workstation names and users 3 Select and copy the list 2000 2014 XL Consulting GmbH Switzerland Install Uninstall 15 aa 4 Open the Manage tab of Synkronizer 5 Press Paste The list is filled with a maximum number of licensed computers Pa Synkronizer License beei berez x peste EE 100 Description EUR MKT DELLOO1 Roger Paris Ll EUR MKT DELLOO2 Hilda Frankfurt Leka USA MKT HP001 George Seattle s 1 USA MKT HP002 Bill Little Rock lel USA MKT HP003 Steve Redmont IND DEV ACEROO1 Prawash Mumbai lel IND DEV ACER002 Rajya Mumbai leie Ede te Info Manage Changing editing Items in the list cannot be edited or changed If a name or description is wrong remove the existing item and then add the item with the correct text 2 4 Administrative Installation Definition This installation method is suitable for the installation of multi user licenses In a distribution file the administrator defines the COMPUTERS by their bios computer nam
53. atchInclude MatchType AddMatched End With With Settings CompareType Formats 0 Filters 0 HighlightType ReportType ShowHide nd With Execute 0 0 E ReportWorkbook Windows 1 WindowState syMatchFirstByName syCompareFormulas syHighlightNone syReportStandard xlNormal vbOKOnly vbInformation Synkronizer MsgBox Results SummaryMessage Unload CloseFiles True End With End Sub a deg egn a eg e er E en dar ga Ee be eei Sen deg eg e Geu Example 4 Database comparison highlight differences Public Sub Example Dim oProj As Synkronizer Project Set oProj SynkProject If oProj Is Nothing Then Exit Sub With oProj Files Load FILEO FILE1 With Pairs Add Addresses Addresses End With 2000 2014 XL Consulting GmbH Switzerland Visual Basic for Applications VBA 73 define database options With Pairs 1 DBRow 1 define primary key columns of Ist file DBKeys 2 3 DBOptions syDatabaseGroup True End With With Settings CompareType syCompareFormulas Formats 0 Filters syFiltersENABLED syFiltersCase syFiltersTrim syFiltersType HighlightType syHighlight ReportType syReportNone ShowHide 0 End With ArrangeWindows Execute MsgBox Results SummaryMessage vbOKOnly vbInformation Synkronizer Unload CloseFiles True DisplayUndo False End With End Sub
54. be stored in the license file The license file is now uniquely coupled to your PC and your software is activated 2000 2014 XL Consulting GmbH Switzerland z Synkronizer 10 Network Installation Before you can activate a network license you ll need to create a share folder on the network drive Please study this chapter To start the activation process 1 Start Synkronizer The startup screen appears also shown when you select the menu Project Settings System Registration Activation M Synkronizer License LE beei SVAKFOnIZeF BENUTZER PC 2 Press Register The following form appears MF Synkronizer License LE beei Register Enter the registration key and press Validate 2000 2014 XL Consulting GmbH Swizerland Install Uninstall 21 Multiuser License If you should install a multiuser license an additional input field is displayed next to the registration key field see below picture This field contains the license number sequential number from one to the number of purchased licenses Be aware that you enter in that field a number which not has be entered yet Validate 3 Please enter then the secret question Click Activate M Synkronizer License LE beei Your mother s maiden name B Note The security answer is an additional safeguard so that no one else can use your license If you want to install your license on a second PC you need to en
55. books 1 SaveAs sFileNew Workbooks sWorkbooks 0 Close SaveChanges Fals DisplayAlerts True End With Else Print 1 aFiles i amp vbTab error End If Next i Print 1 Print 1 Comparison time Format Timer t0 00 00 s Close MsgBox finished amp vbLf amp _ n 0 amp workbooks without differences vbLf amp _ n 1 amp workbooks with differences s reports vbOKOnly vbInformation Synkronizer End Sub 8 Object Model Reference 8 1 Application Object 8 1 1 Application Object Top level object Entry point for all automation Remarks The application object is only accessible when you have a licensed Developer Edition Example To gain access to the Application and the ActiveProject in VBA you should do following 2000 2014 XL Consulting GmbH Switzerland 82 Synkronizer 10 8 1 2 8 1 3 8 1 3 1 Dim synAdd As Office ComAddin Dim synApp As Synkronizer Application Dim synProject As Synkronizer Project set the comaddin object Set synAdd Application ComAddins synkronizer connect ensure the comaddin is connected If not synAdd Connect then synAdd Connect True set the application Set synApp synAdd Object set the project Set synProject synApp ActiveProject See Also e Application Object e Application Object Members Application Object Members Methods Properties ActiveProject Project that is currentl
56. bution Manager xlsm E Gi Bl LicenseDistribution Manager xlsm Microsoft Excel res o xd i e A yout me Te ETE an EEE SE OTZ a OO o gP SE K Se erara ie eki y See e E Bedin e Formatierung g Einf gen E OE A ESA SEE EZ Als Tabelle formatieren Zo L schen E X Soroen vg EA d rS Oe An ESS dE Sr Br 00 Sd E Zellenformatvorlagen gt PO Format Q EEE suchen ued Zwischenablage amp Schriftart Ausrichtung Zahi Formatvorlagen Zellen Bearbeiten B5 e Fe Machines ba LA E D E F G H 1 J K L DE d export XML import XML 4 BE Machines 6 computer name license number comment question answer comment 7 y E d E D 8 COMPUTER_001 Pro10 0010 John Doe Pro10 1234567 ABCDEFG 1234567 10 2 Beethoven Example 9 r 10 d 11 id D r 13 SZ A 14 15 16 17 18 19 20 21 22 E M 4 M LicenseDistribution 3 DER D KI Bereit 2 IRD 1009 Zira 2 This file contains all relevant data set which is necessary for the installation on the workstations The following chapter describes how the data must be entered File LicenseDistribution xml This file is being read by the workstations It can be edited with any XML editor but we ve provided an excel file for your convenience File LicenseDistribution Manager xlsm This Excel file is just an editing tool for the xml file listed above e g the tables in the Excel file are mapped to the XML and provid
57. ces of 1 cent or smaller Text Filter With a text filter you can choose to ignore specific differences Choose between VBA s like operator or a RegEx pattern for Microsoft VBScript Regular Expressions if installed on your system Following are a few examples of how to use a TextFilter e In a project file the cells with Done should be ignored Syntax Like TextFilter Done or DdJone if you want e Ina project file the cells with Todo or Done should be ignored Syntax RegEx TextFilter Todo Done e Ina database the fields with New York or Los Angeles should be ignored Syntax RegEx TextFilter New York Los Angeles Note Case sensitivity for text filters depends on Ignore Case option If you want to filter a date then you must enter the numerical value of the date e g the 1 January 2010 corresponds to the number 40179 Important If no project is active or empty the default settings are displayed If a project is active then the project settings are shown 2000 2014 XL Consulting GmbH Switzerland 4 2 4 Navigator Synkronizer 10 Settings bedel Form Show hide Compare Filter Colors Show Missing columns User Interface Updating V Allow overwriting data by empty cells System E Use entire rows columns when inserting deleting Remove highlighting on exit Reset preferences Ask to undo highlighting on exit D Save preferences OK Cancel
58. check Synkronizer ignores differences between small and capital letters e g John Miller JOHN MILLER Trim whitespace If you select the Trim whitespace option Synkronizer removes spaces at the beginning and end of the cell entries and comments before comparing them Thus John Miller and John Miller with trailing spaces will be considered the same You can use this to ignore the differences between empty cells and cells with spaces only Ignore datatype It is possible that the numbers are formatted in a one worksheets as a number in the other as text With this option these differences are ignored Note If date values are shown in one worksheet as date and in the other as a number then these are formatting differences These differences can by recognized with the setting Compare Compare cell formatting Number Ignore cells with constants formulas If one of these options is active all cells with constants values or formulas are ignored Ignore hidden columns rows 2000 2014 XL Consulting GmbH Switzerland s Synkronizer 10 If you click on these check boxes the hidden columns and or rows are not compared Obviously this option is only effective if the columns rows in both tables are hidden Numeric Tolerance Normally numeric tolerance is 0 All differences between numeric entries are reported Synkronizer allows you though to ignore small differences Set numeric tolerance to 0 01 to ignore differen
59. ckbox see cursor below f Synkronizer 10 New Project beite Project Help Workbooks Masterals E E Updates E A Worksheets 32 ie 2 22 a ID ist sheet 2nd sheet Turnover Select sheets then click to add to the list The worksheets are added to the worksheet list 6 In order to activate the database modus you need to double click on the worksheets or click on the database symbol see cursor 2000 2014 XL Consulting GmbH Swizerland 32 Synkronizer 10 Td Synkronizer 10 New Project bie Project Help Workbooks Master xis E EO Updates 2 Worksheets de 22 2 22 W ID ist sheet 2nd sheet Actions Highlight Ditterences E tio Report Tj La La 2 dE A The options frame is now open 7 Click Enable DB mode and select the primary key Apparently the Fieldname drop down is empty see cursor below This means that no identical field names were found For more information about the behaviour of databases and primary keys please read chapter General information about databases Synkronizer 10 New Project beiei Project Help Workbooks Masterxls E EO Update xts a Worksheets de ie P 22 ID ist sheet 2nd sheet 1 Customers 2009 10 31 Turnover S Options Edit ranges EI E V Enable DB mode ID Fieldname Row for headers 1 Y k m No matching fieldridines E Sort records found on r
60. de Returns or sets a series of options specifying which group of Rows and Columns to Show or Hide ShowHideOption Returns or sets the specified ShowHide option ShowHideDescription Returns a descriptive text for the enabled ShowHide options 2000 2014 XL Consulting GmbH Swizerland 96 Synkronizer 10 8 3 3 8 3 3 1 8 3 3 2 8 3 3 3 Events Methods DefaultsLoad Loads user preferences Syntax expression DefaultsLoad expression A variable representing a Settings object Return Value none See Also e Settings Object e Settings Object Members DefaultsSave Saves settings as user preference Syntax expression DefaultsSave expression A variable representing a Settings object Return Value none See Also e Settings Object e Settings Object Members DefaultsReset Removes user preference reloads default values Syntax expression DefaultsReset expression A variable representing a Settings object 2000 2014 XL Consulting GmbH Swizerland Object Model Reference Return Value none See Also e Settings Object e Settings Object Members 8 3 4 Properties 8 3 4 1 CompareType Returns or sets a constant specifying which content property to compare syCompareType constant Syntax expression CompareType expression A variable representing a Settings object See Also e Settings Object e Settings Object Members 8 3 4 2 CompareDescription Returns a descriptive text for the Co
61. e and the LICENSE KEY they will use During installation the user must select the folder containing the distribution files A shortcut to this 2000 2014 XL Consulting GmbH Switzerland 16 Synkronizer 10 folder is then placed in the installation folder When the user starts the Synkronizer program it will first look in the install folder for a valid license file If it fails BUT it does find a LicenseDistribution shortcut it will then lookup the computer name in the distribution file and read the associated registration key Then it contacts our activation server and creates a local license file Initial Installation 1 Install the software using the method Administrative Installation Select Components Which components should be installed P Select the components you want to install dear the components you do not want to install Click Next when you are ready to continue Administrative Installation for multiuser licenses only z 2 Then select the directory in which the distribution files to be copied a folder or a URL designated for the license distribution files P Select the location of the the distribution files If the location is empty then blank files will be installed but existing files will not be overwritten Click Next when done 3 Complete the installation 2000 2014 XL Consulting GmbH Switzerland Install Uninstall 4 Open the file LicenseDistri
62. e Also e Pairs Object e Pairs Object Members Properties Item Property Returns a single item Pair from the collection Pair object Syntax expression Item Index expression A variable representing a Pairs object Parameters Index Variant A valid ID or Index for the Pair See Also e Pairs Object e Pairs Object Members 2000 2014 XL Consulting GmbH Swizerland Object Model Reference 109 8 5 4 2 Count Property Returns the number of members in the collection Long value Syntax expression Count expression A variable representing a Pairs object See Also e Pairs Object e Pairs Object Members 8 5 4 3 Exists Property Returns True if specified ID or Index exists in the collection Boolean value Syntax expression Exists Index expression A variable representing a Pairs object Parameters Index Variant A valid ID or Index for the Pair See Also e Pairs Object e Pairs Object Members 8 5 4 4 PasswordList Property Returns or sets a semicolon delimited list of passwords needed to access protected sheets String value Syntax expression PasswordList expression A variable representing a Pairs object See Also e Pairs Object e Pairs Object Members 2000 2014 XL Consulting GmbH Switzerland rma Synkronizer 10 8 5 4 5 8 5 4 6 8 6 8 6 1 MatchInclude Property Returns or sets options specifying types of worksheets to include by AddMatched method syMatchIncludeFlag con
63. e Excel Synkronizer 10 does NOT work with the 64 bit version of Excel 2010 In order to install the application proceed as follows Download the latest version of Synkronizer Log in to Windows using an account with administrator rights Double click the installation file SetupSynkronizer 10 X xxx exe Follow the instructions Done e Start Excel 2010 2007 You ll find the Synkronizer 10 icon in the Review tab e Start Excel 2003 2002 XP You ll find the Synkronizer 10 icon in the Tools menu ma b JA b b Type of installations There are three different installation methods e Standard Installation This installation method is used for single user licenses e Network Installation Standard Installation PLUS a shortcut to a shared network license file Only for users companies who purchased a special NETWORK LICENSE KEY This installation is designed for corporate networks Workstations need PERMANENT access to the license data which is stored on the network share During the installation you will be asked to specify TWO directories the INSTALLATION directory where the program files will be installed preferably on the local disk the NETWORK directory where the license file is located This MUST be an existing directory An empty license file will be copied here when it doesn t exist a shortcut to the network license file will be placed in the installation folder e Administrative Installation Standard Instal
64. e a convenient way to edit and maintain the actual xml data DO NOT FORGET TO EXPORT THE XML FROM THE EXCEL FILE Table Licenses The license data can be entered in this table Enter the following data ID Unique identifier for the license eg PRO10 DEV5 This ID will be used in the table machines key Registration key of Synkronizer count Number of licenses question Number of the security question required for software activation 2000 2014 XL Consulting GmbH Switzerland a Synkronizer 10 2 5 answer Answer of the security question comment Optional comment Table Machines This table defines the workstations computers or laptops on which Synkronizer is to be installed The following fields are available computer Name of the computer to be installed Synkronizer The computer name must be unique if name not the installation fails later license Identifier or ID of the license which is entered in the first column of Table Licenses number Unique number of the license If for example a ten user license is present the numbers one through ten must be entered in this column comment Optional comment Once the data are entered save the excel file AND click on the button XML export This generates the distribution file in the same directory To export to a different directory or file use standard excel xml export form the developer tab TIP for computers that wont be connected to your network during first use
65. e many new possibilities Existing code will require a wrapper Addin which provides almost full backward compatibility with the previous single function Many situations where customers felt the need to write there own code should be handled by the ability to use project files Synkronizer 10 is now written as a Com Addin with it s own object model and offering more flexibility to developers albeit with a completely new syntax Please read the extensive help provided for developers Can t find the answer Our support is happy to help with code transition Feel free to contact us via the contact form on our website 1 2 Editions Synkronizer 10 is available in 3 editions Free Evaluation Professional Developer Range restriction A1 Z100 unrestricted unrestricted Highlight Differences 9 Create a Difference Report Navigator Automation VBA access b Price free EUR 79 EUR 149 When you purchase a license for Synkronizer Professional or Developer Edition you ll receive a registration key which is then sent to our Activation server that verifies that the registration key is legitimate and not used by anyone else 2 Install Uninstall 2 1 Install Installation 2000 2014 XL Consulting GmbH Switzerland 3 Synkronizer 10 Synkronizer 10 is a COM add in for 32 bit versions of Excel 2010 32 bit 2007 2003 and XP It works on 32 bit and 64 bit versions of Windows XP thru Win7 if you install 32 bit Offic
66. e number of Cells with Different Formats Sum Returns the total number of differences found SumByCode Returns the number of specified differences found SummaryMessage Returns the results as a formatted text for messagebox TextDisplay Returns the number of specified differences found as text Events Properties MissingTabs Property Returns the number of Missing Sheets auto matching only Read Only Long value Syntax expression MissingTabs MUid expression A variable representing a Results object Parameters MUid muMasterUp Selects either member of the Pair datelD See Also e Results Object e Results Object Members MissingCols Property Returns the number of Missing Columns or Fields Read Only Long value Syntax expression MissingCols MUIid expression A variable representing a Results object Parameters MUid muMasterUp Selects either member of the Pair datelD 2000 2014 XL Consulting GmbH Switzerland ns Synkronizer 10 8 7 3 3 8 7 3 4 See Also e Results Object e Results Object Members MissingRows Property Returns the number of Missing Rows or Records Read Only Long value Syntax expression MissingRows MUid expression A variable representing a Results object Parameters MUid muMasterUp Selects either member of the Pair datelD See Also e Results Object e Results Object Members DuplicateKeys Property Returns the number of Duplicate Keys database only Re
67. e on the share Administrative Installation The user who performs the installation MUST have administrator privileges The user who maintains the License distribution files must have WRITE MODIFY permissions to files on the network share The user of the local computer needs READ permission to the license distribution file on the share Problems Setup should run without problems However the infamous User Account Control does make life difficult in some cases If you are on a corporate network go to your administrator If you are on a home PC or laptop and setup doesn t complete as expected or synkronizer reports initialization errors on startup some manual work may be required Dependencies Synkronizer setup assumes the VB6 runtimes are installed These are pre installed on Vista and Windows7 but could be missing on older Windows XP installations If not installed you ll find them here Search Microsoft com Synkronizer setup installs some DIl s and Ocx files to the install directory which it attempts to register with RegSvr32 RegSvr32 may fail if your login account doesn t have Administrator Privileges or if User Account Control in Win7 Vista prevents access We need to open a console with admin rights To do that we must create a shortcut to cmd exe with some extras e Right click the desktop Select menu New Select menu Shortcut Type cmd exe Follow instruction and the shortcut is created e Right click the shor
68. e the license from the old computer before you can activate it on the new one Proceed as follows 1 Start Synkronizer 2 Select Project Settings System Registration Activation The welcome screen is displayed M Synkronizer License LE beei SVYMKFONUZer Computer BENUTZER PC Info Manage 3 Select the tab Manage 2000 2014 XL Consulting GmbH Switzerland Install Uninstall 25 Tr Synkronizer License LE beei Computer Edition BENUTZER PC Professional Edition Name Value License Registration Ok Edition Professional Edition Install type Local Activation U2KUYQI ERC6PNB M5LOO5U Ok LicenseID 0001 0001 Hardware Info Manage 4 Click Deactivate 5 The software is deactivated If you should have no internet connection you ll need to deactivate the software manually A manual deactivation works exactly the same like the manual activation The opnly difference is that you ll get an unlock key instead of an activation key Read this chapter for manually deactivating the software 3 Tutorials This section contains some tutorials to get familiar with Synkronizer 1 Too many differences 2 Merge spreadsheets 3 Determine missing rows 3 1 Too many differences This tutorial explains how to minimize the number of differences using the filter function When comparing cells Synkronizer first looks at the data type of the cells Numbers Text Date Boolean Err
69. ect Members Unload Method Unloads the project results Optionally closes the workbooks without saving Syntax expression Unload CloseFiles DisplayUndo expression A variable representing a Project object Parameters CloseFiles Boolean Closes the compared files workbooks DisplayUndo Boolean Optional When True and Highlighting or ShowHide is active then the user will be given the option to undo Return Value none Remarks If you want the files to be saved with possible highlighting and showhide then you must do so before the Unload method is called See Also e Project Object 2000 2014 XL Consulting GmbH Swizerland 8 2 3 4 8 2 3 5 Object Model Reference e Project Object Members Reset Method Resets specified objects or results Syntax expression Reset eReset expression A variable representing a Project object Parameters eReset syResetType Constant specifying which objects and variables should be reset Return Value none See Also e Project Object e Project Object Members Execute Method Executes the compare process Syntax expression Execute ActivePairOnly expression A variable representing a Project object Parameters ActivePairOnly Boolean When True only the ActivePair is processed Return Value none Remarks Includes the actions DoReport DoHighlight and DoShowHide specified in related Settings See Also 2000 2014 XL Consulting GmbH Switzerland
70. ells syProgressHi Processing DoHighlight ghlight syProgressSh Processing DoShowHide owHide syProgressRe Processing DoReport port syProgressHi Processing UnDoHighlight ghlightUndo syProgressSh Processing UnDoShowHide owHideUndo syProgressUp Updating cells Navigator only date 8 8 13 syResetType Constants used by the Reset method in Project and Pairs syResetResult Results are removed May display alert if Pairs are highlighted s syResetReleas Releases an event handler monitorirng accidental closing of the workbooks e syResetPairs All items in the pairs collection are removed syResetAll All settings will be removed from the project 8 8 14 syErrors Constants used to identify a group of errors possibly raised during Project load syErrinit Errors raised during initialization syErrProj Errors raised during project load Project will not be loaded syErrFile Errors raised during files load Project will not be loaded syErrPair Errors raised during Pairs add Pair will not be added syErrProp Errors raised when setting incorrect Pair options Pair will be added but these options will be ignored 2000 2014 XL Consulting GmbH Swizerland 126 Synkronizer 10 Index A action 49 Activation 19 59 ActivePair 92 ActiveProject 82 Add 106 Add SynkID 44 add worksheet 42 AddMatched 107 Appearance of forms 58 Application 81 Application Settings 51 Arrange Excel windows 58 Arrange windows 61
71. ences er If the cell differences are grouped the Details Icon is displayed Use this button to show or hide the cell differences within the columns and rows This command is also available in different cell formats Differences which are displayed with a character If a cell contains a text string that can be interpreted as a number date or boolean variable True False a indicator is shown E g the number 123 formatted as text is shown 123 2000 2014 XL Consulting GmbH Switzerland e Synkronizer 10 5 5 1 Project File FileFormat The Project file is a simple XML text file that can be edited with any standard text editor such as notepad When manually editing XML files be aware that the names of tags are Case sensitive Misspelling will cause failures Tip prepare your project using the Synkronizer form save it then edit it to suit your needs This is the layout of the file for a project where sheets are auto matched without any options set for any of the pairs You ll notice that there are no individual pairs listed inside the lt Pairs gt tag lt xml version 1 0 standalone yes gt lt Synkronizer gt lt Project ID 1 gt lt Settings gt lt CompareType gt 0 lt CompareType gt lt Formats gt 0 lt Formats gt lt Filters gt 0 lt Filters gt lt FilterTolerance gt 0 lt FilterTolerance gt lt FilterPattern gt lt ReportType gt 0 lt ReportType gt lt HighlightType gt 1 lt Hig
72. erTolerance expression A variable representing a Settings object See Also e Settings Object e Settings Object Members 8 3 4 11 ReportType Returns or sets a constant specifying which Report to create syReportType constant Syntax expression ReportType expression A variable representing a Settings object See Also 2000 2014 XL Consulting GmbH Switzerland Object Model Reference rz e Settings Object e Settings Object Members 8 3 4 12 HighlightType Returns or sets a constant specifying which Highlighting to perform syHighlightType constant Syntax expression HighlightType expression A variable representing a Settings object See Also e Settings Object e Settings Object Members 8 3 4 13 HighlightDescription Returns a descriptive text for the HighlightType Read Only String value Syntax expression HighlightDescription expression A variable representing a Settings object See Also e Settings Object e Settings Object Members 8 3 4 14 ShowHide Returns or sets a series of options specifying which group of Rows and Columns to Show or Hide syShowHideFlag constant Syntax expression ShowHide expression A variable representing a Settings object Remarks If the ENABLED flag is not set all other flags are ignored See Also e Settings Object 2000 2014 XL Consulting GmbH Switzerland oz Synkronizer 10 8 3 4 15 8 3 4 16 8 4 8 4 1 e Settings Object Members ShowHide Op
73. ers 2009 EE 9 e 8 qe i ow Addri Addr2 Formula 1 Formula 2 B B Customer ac Phone D D Address E EE City B B January ac February D D March AA eee EE May G G June d Missing columns 4 12 13 Alternatively you can transfer all columns by one click The corresponding button is shown in below illustration Synkronizer 10 Navigator Project Window Help ala SIE E SIE ao oO e Worksheet i a 14 1 Customers 2009 4 12 A a qe 1 Addri Addr2 Formula 1 Formula 2 a B B Customer ac Phone D D Address E EE City B B January cc February D D March E E E EE May G G June da Missing columns 4 12 The spreadsheets are now merged You can close the Navigator 3 3 Determine missing rows This tutorial explains how missing or different rows of two Excel spreadsheets can be 2000 2014 XL Consulting GmbH Switzerland 36 Synkronizer 10 recognized When you are working with large sheets your differences can be all over the place With the navigator you can jump from one difference to the next but you want overview and concentrate on for instance the rows that were added or deleted Synkronizer allows you to only display specific differences e g missing rows and blend out the remaining parts so your differences appear together on your screen This page will show you how to efficiently use the Sho
74. es tables etc is defined If you want to define additional information you have to open the Settings form The Settings form can be opened with the menu command Project Settings or the button depicted on the left For more detailed information please refer to the chapter Settings 41 5 Passwords p Password protected worksheets can be compared and a difference report can be created but the differences can not be highlighted hidden or transferred These actions can only be executed when you enter the passwords of the protected worksheets To enter the passwords just click on the button shown to the left The following forms appears 2000 2014 XL Consulting GmbH Switzerland as Synkronizer 10 r e Synkronizer 10 Passwords ES Locked sheets 1st sheet 2nd sheet B3 Information for PH Information for EO General By General ZO Quality Status ZO Quality Status 7 Financial Overvi Zi Financial Overvi GO Milestones By Milestones Enter passwords separate with enter Add passwords then press Test to verify In this form all password protected worksheets are listed In the bottom of the form the required passwords can be entered Test passwords With the Test button you can check whether you entered the correct passwords If you have entered the correct passwords the locks will be replaced by a key in the symbols see the first pair of worksheets Passwo
75. ferences to filter ignore syFiltersFlag constant Syntax expression Filters expression A variable representing a Settings object Remarks If the ENABLED flag is not set all other flags are ignored See Also e Settings Object e Settings Object Members 8 3 4 7 FiltersOption Returns or sets the specified filter option Boolean value Syntax expression FiltersOption Flag expression A variable representing a Settings object Parameters Flag syFiltersFlag Series of Flag s specifying which differences to ignore Remarks If the ENABLED flag is not set all other flags are ignored See Also e Settings Object e Settings Object Members 8 3 4 8 FiltersDescription Returns a descriptive text for the enabled Filters options Read Only String value Syntax expression FiltersDescription expression A variable representing a Settings object 2000 2014 XL Consulting GmbH Swizerland 100 Synkronizer 10 See Also e Settings Object e Settings Object Members 8 3 4 9 FilterPattern Returns or sets a pattern for either Regex of Like filtering of string differences String value Syntax expression FilterPattern expression A variable representing a Settings object Remarks Must begin with either r or See Also e Settings Object e Settings Object Members 8 3 4 10 FilterTolerance Returns or sets a number below which numeric differences will be ignored Double value Syntax expression Filt
76. finition of normal worksheets versus a database Normal worksheets do not contain column headings and no database structure For such worksheets Synkronizer performs a cell by cell comparison As a frequent user of Synkronizer you will soon learn that the data 2000 2014 XL Consulting GmbH Switzerland s Synkronizer 10 4 1 3 3 comparison of a normal worksheets is not as efficient as a database comparison Databases are worksheets that contain data within a database structure The data must be arranged as follows The first row contains unique column names The database contains one or more column s with primary key s unique identification number article number etc A primary key uniquely identifies each data records and facilitates the sorting and synchronizing of your data The primary key does not need to be a single field e g employee number It can be constructed with multiple fields e g family name first name and city to create a unique identifier and avoid duplicate keys records Below paragraphs describe the difference between a duplicate key and duplicate records Duplicate keys Duplicate keys are data records which contain the same primary key Assuming the primary key is formed from the family and first name if you have several customers called John Miller in your database Synkronizer will not know which data records should be compared Make sure you operate with unique primary keys when worki
77. he first worksheets of the files are matched All worksheet pairs are removed from the list Match worksheets manually The worksheets can be matched manually This is done in the header of the worksheet list box Worksheets EEE a ID 1st sheet 2nd sheet Controlling Controlling Select sheets then c Textformulas Array Functions In the row marked with a star there are two drop down boxes see illustration With these fields the required worksheets can be selected The selection has to be confirmed by selecting the checkbox to the right and the pair of worksheets will be included in the list Remove worksheet pairs manually If you do not want to compare all pairs of worksheets you can remove the unwanted pairs with the Delete button As a further option the worksheet couples can be removed by clicking with on the checkbox at the right side see picture below 3 barr barn vie uen A 11 Cantrallina Cantrallina R Once the box is unchecked the worksheet couple disappears from the list No worksheets found 2000 2014 XL Consulting GmbH Switzerland a Synkronizer 10 If no worksheets are available in the drop down lists the worksheets are either protected empty and or hidden In this case you need to change the display mode for worksheets For more detailed information please refer to the chapter Settings Main Form 4 1 3 1 Ranges Worksheets Ba e P 22
78. hlightType gt lt ShowHide gt 0 lt ShowHide gt lt Settings gt lt Files gt lt Filename0 gt C Folder SubFolder File 1 xls lt Filename0 gt lt Filenamel gt C Folder SubFolder File 2 x1s lt Filenamel gt lt Files gt lt Pairs gt lt MatchType gt 1 lt MatchType gt lt MatchInclude gt 2 lt MatchInclude gt lt Pairs gt lt Project gt lt Synkronizer gt For a project where pairs have individual settings the file will look like this MatchType will be set to 0 as soon as you set a pair option such as RangeAddr or DBKeys The pair tags must be sequentially numbered via the ID attribute lt xml version 1 0 standalone yes gt lt Synkronizer gt lt Project ID 1 gt lt Settings gt 2000 2014 XL Consulting GmbH Switzerland Project File e lt CompareType gt 0 lt CompareType gt lt Formats gt 0 lt Formats gt lt Filters gt 0 lt Filters gt lt FilterTolerance gt 0 lt FilterTolerance gt lt FilterPattern gt lt ReportType gt 0 lt ReportType gt lt HighlightType gt 1 lt HighlightType gt lt ShowHide gt 0 lt ShowHide gt lt Settings gt lt Files gt lt Filename0 gt C Folder SubFolder Fi xls lt Filename0 gt lt Filename1 gt C Folder SubFolder File 2 xls lt Filenamel gt lt Files gt lt Pairs gt lt MatchInclude gt 3 lt MatchInclude gt lt MatchType gt 0 lt MatchType gt oO GO
79. ides Za TIEFE SE bue GO SOS SSG 2 gt gt 2 a po a 523 524 526 527 EOO btt bt dt btt bt bt bb rp btt b A 2 a ZO BS SOS SO SOS 3 3 mn TA At one glance Synkronizer clearly displays all differences and you can decide with which version of the worksheet you wish to proceed The colors listed above are the default values you can specify your own color settings see the chapter Settings Colors Cells with Conditional Formattings If Synkronizer finds different cell values or formulas in cells with conditional formatting then the cells can not be marked In this case Synkronizer draws a diagonal line in the corresponding cell 2000 2014 XL Consulting GmbH Switzerland Forms 51 4 1 6 2 Create a Difference Report If desired you can display the result of the comparison in a reader friendly difference report On this report you see the rows or columns deleted and or added and each cell with a different value The difference report displays the values entered in worksheet 1 and worksheet 2 This report is a great help in tracing differences and updating your Excel files Y e 2 DEE A Difference Report _2 0810 2009 20 34 40 3 T a Mad 23 FOM FDM 135 JZ 2 columns columns Soi columns of columns LZ D D 1 columns B G amp G 1 columns e 23 gt AT 31 rows rows E of rows of rows 132 22 22 1 rows e 3j 23 23 1 rows 36 40 60 1 rows
80. ions 4 2 7 System In this area the system will be set S Synkronizer 10 Settings beei Form About Synkronizer Version Compare EEE Synkronizer 10 0 508 Filter KEN TESTW7A CAUNW6K 4YLXQE2 Navigator Colors User Interface Folders stem Folder for projects HUSERPROFILE Documents My Synkronizer USERPROFILE Documents My Synkronizer Reset preferences Clear most recently used lists Workbooks Projects Save preferences SI cancel About Synkronizer In this window the Synkronizer version information is provided Registration Activation Click this button to activate or upgrade the software Folder for projects In this directory the project files are saved or opened 2000 2014 XL Consulting GmbH Switzerland e Synkronizer 10 Clear most recently used lists If you click on these buttons the recently opened files and projects will be deleted 43 Navigator After completion of the comparison process the worksheets are displayed vertically or horizontally and a user friendly form is displayed This form the navigator allows you to navigate through all differences Sanchar l 1 Talnamen Auz MM Ni 2 s e 19 Syrkronizer 10 Navigator 25 1012001 17 rn 0800 26 G1012001_17_00 0003 00 1000 4 bide berez ei 27 G1012001_17_00 0003 07 0600 6 laa la Gz GOI OO 28 G1012001_14_00 0003 01 0909 E m 33 as E El 29 Gt012001_17_00 0003 02
81. istration 19 59 65 Remove 42 107 Remove highlighting 57 Remove highlighting on exit 57 Report 49 ReportType 100 ReportWorkbook 93 Reset 51 87 ResetPairs 108 Results 92 115 116 Review 7 ue Save 51 86 select 42 44 select range 44 Serial Number 19 Settings 47 51 91 95 Setup 7 Sheet 114 SheetName 114 show 35 Show hide 57 Show hide differences 61 ShowHide 101 ShowHideDescription 102 ShowHideOption 102 Sort cell differences 62 Sort records 44 start symbol 7 Sum 120 SumByCode 120 summary 120 summary message 120 switches 18 syCompareType 121 syDatabaseFlag 124 syErrors 125 syFiltersFlag 122 syFormatsFlag 122 syHighlightType 122 syMatchincludeFlag 124 syMatchType 123 symbol 7 SynkID 44 Synkronizer icon 58 syProgressID 124 syReportType 121 syResetType 125 syResultCode 124 syShowHideFlag 123 e d Test passwords 47 text filter 54 TextDisplay 121 toolbar 58 61 Tools 7 transfer 30 35 Transfer cell differences 62 Transfer missing rows 62 trim whitespace 54 2000 2014 XL Consulting GmbH Switzerland tutorial 25 tutorials 25 U UndoHighlight 89 UndoShowHide 89 Unload 86 unlock 47 update 35 Updating 57 upgrade 59 PA values 53 wa 69 70 VBScript Regular Expressions 54 VersionName 84 vertically 58 W Window 105 windows 58 Workbook 104 worksheet 42 worksheets 42 wrapper 69 70 write permission 10 X XML 49 2000 2014 XL Consulting GmbH Switzerland
82. l differences of each file pair Sub Example8 Dim oProj As Synkronizer Project Dim sFile As String Dim aFiles As String Dim i As Integer Dim sFolderOld As String Dim sFolderNew As String Dim sFolderRep As String Dim sFolderLog As String Dim sFileOld As String Dim sFileNew As String Dim sFileRep As String Dim sFileLog As String Dim sWorkbooks 0 To 1 As String Dim n 0 To 1 As Long Dim t0 As Date define folders must end with backslash sFolderOld D Documents Old sFolderNew D Documents New sFolderRep D Documents Reports sFolderLog D Documents Log Dir Dir Debug Assert Len Debug Assert Len Debug Assert Len Debug Assert Len sFolderOld vbDirectory sFolderNew vbDirectory sFolderRep vbDirectory sFolderLog vbDirectory Dir Dir ege Gg e ege tO Timer create log file sFileLog sFolderLog synkronizer log txt Open sFileLog For Output As 1 Print 1 Synkronizer Logfile Print 1 n Print 1 Print 1 Date Format Date YYYY MM DD Print 1 Time amp Format Time hh nn ss Print 1 2000 2014 XL Consulting GmbH Switzerland 79 so Synkronizer 10 Print 1 Filepair read old files i d sFile Dir sFolderoO amp vbTab amp Differences a E MER LS Do While Len sFile ReDim Preserve aFil aFiles i sFil i i 1 sFile Loop Dir
83. lation PLUS a shortcut to a shared license distribution file Only for users companies who purchased a MULTI USER LICENSE KEY This installation method is designed for system administrators in companies which have multi user licenses Workstations need ONETIME access to obtain registration data which is then stored on the local disk During the installation you will be asked to specify TWO directories the INSTALLATION directory where the program files will be installed the DISTRIBUTION directory where the license distribution files are located This MUST be an existing directory Empty license distribution files will be copied here when they don t exist a shortcut to the distribution folder will be placed in the installation folder Scripted Installations the setup executable accepts command line switches for use in scripted installations or batch files The syntax is described in this chapter 2000 2014 XL Consulting GmbH Switzerland Install Uninstall 9 Administrator Rights The user who performs the installation MUST have administrator privileges Network Installation The user who performs the installation MUST have administrator privileges The user who registers and activates the license and or wants to add remove nodes workstations must have WRITE MODIFY permission to the license file on the network share if the workstation is already registered in the file the user for that computer needs READ permission to the license fil
84. lder subfolder ignored for type standard required for type network or type distrib can be included in single or double quotes Set the installation folder DIR a valid file path or URL for the installation folder e g C Program Files x86 Synkronizer default pf32 Synkronizer Synkronizer 10 0 Minimize user interaction SILENT No output or messages VERYSILENT Create a log in the temp directory LOG There are other parameters available which are described in the following link http www jrsoftware org ishelp index php topic setupcmdline Examples Standard Installation SetupSynkronizer 10 x xxx exe TYPE standard silent Network Installation SetupSynkronizer 10 x xxx exe TYPE network FOLDER x my folder Administrative Installation SetupSynkronizer10 x xxx exe TYPE distrib FOLDER x my folder 2 6 Registration When you purchase a license you will receive a registration key for the edition you bought This registration key is used to register and activate the software The activation process is fully automated it only needs an internet connection It can also be done via email if firewall settings prevent the program s direct communication with our server What happens during the activation process The registration key and some hardware codes is sent to our activation server which returns one or two activation codes The activation codes received from the server should match the codes sent and will then
85. mers 2009 Y Missing columns Kentical rows Different rows 3 Duplicates Swap hidden shown items 3 JE da Alb dS E e de lle f Addri Addr2 Formula 1 Formula 2 No differences found for this selection 12 You can see the missing rows only and all columns A edi E 1 Customer Nc Customer E Address Phone Mess ZZ Bis Hyach D Lined Sugan Oban Sivoo mas Frate Oi Truck Contre 2052 505 W7 besieaten m AE DF DA CAS rte ORO MUk UAB m ZE Grurpeca OF Ld ZTEI IEZA NT KIZUN Averss G a Do bebe Coma UA DERE LEVE Gi SZ pa OS GA LR GA Fior Stree SZ pa Vatu Chifrertion BEATO SZ pen Fiesotercho Di Service Drug T245 Gozo Sarr E me Bargin Of Corte Rr TI PRIS Syu kio Steet 5 Tiat OE Servise Los 01832 301 PD Prowencher POTTO E nis Eb DIU IEE ssh EISE pate bantu 5 pm Forty OU Company puma Ah rest 13 When you close the Navigator form you will be asked if the Synkronizer highlighting should be removed or not Press No Note the default response to this dialog can be set in Settings Synkronizer 10 bezal O Undo Synkronizer s Highlighting e me 2000 2014 XL Consulting GmbH Switzerland 4 Forms 4 1 Projects Synkronizer is started as follows Excel 2007 Menu Reviews Synkronizer 10 Excel XP 2003 Tools menu Synkronizer 10 The main form of Synkronizer 10 appears pe E Synkronizer 10 Benchmark xml x Project Help Workbo
86. mpareType Read Only String value Syntax expression CompareDescription expression A variable representing a Settings object See Also e Settings Object e Settings Object Members 8 3 4 3 Formats Returns or sets a series of options specifying which formatting properties to compare syFormatsFlag constant Syntax expression Formats 2000 2014 XL Consulting GmbH Switzerland os Synkronizer 10 8 3 4 4 8 3 4 5 expression A variable representing a Settings object Remarks If the ENABLED flag is not set all other flags are ignored See Also e Settings Object e Settings Object Members FormatsOption Returns or sets the specified formats compare option Boolean value Syntax expression FormatsOption Flag expression A variable representing a Settings object Parameters Flag syFormatsFla Series of Flag s specifying a group of formatting g properties to compare Remarks If the ENABLED flag is not set all other flags are ignored See Also e Settings Object e Settings Object Members FormatsDescription Returns a descriptive text for the enabled Formats options Read Only String value Syntax expression FormatsDescription expression A variable representing a Settings object See Also e Settings Object e Settings Object Members 2000 2014 XL Consulting GmbH Swizerland Object Model Reference 9 8 3 4 6 Filters Returns or sets a series of options specifying which dif
87. mulas values formulas In the columns of the list box various differences are shown The difference types are as follows ll Missing columns Missing rows Duplicate keys only database mode Duplicate records redundant records only database mode Different cell values formulas Different cell formats only displayed by formatting comparison E E E You can view the detailed information for each difference by clicking on the difference your interested in The detailed information will appear in the lower part of the form Description of the icons in the toolbar a Es Re compare worksheets With these buttons you can repeat the comparison the worksheets without closing the Navigator This comes very handy when wanting to update differences and to know whether 2000 2014 XL Consulting GmbH Switzerland 62 Synkronizer 10 4 3 2 or not there are still differences If all worksheets are to be compared the left icon must be locked If this symbol is not locked however only the selected worksheet will be compared With the right icon a new comparison is started EO Highlight remove differences With the left symbol differences are highlighted With the right icon the Synkronizer high lighting s are removed with the original background colors restored GZ e Show hide differences With these two symbols the differences can be shown or hidden To view specific differences proceed as follows 1 Press the arr
88. ng with Synkronizer Duplicate Records Redundant records Synkronizer also checks whether your database contains duplicate records or redundant records These are records where all fields not just the key fields are equal If found these records will be highlighted in gray Duplicate records do not serve any purpose and should be deleted The comparison of databases is a fast The databases may be sorted or altered The inserting and deleting of columns and rows is allowed Synkronizer will recognize all differences between the two files Link data 1 on 1 If you are sure that your worksheets contain no deleted or inserted columns rows you can check the fields Link 1 on 1 for columns rows This overrides the linking algorithms Proceed as follows to link the data 1 on 1 1 Select the worksheet couple containing the desired data 2 Select the button Edit ranges and database options icon to the right or double click on the pair of worksheets which must be edited The option field appears 2000 2014 XL Consulting GmbH Switzerland Worksheets PEA a ID 1st sheet 2nd sheet 1 Sheet Sheetl E Options Edit ranges Enable DB mode V Link Columns 1 on 1 V Link Rows 1 on 1 3 The following checkboxes appear Link Columns 1 on 1 and Link Rows 1 on 1 4 Click on the desired checkboxes and start the comparison 41 4 Settings In the main form the most important information such as fil
89. nnot run macro RibbonOnLoad The macro may not be available in this workbook or all macros may be disabled These errors usually occur when not all Microsoft Office updates are correctly installed and or Microsoft Office is corrupted Please download and install all Microsoft Office updates http office microsoft com officeupdate and test if the problems are solved If not you will have to re install Microsoft Office How to disable hyperlink warning messages These articles describes how to disable warning messages about potentially dangerous hyperlinks in Office 2007 2003 programs Office 2007 http support microsoft com kb 925757 Office 2003 http support microsoft com kb 829072 Problems with Excel 4 0 Macros You receive an error message when you open a workbook that contains XLM macros Excel 2003 http support microsoft com kb 820739 en 2000 2014 XL Consulting GmbH Switzerland Visual Basic for Applications VBA 6 7 Visual Basic for Applications VBA 7 1 Introduction Synkronizer 10 has a new feature to load and save project files which for many 9 5 users means they will no longer need to write their own code For those that do need to process recurring standardized and or complex comparisons synkronizer s functionality can be accessed with Visual Basic for Applications VBA YOU MUST HAVE A LICENSED DEVELOPER EDITION TO ACCESS SYNKRONIZER FUNCTIONS To access the automation functionality of Synkroni
90. o e Results Object e Results Object Members SumByCode Property Returns the number of specified differences found Read Only Long value Syntax expression SumByCode ResultCode MUid expression A variable representing a Results object Parameters ResultCode syResultCode Constant selects group of results to return MUid muMasterUp Optional Leave blank to return combined results dateID See Also e Results Object e Results Object Members SummaryMessage Property Returns a formatted text that can be used in a messagebox Read Only String Syntax expression SummaryMessage expression A variable representing a Results object See Also e Results Object 2000 2014 XL Consulting GmbH Switzerland Object Model Reference 124 e Results Object Members 8 7 3 11 TextDisplay Property Returns the number of specified differences found as text Read Only String value Syntax expression TextDisplay ResultCode expression A variable representing a Results object Parameters ResultCode syResultCode Constant selects group of results to return See Also e Results Object e Results Object Members 8 8 Enumerations 8 8 1 muMasterUpdatelD Constants used to identity the member of a set or pair muMaster Selects the 1st member of the set NOTE value is 0 muUpdate Selects the 2nd member of the set NOTE value is 1 8 8 2 syCompareType Constants used by the CompareType property in Settings syCompareFo The
91. ocessed Return Value none See Also e Project Object e Project Object Members 8 2 3 10 UndoShowHide Method Undoes changes made by DoShowHide 2000 2014 XL Consulting GmbH Switzerland so Synkronizer 10 8 2 3 11 8 2 3 12 Syntax expression UndoShowHide ActivePairOnly expression A variable representing a Project object Parameters ActivePairOnly Boolean Optional When True only the ActivePair is processed Return Value none See Also e Project Object e Project Object Members ProtectionRemove Method Removes protection for pairs that have protected worksheets using the PasswordList Syntax expression ProtectionRemove ActivePairOnly expression A variable representing a Project object Parameters ActivePairOnly Boolean Optional When True only the ActivePair is processed Return Value none See Also e Project Object e Project Object Members ProtectionValidate Method Verifies that protected worksheets in pairs can be unprotected with current PasswordList Syntax expression ProtectionValidate expression A variable representing a Project object 2000 2014 XL Consulting GmbH Swizerland Object Model Reference Return Value Returns a Boolean value See Also e Project Object e Project Object Members 8 2 3 13 ArrangeWindows Method Arranges tiles windows for the active Files Syntax expression ArrangeWindows expression A variable representing a Project object
92. oks Benchmark 1ds EO Benchmark Zan E a Worksheets de 2e 2 22 E ID ist sheet 2nd sheet a E Addresses Addresses zj 3 Number Formats Number Formats Y 5 Errors Errors a 5 8 0123 0123 EA 10 DB OB y 12 Razo e Pazo aa 15 DBL OBL EA 16 DB2 DB2 ZA 17 123345 12345 7 18 Date Date EAI 19 Textformulas Textformulas 7 20 Array Functions Array Functions 7 21 Merged Cells Merged Cells 3 DO Sinise 274 298 Limia 771 270 a SO Actions Highlight Differences 7 Difference Report E SG 2 Laj e 392 JA In this form the main settings are set Further settings are set in the form Settings 4 1 1 Projects A project holds a configuration of files pairs of worksheets and settings filters etc Project files can be saved so you can easily manage your recurring comparisons The following commands are available from the Projects Menu New Clears the main form Settings revert to the defaults specified in settings files and pairs are emptied Open Opens an project file from disk and loads the files pairs and settings 2000 2014 XL Consulting GmbH Switzerland 42 Synkronizer 10 4 1 2 4 1 3 Save Saves current settings as a project including the selection of the Excel files Save as An existing project or current settings can be stored as a new project under a new name Note Important by default passwords are not stored in the project file For more detailed information please study the chapter Passwords
93. on only This manual explains how to use Synkronizer and how to effectively accomplish above tasks In case you have other suggestions to improve Synkronizer let us know and drop us know via the contact menu Best regards Your XL Consulting GmbH Team What s new Synkronizer 10 is a complete overhaul of the previous version In fact it is almost a new software If you are used to older versions of Synkronizer the new interface is an immediate eye catcher But there is a whole lot more 2000 2014 XL Consulting GmbH Switzerland e Synkronizer 10 is a COM add in and no longer a Microsoft Excel add in Projects allow you to save regular comparisons in a project file All your settings choice of files sheets ranges databases etc can be saved to project files Each project consists of one or two workbooks and a series of pairs of worksheets ranges or databases as many as needed Ranges and databases can be set per pair of worksheets individually In previous versions these options could be set only for a single pair of tables e The Navigator toolbar has been replaced with the Navigator form All differences are now depicted onscreen intuitively presented and ready for review and update e When differences are highlighted the original background color can be restored until the form is closed In previous Synkronizer versions the highlighted cells original background colors were permanently lost For developers there ar
94. operties FullName The full path and name of the project file Name The name of the project file ReportWorkbook Report workbook when created Settings Container for all Settings of the project Files Container for the Files workbooks of the project Pairs Container for the Pairs worksheets of the project Results Container for the combined Results of all Pairs ActivePair Pair that is currently active Events Progress Raised during compare execution to indicate running subprocess PairActivate Raised when is Pair is activated PairDeactivate Raised before a Pair is deactivated 8 2 3 Methods 8 2 3 1 Load Method Loads the specified projectfile into memory Syntax expression Load ProjectFilePath expression A variable representing a Project object Parameters ProjectFilePath String Path and file name of the project file File extension is xml Return Value none Remarks If another project is active it will be unloaded first See Also e Project Object e Project Object Members 2000 2014 XL Consulting GmbH Switzerland s Synkronizer 10 8 2 3 2 8 2 3 3 Save Method Saves the project s configuration to the specified path Syntax expression Save ProjectFilePath expression A variable representing a Project object Parameters ProjectFilePath String Path and file name of the project file File extension is xml Return Value none See Also e Project Object e Project Obj
95. or etc and only then compares the actual content If the data type and the content are not completely equal a difference will be reported Sometimes this is too precise for your purpose and you get too many differences e g you don t care about differences like Customer and CUSTOMER or 123 and 123 2000 2014 XL Consulting GmbH Switzerland z Synkronizer 10 Then you can use Filters to instruct Synkronizer to be less precise This page will show you how to do that 1 Download the example files from the webpage http www synkronizer com files examplefiles zip 2 Extract the zip file and copy the Excel files to a folder of your preference 3 Open Excel and start Synkronizer 4 Select the files Master xls and Update xls 5 Select the worksheets TDM1 and TDM2 and start then the comparison process 6 The Navigator form is displayed which determined 500 differences E Synkronizer 10 Navigator EO Project Window Help fe eax fez E 3 sa mna ao oo Worksheet I a 2 1 mmo d Si faz aa AY EEES 23 i Addri Addr2 Formulai Formula 2 S Al Al Name NAME Bi Bi Page PAGE ca Ci Eff Date EFF DATE Di D1 Sequence SEQUENCE El El Value VALUE A2 A2 FFICBillingPayorA FFICBILLINGPAYOR B2 B2 Billing Information BILLING INFORMA c2 C2 01 09 2009 2009 09 01 D2 D2 800 800 E2 E2 6283 21 6283 21491604209 Different content 500 7 As you scan see from the first couple
96. ow Group records 7 Add SynkiD Actions Highlight Ditterences D No Report X E E EZE EEE EO 2 2000 2014 XL Consulting GmbH Swizerland Tutorials 33 8 Close the Synkronizer form and check the field names of the files Their databases have different field names Customer No and Customer Change the caption of the spreadsheet Turnover to Customer No Now you have an identical primary key E EE gt i EZ EES EZ D E EX Phone Addewex derra Febreary March Apri May EGK ATA GL ZEA TOD Ie Amene 7 TH me GO sa ma Gua mm TZEI 175 yack C8 Linvted PIAR D La AA LA 3 tE Urba 2746 IE me 27872 J175 GURA ast Tiran Lid 204 EGZ ET 6143 45 Avene IEE THEO 2233 GES KEA 218260 au 244849 9 Restart Synkronizer and select the database mode You are able to select the primary key Confirm the fieldname Customer No with a check see cursor below a Synkronizer 10 New Project x Project Help Workbooks Master xis E E Update xds E E Worksheets GEO 3 ie ie 29 22 La ID ist sheet 2nd sheet 1 Customers 2009 10 31 Turnover EA Options Edit ranges EI E V Enable DB mode ID Fieldname Rowforheaders 1 Customer No w Select primary key with al E Sort records least 1 fieldname in the E Group records buruko E Add SynkiD Actions Highlight Ditterences y no Report e taj ka 2 92 a A 10 Check sort records and start the
97. ow of the left icon and select the differences you want to be shown hidden 2 Click the plus sign of the left of the symbol the differences will be shown hidden 3 When all differences are to be shown again click the right button again EU 0 Arrange windows With these two button you can change between a horizontal and a vertical view PI Dock Navigator form Normally the navigator form is shown above the Excel worksheets With these buttons you can dock the Navigator form at the left or right of the Excel worksheets Detail Information In the lower part of the form the details are displayed which were selected in the upper part 3 gt 42 4 3 18 Addr1 Addr2 Formula 1 Formula 2 be E3 F3 149 6 148 6 Z E19 F19 83 85 E24 F24 138 138 5 E31 F31 138 138 5 C41 0008 00 Index D151 E151 0400 1000 E151 F151 68 5 40 F151 G151 F R H151 H151 A PW 151 151 n FDM Different content 79 With that list box you can transfer update or delete unwanted differences The following buttons are available 3 3 Transfer missing rows from 1st to 2nd worksheet With these buttons you can transfer the missing rows from the 1st to the 2nd worksheet from left to right Either all or only the selected row will be transferred Once the row has been transferred it is shown in red These commands are also available in different columns E EO Transfer missing rows from 2nd to 1st worksheet 2000 2014 XL Consulting
98. passwords needed to access protected sheets Events Methods Add Method Adds a pair to the Pairs collection Syntax expression Add SheetName0 SheetName1 expression A variable representing a Pairs object Parameters SheetNameO String A name or index of a worksheet must exist in Master SheetNamel String A name or index of a worksheet must exist in Update Return Value Returns a Pair object 2000 2014 XL Consulting GmbH Switzerland Object Model Reference 107 See Also e Pairs Object e Pairs Object Members 8 5 3 2 AddMatched Method Creates Pairs for all matched worksheets Syntax expression AddMatched expression A variable representing a Pairs object Return Value none Remarks Matching depends on MatchType and MatchInclude See Also e Pairs Object e Pairs Object Members 8 5 3 3 Remove Method Removes specified Pair from the Collection Syntax expression Remove Index expression A variable representing a Pairs object Parameters Index Variant A valid ID or Index for the Pair Return Value none See Also 2000 2014 XL Consulting GmbH Swizerland 108 Synkronizer 10 8 5 3 4 8 5 4 8 5 4 1 e Pairs Object e Pairs Object Members ResetPairs Method Resets specified objects and properties from Pairs Syntax expression ResetPairs eReset expression A variable representing a Pairs object Parameters eReset syResetType Return Value none Se
99. r 10 Navigator Project Window Help Worksheet il 1 TMOL aja ele Addri Addr2 Formula 1 Formula 2 AL Al Name NAME Bi Bi Page PAGE a Ci Eff Date EFF DATE Di D1 Sequence SEQUENCE El El Value VALUE A2 A2 FFICBillingPayorA FFICBILLINGPAYOR B2 B2 Billing Information BILLING INFORMA c2 C2 01 09 2009 2009 09 01 D2 D2 800 800 E2 E2 6283 21 6283 21491604209 Different content 500 11 We now have 396 differences left ES Synkronizer 10 Navigator Project Window Help 38 sa Ja Bo oo Worksheet I 1 DO aoe GO Addri Addr2 Formulal Formula 2 B2 B2 Billing Information BILLING INFORMA c2 C2 01 09 2009 2009 09 01 D2 D2 800 800 E2 E2 6283 21 6283 21491604209 B3 B3 Commissions COMMISSIONS G C3 05 01 2010 2010 01 05 D3 D3 600 600 E3 E3 743955 94 743955 938735648 B4 B4 Agency AGENCY c4 C4 15 11 2009 2009 11 15 Different content 396 12 Closer inspection will show that the differences in Column B are caused by trailing spaces in the Update file Repeat the steps 7 10 and add a check next to the filter option Trim Whitespace You should now have 297 differences left 2000 2014 XL Consulting GmbH Swizerland Tutorials 29 Synkronizer 10 Navigator Project Window Help IE SIE ZIE 80 00 J Worksheet uu aa a 1 TMOL e BEZ l Algae ae Addri Addr2 Formulai Formula 2 ea c2 C201
100. rds in projects By default passwords are not saved in the project file If you do want to store passwords in your projects you have to change a registry setting Note that passwords are not stored in readily legible form but the obscuring routine is not difficult to reverse engineer If you want the convenience of storing passwords with your projects you have to open the registry and select the following key HKEY CURRENT USER Software Synkronizer Software Synkronizer 10 0 Select the entry ProjectsPasswords and change the value to 1 The passwords will now be stored and loaded when you save or open a project file Problems with password protected worksheets Protected worksheets can be compared without a password and without problems EXCEPT when they contain cells that have the Hidden checkbox on the Protection tab turned on In this case Synkronizer MAY return inaccurate results because not all cell information can be read correctly With the unlocking of these worksheets potential problems can be avoided Also note that the Navigator may not be able to highlight select or update cells or ranges if the sheets 2000 2014 XL Consulting GmbH Swizerland are protected and password is not provided 4 1 6 Actions If you have set all comparison options you can determine what actions you want Synkronizer to perform The following two buttons are available Actions No Highlighting y No Report x L la ERE
101. riable representing a Pair object See Also e Pair Object e Pair Object Members 8 6 3 9 ID Property Returns the key of the Pair object in the Pairs collection Read Only String value Syntax expression ID expression A variable representing a Pair object 2000 2014 XL Consulting GmbH Switzerland ne Synkronizer 10 8 6 3 10 8 7 8 7 1 8 7 2 See Also e Pair Object e Pair Object Members Index Property Returns the index of the Pair object in the Pairs collection Read Only Long value Syntax expression Index expression A variable representing a Pair object See Also e Pair Object e Pair Object Members Results Object Results Object Container for the number of differences found Remarks The Project returns the combined results Each Pair returns individual results See Also e Results Object e Results Object Members Results Object Members Methods Properties MissingTabs Returns the number of Missing Sheets auto matching only MissingCols Returns the number of Missing Columns or Fields MissingRows Returns the number of Missing Rows or Records 2000 2014 XL Consulting GmbH Swizerland 8 7 3 8 7 3 1 8 7 3 2 Object Model Reference 117 DuplicateKeys Returns the number of Duplicate Keys database only DuplicateRecords Returns the number of Duplicate Records database only DifferentContent Returns the number of Cells with Different Content DifferentFormats Returns th
102. rlioop all old For i 1 To SFO gt 0 les 1 To i files UBound aFiles dero d amp aFiles i sFileOld sFileNew sFileRep sFileRep sFol sFol check if new is If Len Dir sFileNew Set oProj With oProj With Settings CompareType Formats 0 Filters 0 HighlightType 0 ShowHide ReportType End With Files Load sFileOld With Pairs MatchInclude MatchType AddMatched nd With E Execute If n 1 n 1 Else n 0 n 0 End If lderNew aFi lderRep Left sFil Results Sum If Len Dir sFil ReportWorkbook C ReportWorkbook C les i fference Report InStrRev sFileRep Di leRep amp aFiles 1 wow 1 amp AU xlsx there gt 0 Then SynkProject syCompareFormulas syHighlight syReportStandard sFileNew 0 syMatchFirstByName a Then leRep lose True gt 0 Then Kill sFileRep sFileRep lose False 2000 2014 XL Consulting GmbH Switzerland Visual Basic for Applications VBA at Print 1 aFiles i amp vbTab amp Results Sum store workbook names sWorkbooks 0 Files Workbook 0 Name sWorkbooks 1 Files Workbookx 1 Name close project Unload CloseFiles Fals End With Set oProj Nothing save and close workbooks With Application DisplayAlerts False Workbooks sWorkbooks 0 Close SaveChanges Tru Workbooks sWork
103. ry key Customer No in the list box Fieldnames 2000 2014 XL Consulting GmbH Switzerland 3 Synkronizer 10 Add the primary key with a check see cursor For more information about the behaviour of databases and primary keys please read chapter General information about databases 1 Customers 2009 10 31 Options Edit ranges V Enable DB mode Td Synkronizer 10 New Project Es Project Help Workbooks Master xis E A Update ds E EO Worksheets de ie 2 22 ID ist sheet 2nd sheet Customers 2009 11 30 F ID Fieldname Rowfor headers 1 Customer No w Select primary key with at E Sort records least 1 fieldname in the T Group records opinen Add SynkID Actions I Highlight Difterences y tio Report E 3 E de ie 29 22 ID Ist sheet 1 Customers 2009 10 31 Options Edit ranges V Enable DE mode Row for headers 1 Sort records V Group records Add SynkID Actions Synkronizer 10 New Project Project Help Workbooks Masterxls EI E Update xts E EO Worksheets 2nd sheet Customers 2009 11 30 wi ID Fieldname Customer No F Highlight Ditterences v no Report hd Lajla 2 a Start the comparison see cursor above 2000 2014 XL Consulting GmbH Swizerland Tutorials 39 9 The Navigator is now displayed As you can see Synkronizer found 5 different ro
104. se Excel Start RegEdit exe Select the following folder Excel XP HKEY_CURRENT_USER Software Microsoft Office 10 0 Excel Resiliency Excel 2003 HKEY_CURRENT_USER Software Microsoft Office 11 0 Excel Resiliency 4 If there is a key there called Disableditems then remove the entire key or the items it contains WN FR You could create a Registry file reg to do this 1 Start notepad or any text editor 2 Cut and Paste the block of code below 3 Save the file on your desktop with a REG file extension 4 Double click the saved file you must have administor rights due to the changes under HKLM Windows Registry Editor Version 5 00 REMOVE ALL DISABLED ITEMS HKEY_ CURRENT USER Software Microsoft Office 10 0 Excel Resiliency Disableditems HKEY CURRENT USER Software Microsoft Office 11 0 Excel Resiliency Disableditems HKEY_ CURRENT USER Software Microsoft Office 12 0 Excel Resiliency Disableditems HKEY_ CURRENT USER Software Microsoft Office 14 0 Excel Resiliency Disableditems REMOVE THE ADDIN LOAD COMMAND FROM HKCU HKEY CURRENT USER Software Microsoft Office Excel Addins Synkronizer Connect ADD THE ADDIN LOAD COMMANDS TO HKLM for 32bit windows HKEY LOCAL MACHINE SOFTWARE Microsoft Office Excel Addins Synkronizer Connect FriendlyName Synkronizer 10 0 Description Tool for finding and man
105. sheets are added yName syMatchAllBy All included sheets are matched by Name Name syMatchAllBy All included sheets are matched by Index Index 2000 2014 XL Consulting GmbH Swizerland 12 Synkronizer 10 8 8 9 8 8 10 8 8 11 8 8 12 syMatchIncludeFlag Flags specifying a group of sheets to include during Matching syMatchInclu Empty sheets will be included by AddMatched deEmpty syMatchinclu Protected sheets will be included by AddMatched deProtected syMatchInclu Hidden sheets will be included by AddMatched deHidden syDatabaseFlag Flags for pair s DBoptions syDatabaseSo Records are sorted on fields in primary key before comparing rt syDatabaseGr Records are grouped by Result after comparing oup syDatabaseA Adds an ID in the last column of the database to aid sorting and filtering ddID syResultCode Constants used by the SumByCode property in Results syResultTabs Missing sheets syResultCols Missing columns syResultRows Missing rows syResultKeys Duplicate Keys syResultDups Duplicate Records syResultCont Different Content syResultFmts Different Formats syProgressID Constants used by Progress events to identify current ly executing sub process syProgressLin Linking columns kCols syProgressLin Creating row indexes 2000 2014 XL Consulting GmbH Swizerland Object Model Reference 125 kData syProgressLin Linking row indexes kRows syProgressCo Comparing cells mpC
106. stant Syntax expression MatchInclude expression A variable representing a Pairs object See Also e Pairs Object e Pairs Object Members MatchType Property Returns or sets a constant specifying how worksheets will be matched by AddMatched method syMatchType constant Syntax expression MatchType expression A variable representing a Pairs object See Also e Pairs Object e Pairs Object Members Pair Object Pair Object Container for a pair of worksheets to compare Remarks Specify a Range Normally the entire data range of the sheet is used but you set the range using the RangeAddr property Compare as Database You can compare the sheets as databases when your data is organized in a tabular layout with descriptive headers above each column rows can be uniquely identified by a primary key You must set the primary key using the DBKeys property You can specify the starting row of the database with the DBRow property 2000 2014 XL Consulting GmbH Switzerland Object Model Reference D When you want the records sorted or the results grouped use DBOptions See Also e Pair Object e Pair Object Members 8 6 2 Pair Object Members Methods Properties DBRow Returns or sets the index for the row which contains the headers DBKeys Returns or sets a semicolon delimited list of Column indices used as primary key for Database DBOptions Returns or sets options for sorting or grouping database records
107. ster FileNamel String Full path for 2nd file also called Update Return Value none See Also e Files Object e Files Object Members 2000 2014 XL Consulting GmbH Switzerland 104 Synkronizer 10 8 4 4 8 4 4 1 8 4 4 2 8 4 4 3 Properties FileName Property Returns the full path and filename of either of the Files Read Only String value Syntax expression FileName MUid expression A variable representing a Files object Parameters MUIid muMasterUp Selects either member of the Files datelD See Also e Files Object e Files Object Members IsValid Property Returns True if both Files are loaded and can be compared Read Only Boolean value Syntax expression IsValid expression A variable representing a Files object See Also e Files Object e Files Object Members Workbook Property Returns the workbook object of either of the files Read Only Workbook object Syntax expression Workbook MUid expression A variable representing a Files object Parameters MUIid muMasterUp Selects either member of the Files datelD 2000 2014 XL Consulting GmbH Switzerland Object Model Reference 105 See Also e Files Object e Files Object Members 8 4 4 4 Window Property Returns the window object of either of the files Read Only Window object Syntax expression Window MUid expression A variable representing a Files object Parameters MUIid muMasterUp Selects either member
108. sults ala ActivePair Propecia ds ie EAEE 92 F lName Proper y noe nn ii Ri aan AA AAA dd ain Gan ch Ee 93 2000 2014 XL Consulting GmbH Switzerland 4 Synkronizer 10 Name dela 93 ReportWorkbook Sdad AAA 93 Events patata a ka aa aa aea aen 94 Progress ESE urr rga a cca daaa dau Ed da dre 94 Fait Activ ate EAEAN 94 PairDeactivate EAE 94 e Settings OD e Clee vic Era eak tea EEE EEE EEN aa aak teka ad 95 SO TLIN GS OD je Cbs AAA 95 Settings Object Members isi ecccsceccsscecusscseccsseavccccsncvuccecsssvvcvscussucescavenscocvescavecedeesvscrssuece sterecascveccedsseursvscrssteessesirs 95 LDL EAEE DefaultsLoad ZO D fa lts Savezi taia ida Ek iaiki tadura ark DefaultsResets EAE aT A E A Ea E a UE EEE Ee Prope EU EE aaa ea a a pagen zterketa Compare ly AAE CompareDescription Formats Biduze aA Formats Des gae EAEE SUSA Fillers OPIO aia TUEN Ea E i TE ites E E EEE Filters Des Cl muta a E aan o A kr aeta EA lA BAA SUSA AENA REPO NASA Highlight Mu AEE HighlightDESCTIPION AAA SHOW ela Show HideOption Show HIGEDES CriptlOn arrarua ora AAE a AAA A DAS 102 A Files OE aaa EE ae Ea bae ark ae FIGS DS E reka a eka a areka a Files Object Members LD lala EA Load A gilea BAA EOS EIS E ereta aa a eae aaa eeta aa FileName PrOpe rly rrara sviseisnccesvecesusaviveavevesevstacwadtave dais IS Valli delega ALARE Workbook PODERE a a ia aaa ara arei Window A 3 Pars OA E E E E a E E a E E Eee AS
109. t the worksheet couple containing the databases 2 Select the button Edit ranges and database options icon to the right or double click on the pair of worksheets which must be edited The option field appears 3 Click Enable DB mode 4 Select with the field Row for headers the row on which there are the field names in the database normally row 1 5 Build the primary key using the DropDown Fieldname Confirm the selection by clicking on the checkbox Important The field names must have identical names otherwise the database can not be linked 6 The database icon appears in the worksheets list In addition the below options can be selected Sort records The data is sorted by the primary key Group records The data is sorted by the primary key but grouped First the linked records are listed then the added deleted rows and last but not least the duplicate keys records Add SynkID To the right of the databases a new column entitled SynkID will be added This column contains the various difference types You can then sort and or filter auto filter the databases by the differences types The following captions are written in the SynkID column 0 Key Duplicate key 0 Dup Duplicate record redundant record 1 Rec Missing row 2 Val Different cell value s formula s 2 Fmt Different cell formatting s 4 1 3 2 1 General information about databases Prior to describing the types of differences we want to give a brief de
110. tcut Select menu Properties Select tab Shortcut Select the button Advanced Check Run As Administrator Click OK twice to close the dialog e Now double click the shortcut You should get a warning from User Account control You must accept e Navigate to your installation directory and use regsvr32 to register the files Cy cd program files synkronizer or program files x86 synkronizer cd synkronizer 10 0 regsvr32 synk10 dll regsvr32 synklic dll regsvr32 Codejock Controls ocx regsvr32 Codejock ReportControl ocx 2000 2014 XL Consulting GmbH Switzerland 10 Synkronizer 10 2 2 2 3 Uninstall Uninstall Synkronizer 10 is a COM Addin It takes very few resources and time during startup so you can leave it enabled and available for direct use It depends on several other components and these must also be properly un installed So please do not simply delete the folder Should you decide to remove Synkronizer from your computer then proceed as follows Uninstall via Control Panel 1 In Windows open the Control Panel 2 Select Programs and Features Windows 7 Vista or Software Windows XP 3 Select Synkronizer 10 and click on Uninstall Disable Synkronizer in Excel You can also disable Excel loading the Synkronizer Addin during startup The Addin requires very little time or resources so this is not recommended However it s your choice Excel 2010 2007 1 Choose Start Excel Options 2
111. ter the same answer The secret answer must be at least 5 characters long The following characters are allowed A Z and 0 9 4 The registration data is now sent to the activation server and the software is activated If you should have no internet connection you ll need to activate manually Please read the chapter Manual Activation If you have any problems with the registration activation process please read the chapter Problems with registration 2 6 1 Manual Activation If no internet connection is available the software needs to be activated manually via the Synkronizer 2000 2014 XL Consulting GmbH Switzerland 22 Synkronizer 10 Activation Server This chapter explains how you can manually activate Synkronizer The registrations steps 1 to 3 are explained in the chapter Registration 4 The Manual Activation screen appears which contains the datrastring to be submitted to the activation server M Synkronizer License ACT Sopp ee et tT 20001 00000000 0000 0000 0000 001FDOSS1DCB TELEBEFF Gigabyte Technology Co Ltd EP35 DS3 28444E0D L MILLER The internet connection to the server could not be established Please click this link and paste the datastring above in the field on the webpage The server will generate an activation key which must be entered in the field below Enter Register 5 You ll need now to copy the datastring to a text document e g Notepad Open for
112. tion Returns or sets the specified ShowHide option Boolean value Syntax expression ShowHideOption Flag expression A variable representing a Settings object Parameters Flag syShowHideFl Series of Flag s specifying the group of rows or columns ag Remarks If the ENABLED flag is not set all other flags are ignored See Also e Settings Object e Settings Object Members ShowHide Description Returns a descriptive text for the enabled ShowHide options Read Only String value Syntax expression ShowHideDescription expression A variable representing a Settings object See Also e Settings Object e Settings Object Members Files Object Files Object Container for the two Files workbooks to compare See Also 2000 2014 XL Consulting GmbH Switzerland Object Model Reference m e Files Object e Files Object Members 8 4 2 Files Object Members Methods Load Loads the two files from disk Properties FileName Returns the full path and filename of either of the Files IsValid Returns True if both Files are loaded and can be compared Workbook Returns the workbook object of either of the files Window Returns the window object of either of the files Events 8 4 3 Methods 8 4 3 1 Load Method Loads the two files from disk Syntax expression Load FileNameo0 FileName1 expression A variable representing a Files object Parameters FileNameO String Full path for 1st file also called Ma
113. to access the application object with a Free or Professional edition Nothing is returned and a messagebox is displayed Examples Examples Option Explicit Const FILEO As String D Documents TestFi sx d Const FILE As String D Documents TestFile2 sx Function SynkApplication As Synkronizer Application Wrapper to retrieve the Application object With Excel Application COMAddIns Synkronizer Connect Ensure the addin is connected If Not Connect Then Connect True Return the application Set SynkApplication Object Application End With Display progress in Excel s status bar SynkApplication DisplayStatus True End Function Function SynkProject As Synkronizer Project Wrapper to retrieve the ActiveProject If Not SynkApplication Is Nothing Then Set SynkProject SynkApplication ActiveProject End If End Function Example 1 Compare sheets Addresses highlight differences Public Sub Examplel Dim oProj As Synkronizer Project Set oProj SynkProject If oProj Is Nothing Then Exit Sub With oProj 2000 2014 XL Consulting GmbH Switzerland Visual Basic for Applications VBA 71 Files Load FILEO FILE1 With Pairs Add Addresses Addresses End With With Settings CompareType syCompareFormulas Formats 0 Filters 0 HighlightType syHighlight ReportType syReportNone ShowHide sy
114. w Hide tool 1 Download the example files from the webpage http www synkronizer com files examplefiles zip 2 Extract the zip file and copy the Excel files to a folder of your preference 3 Open Excel and start Synkronizer 4 Select the files Master xls and Update xls f Synkronizer 10 New Project meses Project Help Workbooks Maizak a HER gt Worksheets ae e 22 ID ist sheet 2nd sheet Select sheets then click to add to the list SE A dE A 5 In the section Worksheets select the worksheets Customers 2009 10 31 and Customers 2009 11 30 Now click then on the checkbox see cursor 2000 2014 XL Consulting GmbH Switzerland Tutorials Td Synkronizer 10 New Project EO Project Help Workbooks Master xls EI EO Update xis es Worksheets eleiz 22 D ID ist sheet 2nd sheet cononer 505 cone 20 gt 0 Select sheets then click to add to the list The worksheets are added to the worksheet list 6 In order to activate the database modus you need to double click on the worksheets or click on the database symbol see cursor f Synkronizer 10 New Project biei Project Help Workbooks Masters 2 E Updates DOIE Worksheets i 2 E E ist sheet 2nd sheet Actions Highlight Ditterences No Report y GOIE 2 dE A The options frame is now open 7 Click now Enable DB mode and select the prima
115. ws in the Master file and 7 different rows in the Update file see cursor caption 5 7 f Synkronizer 10 Navigator Project Window Help ala ea asja ao m a e Worksheet 3 aa Bee Addri Addr2 Formula 1 Formula 2 lt 2 49 49 1875 50 50 3215 51 51 4225 52 52 7439 53 53 7526 49 49 1629 50 50 2488 51 51 5472 52 52 7428 53 53 7817 Missing rows 5 7 10 If you like you can now update the worksheets transfer differences from left to right or vice versa For this just select the desired differences and click on the update buttons see cursor below E Synkronizer 10 Navigator Project Window Help as BIEI ana Ao ola Worksheet I 3 143 1 Customers 2009 HE a S398 AER Addri Addr2 Formula 1 Formula 2 49 49 1875 50 50 3215 51 51 4225 52 52 7439 53 53 7526 50 50 2488 51 51 5472 52 52 7428 53 53 7817 a Missing rows 5 7 11 If you prefer to show the missing rows only click with the cursor on the arrow of the Show Hide 2000 2014 XL Consulting GmbH Switzerland 4 Synkronizer 10 symbol and select all columns and Missing rows Click then on the Show Hide button see cursor below The rows will be hidden and or displayed now Synkronizer 10 Navigator Project Window Help 59088 9 80 EE Y Bentical columns Worksheet Y Different columns 4 GO 1 Custo
116. y active DisplayStatus Determines if Progress is displayed on status bar during execution Customer Returns the name of the licensed customer EditionName Returns the name of the licensed synkronizer edition VersionName Returns the version and build number of the application Events Properties ActiveProject Property Project that is currently active Read Only Syntax expression ActiveProject expression A variable representing a Application object 2000 2014 XL Consulting GmbH Swizerland Object Model Reference s See Also e Application Object e Application Object Members 8 1 3 2 DisplayStatus Property Returns or sets a boolean that determines if progress is displayed in Excel s status bar during execution Syntax expression DisplayStatus expression A variable representing a Application object See Also e Application Object e Application Object Members 8 1 3 3 Customer Property Returns the name of the licensed customer Read Only Syntax expression Customer expression A variable representing a Application object See Also e Application Object e Application Object Members 8 1 3 4 EditionName Property Returns the name of the licensed synkronizer edition Read Only Syntax expression EditionName expression A variable representing a Application object See Also e Application Object e Application Object Members 2000 2014 XL Consulting GmbH Switzerland a Synkronizer 10
117. zer you must obtain a reference to the Application Object of the ComAddin Then you will need the Application ActiveProject to start working We ve provided 2 simple wrapper functions to simplify you work which we ll also use in following examples Ensure Synkronizer 10 Developer Edition is installed Start Excel Open a new workbook Open the VB Editor Keyboard Shortcut Alt F11 Select your workbook in the Project Explorer window Select menu Tools References scroll to Synkronizer 10 0 Object Library check it and press ok Select menu Insert Module Copy the following code o pl GO MO b wn FP Function SynkApplication As Synkronizer Application Wrapper to retrieve the Application object With Excel Application COMAddIns Synkronizer Connect Ensure the addin is connected If Not Connect Then Connect True Set SynkApplication Object Application End With Display progress in Excel s status bar SynkApplication DisplayStatus True End Function Function SynkProject As Synkronizer Project Wrapper to retrieve the ActiveProject If Not SynkApplication Is Nothing Then Set SynkProject SynkApplication ActiveProject End If End Function Note 2000 2014 XL Consulting GmbH Switzerland Synkronizer 10 7 2 e The programmatic identifier used for the ComAddin is synkronizer connect e The Synkronizer Application is a child of the ComAddins progid Object e When you attempt

- Synkronizer Excel Compare

Contents

Download Pdf Manuals

Related Search

Related Contents