Synkronizer XL 8.0 User Manual
Contents
1. 4 2 1 Show hide differences With the menu commands 1 to 5 you can show or hide the desired differences dynamically D e pending on whether the menu commands have a checkmark on the left differences are shown or hidden With a checkmark differences are shown without one they are hidden The following is an explanation of the different menu commands 1 2 3 4 5 Unmatched columns If this menu option is selected deleted only in Master file and added only in Update file colums are shown Unmatched rows With this option deleted and or added rows are shown Duplicate indexes With a database comparison duplicates can occur According to whether this menu option is marked or not duplicates are visible or not Different values With this selection rows with different cell values or formulas or comments are shown Unchanged rows Unchanged rows contain the same data in both files These rows can be shown or not if you select this menu April 2004 Page 18 TT User Manual Synkronizer XL 8 0 SYNKRONIZER U pdate M aster U pdate U pdate 4 2 2 Update Master In this menu 6 you can update the Master file table left hand side by using the following functions 8 9 10 11 Added columns the added columns of the file Update can be added to the Master file The columns will be transferred from the right to the left worksheet Added rows the added ro2. After the comparison process the worksheets are tiled vertically and a user friendly tool bar ap pears E Master Budget 2004 08 xls Cell B90 EX Update Budget 2004 12 xls Cell B90 aA Cn ass Fo 85 1978 i aan aan wnan neanrn ans or 5986 314169 369 089 86 1979 40729 365 309 404 054 87 1980 gt dl of 55 gt Show Updates Gy Mle 73 835 403903 476 618 88 1981 vovo oro 78 976 469 097 543 053 89 1982 i i 127 121 89 1982 617766 745755 127 989 474 299 594 351 808 385 207 822 453 242 661 277 666 486 851 874 185 388 500 411 686 061 E 92 734088 946 423 547918 769 615 93 1986 j p 23 769 215 990 460 568 986 806 962 34 1987 004 149 16 854 353 11004122 149 769 640951 810 290 95 1988 064 155 19 909 303 1 064 489 155 187 667 812 861798 96 1989 143 152 Y 20 991190 1 143 683 152 493 727 525 932772 97 1990 253 221 j 27 f 1 031 969 1 2531198 221 229 750314 1 028133 38 1991 324 269 082 32 1 055 041 1 324 403 269 361 761157 1 082716 39 1992 381 290 129 1 091 279 1 381 684 290 404 788 853 1 129 345 100 1993 409 255 142 0 1154401 1 409 512 25510 842 467 1142925 101 1994 461 203 182 251 1 258 627 1 461 902 203 275 923 601 1 182 530 102 1995 515 164 i 14 351 830 1 515 837 164 007 1 000751 1 227173 1 453 062 1 560 572 107 510 1 085 5
3. Optional sRangeOld As String _ Optional sRangeNew As String _ Optional sKeyFields As String _ Optional iCompareType As Integer _ Optional iActionFlags As Integer _ Optional sReportFile As String As Variant 5 2 Retum Value The return value of the Synkronizer function is presented as follows False Synkronizer could not interpret one or more of the arguments or errors during processing os Synkronizer found no differences and returns an empty string Character sequence Synkronizer detected differences and returns the differences in a charac ter sequence whereby the individual differences are separated by commas and semicolons Example Synkronizer returns the following character sequence new rows 5 different cell values 14 This means that Synkronizer could detect 5 new lines and 14 different cell values April 2004 Page 20 User Manual Synkronizer XL 8 0 SYNKRONIZER the ultimate excel compare tool A rguments 5 3 Arguments The function contains the following arguments sFileO ld sFileN ew sSheetO Id sSheetN ew sRangeO Id aRangeN ew sK eyFields iCompareType iA ctionFlags File incl Path of the 1st file Master You can use Excel text and odc files Access databases must be entered in an odc file File incl Path of the 24 file Update Worksheet name or number of the 1 file You can enter the following values 1 the first worksheet of the 1st file will b
4. commands 4 5 7 8 or jump directly to a particular difference 6 Total differences This describes the total number of differences found between the two files Update Update With this command the differences are transferred from the Master file left to the Update file right This overwrites the change with the original data Note only the highlighted difference is updated Update Master With this command the differences are transferred from the Update file right to the Master file left This overwrites the original data with the updated data Note only the highlighted difference is updated Show Update If you click on this switching surface a menu appears with which you can show hide the data and update them Remove background color By using this command the background color of the differences will be deleted All other background colors remain unchanged Close tool bar With this command the tool bar will be closed April 2004 Page 17 User Manual Synkronizer XL 8 0 SYNKRONIZER Show Update Show hide differences 4 2 Show Update With this menu you can show or hide differences The show menu contains the following func tions Which data should be displayed Unmatched columns 1 Unmatched rows 2 3 Different values 4 Identical cells 5 Update Master 1st 6 i Update Update 2nd T Added rows 9 Different values 10 Formats 11
5. essssscssssessesscsccsscssssessecseesscascnsssesseeseeaecnesseeseeaeessenseaceaeeeesnesneees 9 2 2 1 Remove original background COLOUST cciscscnsscevucetasstesssnaisayitacanascadepiaesnapiicrsibnhssonndesierede 9 2 2 2 Highlight differences seessisressressssssssssssssnnssnsnsesnsnnsssnsenstsssnsnstessst 9 2 2 3 Which data should be displayed ssssssssssssesssssssssssssssssssssssssssssssssssssssssssrssses 9 Zo Register COME Language ssmeinonsiaerinisnnini nonea a 11 2 3 1 Color Settings ss sssisessiisserrsssrssssruserisssnnsisssnyessistveresinsovevsisereiestonsrsssssssdavoti sisson naasst t 11 2 3 2 Language Settings seccescsecsssssessessssecscsssssecsssssecsssssecsssssscsecsscesscsscesecsssenscsassnsssssseesssees 11 K ACON a E E R 13 3 1 Highlighter cosa stn cha oa ct oaemnass adnate acpranedab a a 13 3 2 Create a Difference RepOTbmnusinsnionsnnsnnnunsnienniinininainnonin i 14 33 Navigator Fool al srarniissnnnesninnnnann na inan an nR an 15 SA Alof the ahoYe essi sentais eani aa E E EEEE ai Daet arei iak 15 4 Navigator Toolbar s sssssssesssssssssssseesesssssssssessesstereerensnssnsssestterensessnnnsssenstetterennnsssssserstereesensnnssssss 16 41 Tool Ba eecerennoarraanrin iinn A ERA ERORAR 16 4 2 Show Update ou ccecseccssssscsssssscssssssessssssssesssesssssscsusssssseessesseessessesssssseessesseeaeeescsaeesscsasensessecs 18 4 2 1 Show hide differencess c ccssssssessssssessseeseesssssecssssssesssssses
6. vbOKOnly vbInformation End Select Exit Sub Err_Message MsgBox Err Description vbOKOnly vbExclamation End Sub April 2004 Page 23 User Manual Synkronizer XL 8 0 SYNKRONIZER the ultimate excel compare tool On Error G varSynk Select Cas Case F Ms Ms Case Case E Ms End Select Exit Sub Err_Message End Sub 5 5 2 Example 2 This example highlights the differences in all worksheets of the two files Public Sub Example_2 Dim varSynk As Variant oTo Err_Message Synkronizer sFileOld C Documents FileOld xls sFileNew C Documents FileNew xls sSheetOld sSheetNew e varSynk alse gBox Error vbOKOnly vbExclamation Ww gBox No differences noted vbOKOnly vbInformation lse gBox Synkronizer found differences amp Chr 10 amp Chr 10 amp varSynk vbOKOnly vbInformation MsgBox Err Description vbOKOnly vbExclamation April 2004 Page 24 User Manual Synkronizer XL 8 0 SYNKRONIZER 5 5 3 Example 3 This procedure compares the first worksheet of both Excel files and creates a difference re port called Report xls Public Sub Example_3 Dim varSynk As Variant On Error GoTo Err_Message varSynk Synkronizer sFileOld C Documents FileOld xls sFileNew C Documents FileNew xls sSheetOld 1 sSheetNew 1 iActionFlags 2 _ sReportFile C Documents Report xls Select Case varSyn
7. 1 601 282 21 990 1 187 302 1 290 656 391 990 310 626 81 364 1 721 798 1 652 619 69179 1 305 999 1 336 015 415 799 316 604 99195 1 827 454 1 701 932 125 522 1 382 986 1 381 154 444 468 320778 123 690 2 025 218 1 788 826 236 392 1 544 634 1 458 061 480 584 330765 149 319 1 863 926 507 519 109 2002 estimate 1 946 136 2 052 320 106 84 1 428 938 517198 110 2003 estimate 111 2004 estimate 80 170 545 343 13741 573 472 112 2005 estimate 61139 608193 113 2006 estimate 86196 633 664 114 2007 estimate 103 970 665 299 116 500 thousand or less 117 Note Budget figures prior to 1933 are based on the Administrative Budget concepts rather than the Unified Budget Differences are highlighted with the following colors e orange for different cell entries e lightblue for deleted columns and rows e green for added and columns and rows e lavender for duplicate indexes 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 as de scribed in section 2 3 Register Color Language Versions The action Highlight Differences can be used with all versions of Synkronizer April 2004 Page 13 the ultimate excel compare tool User Manual Synkronizer XL 8 0 SYNKRONIZER Create a D ifference R eport w V ersion
8. 81 1 055 041 1 324 403 269 361 761 157 1 082 716 f 1 055 041 1 324 403 269 361 761157 1 082 716 321 55 1 091 279 1 381 884 290 404 788 853 1 129 345 1 091 279 1 381 684 290 404 788 853 1 129 345 340 49 1164 401 1 409 512 255410 842 467 1142925 1 154 401 1 409 512 255 110 842 467 1 142 925 300 45 1 258 627 1 461 902 203 275 923 601 1182 530 1 258 627 1 461 902 203 275 923 601 1 182 530 258 92 1 351 830 1 615 837 164 007 1 000 751 1 22773 1 361 830 1 515 837 164 007 1 000 751 1 227173 226 42 1 453 062 1 560 572 107 510 1 085 570 1 259 668 3 1 453 062 1 560 572 107 510 1 085 570 1 259 668 174 09 1 579 292 1 601 282 21 990 1 187 302 1 290 656 1 579 292 1 601 282 21 990 1 187 302 1 290 656 103 35 1721798 1 652 619 69179 1 305 999 1 336 015 1 721 798 1 652 619 69179 1 305 999 1 336 015 30 01 1 827 454 1 701 932 125 522 1 382 986 1 381 154 1 827 454 1 701 932 125 522 1 382 986 1 381154 1 83 2 025 218 1 788 826 236 392 1 544 634 1 458 061 2 025 218 1 788 826 1 544 634 1 458 061 86 57 1 863 926 1 946 136 2 052 320 106 184 1 428 938 1 946 136 2 052 320 106 184 1 428 938 110 2003 estimate 110 2003 estimate 111 2004 estimate 111 2004 estimate 112 2005 estimate 1122005 estimate 113 2006 estimate 113 2006 estimate 114 2007 estimate 114 2007 estimate 115 500 thousand or less 116 Note Budget figures prior to 1933 are based on the Administrative B
9. April 2004 Page 7 User Manual Synkronizer XL 8 0 SYNKRONIZER the ultimate excel compare tool Comparison by comments Comparison by values Old Turnover January February 11 00 March 9 00 1st Quarter 30 00 Comparison by formulas Old Turnover January February 11 00 March 9 00 1st Quarter 30 00 New Turnover January February 11 00 March 9 00 1st Quarter New Turnover January February 11 00 March 9 00 1st Quarter 30 50 With a comparison by comments all comments are compared Values or formulas are ignored Synkronizer performs the following functions Highlighter Reporter Navigator Toolbar All of the above In chapter 3 of the user manual the actions are described in detail April 2004 Page 8 User Manual Synkronizer XL 8 0 SYNKRONIZER Form D isplay differences D ifferences to be highlighted Rows to be hidden 2 2 Register Action Settings In this register you can determine how Synkronizer is to display the data Synkronizer XL 8 0 Developer Version Main Menu Action Settings Color Language How should differences be displayed Remove original background colors Highlight differences Which data should be displayed Unmatched columns Unmatched rows xI lt l Duplicate indexes xI Different cells x Identical cells DELETE identical data instead of HIDING it lt o wv 2 SYNKR
10. 6 817 80 716 3 899 85 1978 399 561 458 746 59 186 314169 369 089 54 920 85 391 89 657 4 266 86 1979 463 302 504 032 40 729 365 309 404 054 38 745 97 994 99 978 1 984 87 1980 517112 590 947 73 835 403 903 476 618 72 715 113 209 114 329 1 120 88 1981 599 272 678 249 78 976 469 097 43 053 73 956 13076 135196 5 020 89 1982 617 766 745 755 127 989 474 299 594 351 120 052 143 467 151 404 7 937 90 1983 808 385 207 822 453 242 661 277 208 035 147 320 147108 212 91 1984 851 874 185 388 500 411 686 061 185 650 166075 165 313 262 92 946 423 547 918 769 615 221 698 186171 176 307 9 363 990 460 568 986 806 962 237 976 200 228 183 498 16 731 1 004 122 149 769 640 951 810 290 169 339 213 402 193 832 19 570 1 064 489 155 187 667 812 861 798 193 986 241 491 202 691 38 800 991 190 1 143 683 152 493 727 525 932 772 205 247 263 666 210 911 2 754 1 031 969 1 253198 221 229 750 314 11028933 277 819 281 656 225 065 56 590 1 055 041 1 324 403 269 361 761 157 1 082 716 321 559 293 885 241 687 52198 1 091 279 1 381 684 290 404 788 853 1 129 345 340 492 302 426 252 339 50 087 1 154 401 1 409 512 255 110 842 467 1 142 925 300 457 311 934 266 587 45 347 1 258 627 1 461 902 203 275 923 601 1 182 530 258 929 335 026 279 372 55 654 1 351 830 1 515 837 164 007 1 000 751 1 22773 351 079 288 664 62 415 1 453 062 1 560 572 107 510 1 085 570 1 259 668 367 492 300 904 66 588 1 579 292
11. 70 1 259 668 14 579 292 1 601 282 21 990 1 187 302 1 290 656 1 721 798 1 652 619 69 179 1 305 999 1 336 015 1 827 454 1 701 932 125 522 1 382 986 1 3811154 2 025 218 1 788 826 236392 1 544 634 1 458 061 108 2001 863 8 aai 1 363 926 103 2002 estimate 052 106 184 1 428 938 109 2002 estimate 1 946136 2 052 320 106184 110 2003 estimate 8070 110 2003 estimate 111 2004 estimate 13741 111 2004 estimate 112 2005 estimate 61139 112 2005 estimate 113 2006 estimate 86 196 113 2006 estimate 114 2007 estimate 103 970 114 2007 estimate 115 500 thousand or less ti 116 Note Budget figures prior to 1933 are based on the Administrative Budget concep 116 500 thousand or less 117 117 Note Budget fiqures prior to 1933 are based on the Administrative Budget concepts r M 4 gt HN Sheet1 lal sIf Jia gt vi Sheet1 4 1 ToolBar The Navigator tool bar is as follows ME 100 M 4 6 pb DI ofSS 4 Show Update By fle D3 3 95 HYD 910 11 12 13 14 The Navigator tool bar contains the following functions 1 2 Tile worksheets By using these buttons you can tile the worksheets vertically 1 or horizontally 2 April 2004 Page 16 User Manual Synkronizer XL 8 0 SYNKRONIZER 9 10 11 D 13 14 Zoom With this command you can zoom the worksheets N avigation command With these commands you can navigate through each difference
12. ONIZER 2 Start Synkronizer D gt excel compare tool ee 2 2 1 Remove original background colors Here you can select whether Synkronizer removes the background color of the cells before high lighting the differences 2 2 2 Highlight differences If you select yes the differences in the underlying worksheets are highlighted 2 2 3 Which data should be displayed With this function you determine whether Synkronizer displays hides or deletes data For each file you can determine individually which data to be shown 1st file Master 2nd File Up date If the checkboxes are marked the desired data are shown otherwise they are hidden or deleted The following options can be selected Unmatched columns If these checkboxes are selected deleted or added columns are shown If not columns will be hidden or deleted April 2004 Page 9 User Manual Synkronizer XL 8 0 SYNKRONIZER Show rows Unmatched rows If you select these checkboxes deleted or added rows are displayd Duplicate indexes A database comparison can result in the creation of duplicate indexess If you desire to see these duplicate indexes mark these checkboxes Different cells If you select these checkboxes all rows with different cell entries are displayed Identical cells All rows with no differences are shown if you select this checkbox DELETE identical data instead of HIDING it Here you can determine whether non marked d
13. SYNKRONIZER the ultimate excel compare tool _ Synkronizer XL 8 0 User Manual TT User Manual Synkronizer XL 8 0 SYNKRONIZER Table of Contents 1 Install Uninstall Synkronizer sssesssssssssssssesessssssssesseessereesessssnssssetsresresnnnssssnssrsterresresesssseseess 3 TA VeTSION Senara aa a a a a n a 3 1 2 Installation Instructions uc sssssssessseccsseesecssessecssecsecssecsecssessecsscesccsscesscnscesscnesnecseesenseenses 3 1 3 Install Synkronizer manually ncsinossrsniniansaniemunnanoniiinni a 4 1 4 Uninstall SynkroniZer sesssssssssssseecesresesssssssessrereeseosssssssesssrseeeseosonsssnessersrereeoeosrosssessesreoresssssss 4 1 5 Uninstall Synkronizer Manually 5 cisssssstaisscacenipienvsbadpeacis peensstvaseas toeascvivlabsedy inadasnivlancsaandeus nase 4 2 Synkronizer Main MeNu cessscssssssessessscscsssssscsssssscsssesscsssseecsssssnesesssvcsessssesecsseesessssseessucseeesnceeesss 5 2 1 Register Main Menu esssessessssssssssseereessesssssesssrstereeoeeseossssentsrereeoronsssssssessesreseeorossssssoseesreeee 5 2 1 1 1st file Master 2 4 file Update c cccsessesessssssssssssssessesssssessessessesssssseassesesesessneans 5 2 1 2 Worksheet options c ccsccssssssesssescsssssncssessncsseesucsssssecssessecsusssssssssssssscesccsseesccneeeseeneenees 6 QoL A COO Mov sszvsievesiaze vases besess ze tazsasd annue a a E EEn ai 7 2 2 Register Action Settings
14. al If you want to create a difference report just enter the filename incl Path for the report to be created e g C Documents Report xls April 2004 Page 22 User Manual Synkronizer XL 8 0 SYNKRONIZER the ultimate excel compare tool VBA Example 5 4 Using Synkronizer in your VBA Scripts To execute Synkronizer with VBA set a reference to Synkronizer in your scripts To set a refer ence proceed as follows open the Excel worksheet that contains the VBA script open the VBA Editor Keyboard Shortcut Alt F11 set the focus to your VBA script go to the Tools menu and select References TF whe find SynkronizerX L in the list and put a check next to it Now you are able to use the Synkronizer function You can see some examples of VBA scripts below Please feel free to use them 5 5 VBA Examples 5 5 1 Example 1 This example highlights the differences between the worksheets named Addresses in the files FileO ld and FileN ew Public Sub Example_1 Dim varSynk As Variant On Error GoTo Err_Message varSynk Synkronizer sFileOld C Documents FileOld xls _ sFileNew C Documents FileNew xls _ sSheetOld Addresses _ sSheetNew Addresses Select Case varSynk Case False MsgBox Error _ vbOKOnly vbExclamation Case Tr MsgBox No differences noted _ vbOKOnly vbInformation Case Else MsgBox Synkronizer found differences amp _ Chr 10 amp Chr 10 amp varSynk _
15. ata are to be hidden or deleted The deletion of the desired data is only possible with the Highlighter Show columns rows If the columns and or rows are hidden and to be shown please proceed as follows 1 Select the entire worksheet K eyboard Shortcut Ctrl A 2 Execute the following menu instruction Format gt Rows gt Unhide April 2004 Page 10 he ultimate excel compare tool User Manual Synkronizer XL 8 0 SYNKRONIZER 2 3 Register Color Language In this form you can select the language and color settings Synkronizer XL 8 0 Developer Version Main Menu Action Settings Color Language Color Settings Unmatched columns Unmatched rows Duplicate indexes Different cells Language Settings Select your language english More Janguage filer available from Aomepage Yersion v8 0 b0521 C Program Files Synkronizer XL 8 0 syxl80 xla r e SYNKRONIZER 2 Start Synkronizer D gt excel compare tool 2 3 1 Color Settings In this section you can determine the colors with which Synkronizer highlights the differences To change a color just click with the mouse on the appropriate color surfaces left and select then the desired color right 2 3 2 Language Settings Here you can change the language settings The language data are stored in the same folder in which Synkronizer is installed normally C Program Files Synkronizer XL 8 0 The language files have the extensio
16. ded into three areas which are described as follows 2 11 1st file Master 2 4 file Update Here you choose the files to be compared The first file is the Master file and contains the older data the second file the newer data or Update With the Open File buttons you can browse your file system to find the required Excel files With the dropdown Worksheet s you can decide which worksheets are to be compared You can either compare two worksheets or all worksheets of both files together In this case select all worksheets Synkronizer then compares all worksheets with identical worksheet names April 2004 Page 5 4 User Manual Synkronizer XL 8 0 SYNKRONIZER A cess and OD BC D atabases W ork sheet properties normal work sheets D atabases primary key duplicate index es File Types If you are working with Excel XP Version 10 0 and the Developer Version of Synk ronizer the following file types are also supported File type File extension Access databases mdb mde External databases odc udl dsn Text files txt csv prn tab Webpages htm html mht mhtm X ML files xml Excel XP Note If you wish to compare an Access or other O D BC databases the data will be imported as snap shot Please note that Synkronizer only compares Excel copies of the databases This means that a maximum of 65 535 data records may be compared and that the databa
17. e are five different versions of Synkronizer available These versions contain the following Light Standard Premium Developer Shareware Version Version Version Version Version Highlight differences Create a difference re port Navigator Access O D BC Files Synkronizer VBA Price EUR 29 EUR 49 EUR 69 EUR 149 Free the shareware version expires 30 days after installation Please note that the user manual refers to the developer version only 12 Installation Instructions Just follow these easy steps to install Synkronizer 1 Start the file syx180 exe and follow the install instructions 2 Open Excel The menu entry Synkronizer XL 8 0 appears in the menu Tools Now the installation has been completed successfully Problems with French Excel versions If you are working with a French version of Microsoft Excel the following error message may occur This workbook has been password protected with an encryption scheme not available in this region This workbook cannot be opened This is an known error by Microsoft and is described in the following articles http support microsoft com default aspx scid kb E N U S q278679 http support microsoft com default aspx scid K B EN US 213833 If you are working with Excel 97 you can solve the problem by taking the following steps change the coun
18. e compared 3 the third worksheet of the 1 file will be compared sheet1 the worksheet with the caption sheet1 will be compared mm all worksheets will be compared Worksheet name or number of the 2 4 file Optional A1 Range of 1st worksheet If this argument is empty all cells will be compared Optional A1 Range of 274 worksheet Optional If you want to make a database comparison please enter the names of the keys in this section The keys are divided with semicolons e g Family name Fist name City If you do not make a database comparison leave the field empty Optional This argument contains the comparison function Y ou can enter the following arguments comparison by normal values comparison by case sensitive values comparison by normal formulas comparison by case sensitive formulas comparison by comments Or WNr If the argument is empty a comparison by normal values is executed see also 2 1 3 Action Optional This argument contains the action function You can enter the following arguments 1 the differences are highlighted 2 adifference report is created 4 Navigator toolbar The values can be combined E g if a3 is entered then the differences are highlighted and a difference report is provided If the field is empty the differences are highlighted April 2004 Page 21 e ultimate excel compare tool User Manual Synkronizer XL 8 0 SYNKRONIZER sReportFile Option
19. files Public Sub Example_5 Dim varSynk As Variant On Error GoTo Err_Message varSynk Synkronizer sFileOld C Documents FileOld xls sFileNew C Documents FileNew xls sSheetOld Addresses sSheetNew Addresses sKeyFields Familyname Firstname iCompareType 5 Select Case varSynk Case False MsgBox Error vbOKOnly vbExclamation Case Tn MsgBox No differences noted vbOKOnly vbInformation Case Else MsgBox Synkronizer found differences amp Chr 10 amp Chr 10 amp varSynk vbOKOnly vbInformation End Select Exit Sub Err_Message MsgBox Err Description vbOKOnly vbExclamation End Sub April 2004 Page 27 the ultimate excel compare tool User Manual Synkronizer XL 8 0 GSYNKRONIZER Compare multiple files 5 5 6 Example 6 Compare Multiple Files This procedure compares one Master file Master xls with all files within the folder CA Documents New and produces a difference report for every comparison The difference reports are saved in the folder C D ocuments Reports Public Sub Example_6 Dim varSynk As Variant Dim strFileOld strFileNew As String Dim strFolderNew As String Dim strFolderReport As String Dim strFileReport As String On Error GoTo Err_Message Set a Application strFileOld C Documents Old Master xls strFolderNew C Documents New strFolderReport C Documents Reports strFileNew Dir strFolderNew amp x
20. k Case False MsgBox Error vbOKOnly vbExclamation Case Tn MsgBox No differences noted vbOKOnly vbInformation Case Else MsgBox Synkronizer found differences amp Chr 10 amp Chr 10 amp varSynk vbOKOnly vbInformation End Select Exit Sub Err_Message MsgBox Err Description vbOKOnly vbExclamation End Sub April 2004 Page 25 User Manual Synkronizer XL 8 0 SYNKRONIZER 5 5 4 Example 4 This example compares the formulas of the databases Addresses The primary key is ID The differences are highlighted in both files Public Sub Example_4 Dim varSynk As Variant On Error GoTo Err_Message varSynk Synkronizer sFileOld C Documents FileOld xls sFileNew C Documents FileNew xls sSheetOld Addresses SSheetNew Addresses sKeyFields ID iCompareType 3 Select Case varSynk Case False MsgBox Error vbOKOnly vbExclamation Case Tn MsgBox No differences noted vbOKOnly vbInformation Case Else MsgBox Synkronizer found differences amp _ Chr 10 amp Chr 10 amp varSynk vbOKOnly vbInformation End Select Exit Sub Err_Message MsgBox Err Description vbOKOnly vbExclamation End Sub April 2004 Page 26 User Manual Synkronizer XL 8 0 SYNKRONIZER 5 5 5 Example 5 This procedure compares the comments of the databases Addresses The keys are Family name and Firstname The differences are highlighted in both
21. ls Do While strFileNew lt gt strFileReport Difference Report amp strFileNew varSynk Synkronizer sFileOld strFileOld sFileNew strFolderNew amp strFileNew sSheetOld 1 sSheetNew 1 iActionFlags 2 _ sReportFile strFolderReport amp strFileReport If varSynk False Then End Workbooks strFileNew Close SaveChanges True Workbooks strFileReport Close strFileNew Dir Loop Exit Sub Err_Message MsgBox Err Description vbOKOnly vbExclamation End Sub April 2004 Page 28
22. n slf At the moment Synkronizer is available in the following languages e English e German e Spanish e French e Italian e Dutch e Swedish e Polish April 2004 Page 11 e ultimate excel compare tool User Manual Synkronizer XL 8 0 SYNKRONIZER The languages are being constantly extended N ew languages can be downloaded at http www synkronizer com Free registration for translators We will give you a free Synkronizer registration D eveloper Version if you help us translate Synkronizer material into your native language Just send us a mail at support synkronizer com for your free copy Before starting your transla tion please contact us for detailed instructions April 2004 Page 12 the ultimate excel compare tool User Manual Synkronizer XL 8 0 GSYNKAONIZER 3 Action Highlight D ifferences 3 1 Highlighter If you choose this action Synkronizer just highlights the differences in the underlying work sheets B c D E F G H J 81 1975 279 090 332 332 3 242 216633 271 892 55 260 62 458 60 440 2 018 82 1976 298 060 371 792 73 732 231 671 3021183 70 512 66 389 69 609 3 220 83 TQ 81 232 95 975 14 744 63 216 76 555 13 339 18 016 19 421 1 405 84 1977 355 559 409 218 3 659 278741 328 502 49 760 7
23. nction of Synkronizer is the Navigator toolbar After the comparison process the worksheets are displayed vertically or horizontally and a user friendly tool bar is displayed with which you can easily navigate through the differences E Master Budget 2002 44 xls Cell B90 E X Amendments Budget 2002 45 xIs Cell B90 B EC D E F 399 561 458746 g 4 5986 314169 369 089 463 302 604 032 40 729 365 309 404 054 617112 590 947 73 835 403 903 476618 599 272 678 249 78 976 469 097 643 053 3 88 1981 599 272 678 249 78 976 469097 543 053 617 766 745 755 127 989 474 299 594 351 89 1982 617 766 745 755 127 989 474 299 694 351 120 05 808 385 207 822 463 242 661 277 90 1983 600561 808 385 207 822 453 242 661 277 208 03 666486 851 874 185 388 500411 686 061 1984 666 486 851 874 185 388 500 411 686 061 185 65 734088 946 423 547 918 769615 TON 7341088 946 423 47 918 769 615 221 69 769 215 990 460 568 986 806962 f 1986 769 215 990 460 568 986 806 962 237 97 854 353 1 004 122 149 769 640 951 810 290 1987 854 353 1 004 122 149 769 640 951 810 290 169 33 909 303 1 064 489 155 187 667 812 861798 FE j 1988 909 303 1 064 489 155 187 667 812 9861 798 193 98 9911190 1 143 683 152 493 727 525 932772 g 6 1989 991 190 1143 683 152 493 727 525 932 772 205 24 1 031 969 1 253198 221 229 750 314 11028133 i 1990 1 031 969 1 253 198 221 229 750 314 1 028 133 277
24. ollowing steps 1 Select the menu Tools Add Ins if it is not possible to select the menu Add Ins open a new Excel worksheet Now it is possible to select the menu 2 Remove Synkronizer from the add ins available list 3 Delete all files in the Synkronizer folder Synkronizer is installed in the following folder C Program Files Synkronizer X L 8 0 April 2004 Page 4 User Manual Synkronizer XL 8 0 SYNKRONIZER the ultimate excel compare tool Start Synk ronizer Select files compare all work sheets 2 Synkronizer Main Menu Synkronizer is started with the menu instruction Tools gt Synkronizer XL 8 0 An input mask appears which is divided into three registers The functions and properties of the Synkronizer main form are explained in the following chapters 2 1 Register Main Menu In the register Main Menu you can determine the files and worksheets which are to be com pared Synkronizer XL 8 0 Developer Version Main Menu Action Settings Color Language ist file Master Budget 2004 08 xls Worksheet s 2nd file Update bs Budget 2004 12 xls Worksheet s Sheet1 Worksheet options Sheett Action Worksheets are databases Comparison type values v Action I Highlighter MV Reporter Navigator Toolbar All of the above e SYNKRONIZER 2 Start Synkronizer D gt excel compare tool The input mask is divi
25. s 3 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 not only see the rows or columns deleted and or added but also each individual cell with different values 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 Difference Report SYNKRONIZER the ultimate excel compare tool a Comparison type values 21 05 2004 19 33 Budget 2004 08 xls Budget 2004 12 xls H Documents Budget 2004 08 xls H Documents Budget 2004 12 xls 21 05 2004 19 24 01 21 05 2004 19 24 02 synkronized worksheets these worksheets were compared v Sheet1 v Sheet1 worksheets sheet sheet1 55 unmatched rows 1 2 these rows were found in only one of the worksheets row 12 row 5 row 115 different values 52 these cells have different values A3 Year A3 Fiscal Year B90 600562 B90 600561 A92 1985 A92 D92 212334 D92 0 D93 221245 D93 221244 B108 1990866 B108 1991030 D108 126940 D108 127104 Versions The action Create a D ifference Report can be used with the following versions of Synkronizer e Standard Version e Premium Version e Developer Version April 2004 Page 14 User Manual Synkronizer XL 8 0 r SYNKRONIZER the ultimate excel compare tool N avigator T oolbar 3 3 Navigator Toolbar The third fu
26. ses can not be updated The option for updating the external databases will be included in a future ver sion of Synkronizer 2 12 Worksheet options Here you can determine the worksheet properties normal worksheets are ordinary worksheets which do not contain column headings and primary keys ID s Please note that the data should not be sorted Databases are Excel worksheets that contain a database structure The data must be arranged as follows e The first row contains unique column names e The database contains one column with primary keys unique identification number article number etc A primary key identifies the data records and facilitates the sorting and syn chronizing of your data The primary key does not need to be a single column it can also be formed from two or three columns e g family name first name and city Disadvantage of multiple column primary keys duplicate indexes may occur e Duplicate indexes are data records which contain the same identification number 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 are to be compared Make sure that you always operate with unique primary keys Once you have followed all steps as noted above Synkronizer will recognize all differences be tween the two files and list them accordingly April 2004 Page 6 TT User Man
27. sssssesessesssessucenessessnessnseneess 18 4 2 2 Update Master cccssssccsssssssssssssssessscssessncsssssscsnssssesussnsssussusssussnsssussnssasssneesseaseeseeneeens 19 42 3 Update Update sscan a aai a EEES 19 5 Synkronizer with Visual Basic for A pplications ccsesssssscesseceesessesesseesesseeseesessesneenesnes 20 DT oSI scission ea i hed aud T E R Na 20 5 2 R tum Value ss cicscersaaaisteanssecescnseena Seip a ETEO i 20 5 3 ATUM ENS innisin ninaa EE ENET Ei EE EE E A iSi EENIA ESOR SEEREN i a 21 5 4 Using Synkronizer in your VBA SCTipts cssssessesssessesssessssssssesssesecscssscseesnesseesseeseenseess 23 5 9 VBA EXaMples ccccscsssesssssessessessesssssessesssssesssssssssssessessssessessuseussacsusanssusaeceessucsecsesavseessnsansaseaes 23 55 1 Example l sez ccsssezcccssesctassascacesasetvasadatsadsancaadadavsuassacisa ca aasarasudeanattesctadennicanevactenteartonecattas 23 9 9 2 KOMI DLO Zearra nri E an KE E EEL Ei E EEEE NERES 24 Deeds Example J anainn anaa a n E RN 25 5 54 Example p evecaits scene tesasddesasvies caseussauctoa A A E E 26 Did EAM Ge aranana aaa a e E a 27 5 5 6 Example 6 Compare Multiple Files ccsessessesesssssesssssessecsessessessssseseesesssessessanes 28 April 2004 Page 2 User Manual Synkronizer XL 8 0 TT SYNKF RONIZER e ultimate excel compare tool V ersions of Synk ronizer Installation A French E xcel V ersion 1 Install Uninstall Synkronizer 1 1 Versions Ther
28. try settings from French to English open Synkronizer April 2004 Page3 e ultimate excel compare tool User Manual Synkronizer XL 8 0 SYNKRONIZER reset the country settings If you are working with Excel 2000 you can solve the problem with the following Microsoft hotfix http www synkronizer com download RC4Encr zip download the winzip file extract and install the hotfix By taking these steps you will easily solve the password protection problem 13 Install Synkronizer manually Y ou can also install Synkronizer manually by taking the following steps 1 The install program of Synkronizer copies the Synkronizer files to the directory C Programs Synkronizer X L 8 0 2 Copy the file syxl80 xla and the Synkronizer language files filetype slf to a directory of your preference 3 Open Excel and select the menu Tools gt Add Ins if it is not possible to select the menu Add Ins open a new Excel worksheet N ow it is possible to select the menu 4 Click on Browse and locate and choose the file syxl80 xla then click on OK The menu entry Synkronizer XL 8 0 appears in the menu Tools Now the installation has been completed successfully 14 Uninstall Synkronizer Synkronizer is uninstalled with the following instruction Start gt Programs Synkronizer XL 8 0 Uninstall Synkronizer XL 8 0 15 Uninstall Synkronizer Manually You can also remove Synkronizer manually by taking the f
29. ual Synkronizer XL 8 0 SYNKRONIZER C omparison type Comparison by values Comparison by formulas Checkbox Sort data If this checkbox is selected the data are sorted according to the primary key O therwise no sor ting is done 2 13 Action Here you can select the Action to be taken by Synkronizer Y ou can also determine whether the comparison should be made based on 1 values 2 formulas or 3 comments Comparison Type Synkronizer compares the data using the following criteria e normal values e case sensitive values e normal formulas e case sensitive formulas e comments Difference between normal and case sensitive comparisons Y ou can compare either normal or case sensitive cell entries If you make a normal com parison Synkronizer ignores differences between small and capital letters e g John Miller JOHN MILLER however with a case sensitive comparison all differences are recognized Difference between values and formulas The two examples below illustrate the differences between values and formulas The January turnover increases from 10 00 to 10 50 Accordingly the first quarter turnover changes from 30 00 to 30 50 Based on a value comparison the difference between the quar terly amounts is highlighted as monetary differences The formula comparison on the other hand only recognizes the January difference as the for mula for the first quarter is identical
30. udget concep__f 116 500 thousand or less 117 117 Note Budget fiqures prior to 1933 are based on the Administrative Budget concepts r mM 4 gt Sheet1 jal il pi gt A Sheeti D etailed information is described in chapter 4 Navigator Versions The Navigator can be used with the following versions of Synkronizer e Premium Version e Developer Version 3 4 All of the above This option highlights the differences creates an Excel report and then automatically starts the Navigator toolbar April 2004 Page 15 the ultimate excel compare tool User Manual Synkronizer XL 8 0 GSYNKRONIZER N avigator work sheets view N avigator tool bar D escription symbols 4 Navigator Toolbar Certainly the most comfortable function of Synkronizer is the Navigator toolbar After the comparison process the worksheets are displayed vertically or horizontally and a user friendly tool bar is displayed with which you can easily navigate through the differences Synkronizer ensures that both tables are displayed on your screen in order for you to directly compare all differences At this point you can decide with which version you wish to continue With mouse clicks you can transfer values from table 1 to table 2 or vice versa This is the quickest way to eliminate all differences and within a short period of time you will have your Excel worksheet perfectly updated
31. ws of the file Update can be added to the Master file The rows will be transferred from the right to the left worksheet Different values the cells which contain different values can be transferred to the Master file The differ ences can be transferred directly or by manual confirmation Formats the cell formats can be transferred from the Update file to the Master file the following formats will be transferred number alignment font border patterns protection these formats remain unchanged merged cells 4 2 3 Update Update Within this menu 7 you can update the Update file The menu functions are identical with the menu Update Master with the difference that the data are transferred from the Master file to the Update file or from left to right April 2004 Page 19 TT User Manual Synkronizer XL 8 0 SYNKRONIZER V ersions Syntax Radurn V alue 5 Synkronizer with Visual Basic for Applications Synkronizer can also be controlled using Visual Basic for A pplications V BA This has the ad vantage that recurring standardized or complex comparison processes can be automated Versions Synkronizer VBA can be used with the following version of Synkronizer e Developer Version 5 1 Syntax The syntax of the Synkronizer functions is as follows Synkronizer sFileOld As String _ sFileNew As String _ sSheetOld As Variant _ sSheetNew As Variant _
Download Pdf Manuals
Related Search