Home

User Manual - Synkronizer Excel Compare

1. Public Const ROOT As String D Documents ERSRC As String ROOT amp Source ERTGT As String ROOT amp Target ERREP As String ROOT amp Reports ERPRJ As String ROOT amp Projects ERLOG As String ROOT amp Log ESRC As String FOLDERSRC amp Source 1 xlsx ETGT As String FOLDERTGT amp Target 2 xlsx EREP As String FOLDERREP Synkronizer Difference Report xlsx EPRJ As String FOLDERPRJ SynkProject xml As String FOLDERSRC amp Protected 1 xlsx As String FOLDERTGT amp Protected 2 xlsx Public Const PROTTGT Example 1 compare all sheets with the same name create a difference report highlight differences show all differences except identical rows create a project with all settings Public Sub Examplel Dim oProj As Synkronizer Project Dim sMsg As String On Error GoTo EEE Example check if defined constants are valid Check Folders File 2000 2015 XL Consulting GmbH Switzerland 125 Synkronizer 11 User Manual get access to the Synkronizer application object InitSnk create project Set oProj snk NewProject With oProj load files Files Load FILESRC FILETGT define worksheets With Pairs MatchType MatchType AllByName AddMatched End With With Settings create a report Report ReportType Standard highlight differ
2. 2000 2015 XL Consulting GmbH Switzerland 32 Synkronizer 11 User Manual After comparison the files and the results form will be displayed side by side With the results form you can then smartly check the differences and eliminate them 2000 2015 XL Consulting GmbH Switzerland 34 Synkronizer 11 User Manual 3 Results Tab After comparing the results form as well as the worksheets are arranged side by side In the results form you can then smartly check and eliminate any difference 2000 2015 XL Consulting GmbH Switzerland Results Tab 35 S Current Activities Synkronizer 11 E Hi Ribbon g e In this section various actions can be SELECT RESULTS 00 performed With these actions you can Y 3 a selectively and efficiently review and i EGO e manage the differences Filters Report Highlight Outline All X X y X sheets Filters Tools Refresh Worksheet Ms Customer List op zE A le Customer Profile 1218 26 12 Customers by Countries af 5 2 17 Sales Summery 3 1 6 Sales by Categories 4 2 Sales by Countries Yi 4 2 Sales by Categories by Products 8 Sales Detail JEN 15 3 Compare with Last Month 10 20 Product Catalog 25 11 70 Employee Profile 22122 3 GA a EER ele d d ES e dl Sort by row gt Src Tgi Source Target E D7 Elke Bischof Hanna Moos B17 B17 Stuttgart Aachen Summary D1
3. 2000 2015 XL Consulting GmbH Switzerland 132 Synkronizer 11 User Manual Dim oProj As Synkronizer Project Dim sMsg As String On Error GoTo Err Example check if defined constants are valid Check Folders File get access to the Synkronizer application object TInitSnk create project Set oProj snk NewProject With oProj define files Files Load FILESRC FILETGT define worksheets amp link options Pairs AddPair Sheet0 Customer List Sheetl Customer List _ WSOptions WSOptionFlag_Rowslonl highlight differences With Settings Highlight HighlightType WithReset nd With E start Synkronizer Execute 13 get the message string before closing the project sMsg GetDifferences oProj close project CloseProject End With display message MsgBox sMsg vbOKOnly vbInformation Synkronizer Exit Sub Err Example MsgBox Err Description vbExclamation Synkronizer End Sub Example 7 2000 2015 XL Consulting GmbH Switzerland Developer Edition 133 f compare a project create workbook which contains all differences Public Sub Example7 Dim oProj As Synkronizer Project Dim sMsg As String On Error GoTo Err Example check if defined constants are valid Check Folders File get access to the Synkronizer application object TnitSnk load Synkronizer project Set oProj snk Ope
4. close project CloseProject display message sgBox sMsg vbOKOnly vbInformation Synkronizer Exit Sub Err Example sgBox Err Description vbExclamation Synkronizer End Sub 2000 2015 XL Consulting GmbH Swizerland 127 128 Synkronizer 11 User Manual Example 3 compare protected sheets highlight differences Public Sub Example3 Dim oProj As Synkronizer Project Dim sMsg As String On Error GoTo Err Example check if defined constants are valid Check Folders File get access to the Synkronizer application object InitSnk create project Set oProj snk NewProject With oProj define files Files Load PROTSRC PROTTGT With Pairs add worksheets AddPair Customer List Customer List AddPair Customer Profile Customer Profile enter passwords separated by semicolons PasswordList abc def End With highlight differences With Settings Highlight HighlightType Standard End With start Synkronizer Execute get the message string before closing the project sMsg GetDifferences oProj close project CloseProject End With 2000 2015 XL Consulting GmbH Switzerland Developer Edition 129 display message MsgBox sMsg vbOKOnly vbInformation Synkronizer Exit Sub Err Example MsgBox Err Description vbExclamation Synkronizer End Sub Example 4 compare
5. server path license xml Wow6432Node is used on 64bit windows with 32bit excel Cached assemblies are located in a subfolder under C Users UserName AppData Local Assembly dl13 User config files are located in a hashed subfolder under C Users Username AppData Local Microsoft_Corporation Prerequisites e Microsoft NET Framework 4 0 e Visual Studio Tools for Office Runtime 2010 The setup program will search for these prerequisites and install or update them as needed 5 1 Uninstall Synkronizer 11 is uninstalled as follows 1 Open the Windows Control Panel 2 Select Programs and Features 3 Select Synkronizer 11 and click Uninstall 5 2 Network Install For a network installation the license file must be stored in a shared network folder All users working with Synkronizer 11 must have read access to this file All other files may be located in any directory The network license The network license is a single file that contains all the software hardware and user data The network license is installed in a shared network folder a single time Thereafter the individual user PCs workstations which work with Synkronizer can be added or removed without reactivating the license Preparation amp Registration 1 Create a shared folder on a network 2 Create a text file named license xml 2000 2015 XL Consulting GmbH Swizerland s Synkronizer 11 User Manual 3 Write the following text in the
6. 3 Report gt Create a difference report do Highlight Define highlight options 0 Outline Define outline settings show hide options Tools Start Start the comparison Start 2 3 1 Project With the Project commands you can save recurring comparison processes as a project Supposed you need to compare the same files then you can save the files tables and comparison settings as a project Next time you can open this project and immediately start the comparison 2000 2015 XL Consulting GmbH Switzerland Synkronizer 11 User Manual 2 3 2 2 3 3 S Synkronizer 11 HE HH gt SELECT RESULTS 00 a Compare Tools GN inrkhanke 2 New The form is cleared all settings are reset Open This command opens an existing project Save The current project settings are saved as a project Compare Content In this menu you can choose whether additional content needs to be compared The following content options can be selected e Comments Cell Comments are information that can be added to each cell e Names Names are placeholders which can be used to identify a range of cells a function a constant or a table Proceed as follows to perform the comparison of additional content 1 Click Contents 2 Toggle comments names according to your needs 3 When activated the contents names icons change from gray to black Compare Formats The following cell formats can be compared e Nu
7. DifferentFormat different formats DifferentComment different cell comments DifferentName different Excel names TypeSort Type number of difference ColSort Column number of difference 10 RowSort Row number of difference 8 1 1 7 1 3 DifferentComments Returns the number of different cell comments Read only Long value Syntax expression DifferentComments expression A variable representing a Results object 2000 2015 XL Consulting GmbH Switzerland 102 Synkronizer 11 User Manual 8 1 1 7 1 4 8 1 1 7 1 5 8 1 1 7 1 6 8 1 1 7 1 7 8 1 1 7 1 8 DifferentContents Returns the number of cells with different entered vales and or different entered formulas Read only Long value Syntax expression DifferentContents expression A variable representing a Results object DifferentFormats Returns the number of cells with different formats Read only Long value Syntax expression DifferentFormats expression A variable representing a Results object DifferentNames Returns the number of different Excel names Read only Long value Syntax expression DifferentNames expression A variable representing a Results object DifferentValues Returns the number of cells with different cell results Read only Long value Syntax expression DifferentValues expression A variable representing a Results object DuplicateKeys Returns the number of duplicate keys database only Read only Long value Syn
8. Select Tab 23 S New Project EHA x PROJECT SELECT RESULTS UNPROTECT 00 a Information for F Information for F General General Quality Status Quality Status f f Protected sheets f _ AddPassword O Workbooks y Worksheets A Choose the worksheets you want to compare gt Source worksheet a gt Target worksheet No items No items gt Information for Project Manage Information for Project Manage O Options Compare as worksheet D Compare with link options Compare as database Ranges optional select range General Quality Status Financial Overview select range General Quality Status Financial Overview Milestones Milestones 4 You will find three columns in the tab Unprotect The first column a holds all worksheets of the first file listed which are not password protected the second column b holds the password protected worksheets of the second file The third column c contains an input field in which you can enter password s to unlock the worksheets S Synkronizer 11 B A x PROJECT SELECT RESULTS UNPROTECT 00 a Information for F Information for F General General sesono Quality Status Quality Status a gt c Protected sheet Add dee 5 Enter the password in the field c and unlock the worksheets If you have successfully entered all the passwords the worksheets are shown in green and the Unprotect tab disappears 2000 201
9. Synkronizer 11 User Manual P License x Deactivate your license Registration key DEMOV6W KIRRAL3 HLLC7MA Identification Select a question Your answer Place of birth ZUERICH Deactivate your license online License Manage Register 5 The register form is displayed Enter your secret answer and click Deactivate your license online 6 The software is deactivated If you have no internet connection you ll need to deactivate the software manually A manual deactivation works exactly the same like the manual activation The only difference is that you ll get an unlock key instead of an activation key Read this chapter for manually deactivating the software 2000 2015 XL Consulting GmbH Switzerland Synkronizer 11 User Manual 7 Problems Errors Synkronizer 11 is installed but does not appear on the toolbar The most likely cause is that Synkronizer ended up under Disabled Add ins This may occur when Excel is shut down or crashes while Synkronizer is busy Proceed as follows to re enable Synkronizer 1 Click the Microsoft Office Button image click Excel Options and then click Add Ins 2 Check if Synkronizer 11 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 either in Active Application Add ins or Inactive Application Add ins If it is list
10. 2 Employee Profilexml Define general settings EO Save 3 Customer Analysis xml 4 A Save As 4 Benchmarkxmi Get set information about your licen O Reload license Recent Settings System 4 1 Projects Synkronizer 11 B H x PROJECT SELECT RESULTS 00 a D New Recent projects E Open 1 Sales Analysis xml Za 2 protected2 xml E B save 3 Staff Listxml GO ba ZO 4 Historical Income Statementsxm A al Save As 5 Current Activities xml A O Reload 6 Customers xml 4A 7 Financial Statement xml A Recent 8 Top Employees xml A P 9 Customers Merge xml A Settings protected xml A System 2000 2015 XL Consulting GmbH Switzerland s Synkronizer 11 User Manual 4 1 1 All comparison processes can be saved in projects As a great benefit thereof you don t need to select the files worksheets and change the comparison settings again each time In this menu the following commands are available to manage the projects New The form is emptied all settings are reset Open This command opens an existing project Save The current settings are saved as a project Save as The selected project is saved under a new name Reload The project is reloaded all previous and unsaved changes are lost Recent Recent projects List of recently used projects The project is reloaded all previous settings are lost Note The worksheet passwords are not stored in the projects FileFormat The project file is a si
11. Contents constant Syntax expression Contents expression A variable representing a Settings object Example This example also compares comments and Excel names With Settings Contents ContentFlag Comments ContentFlag_Names End With 8 1 1 6 2 2 FilterEquivalents Returns or sets the equivalent values String value Syntax expression FilterEquivalents expression A variable representing a Settings object Example This example compares the worksheets using filters With Settings Filters FilterFlag Enabled FilterFlag StringCase FilterFlag StringSpace FilterTolerance 0 01 FilterEquivalents yes ja no nein End With 2000 2015 XL Consulting GmbH Swizerland 8 1 1 6 2 3 8 1 1 6 2 4 8 1 1 6 2 5 Developer Edition Returns or sets a series of options specifying which differences to filter ignore FiltersFlag constant Filters Syntax expression Filters expression A variable representing a Settings object Remarks If the ENABLED flag is not set all other flags are ignored Example This example compares the worksheets using filters With Settings Filters FilterFlag Enabled FilterFlag StringCase FilterFlag StringSpace FilterTolerance 0 01 FilterEquivalents yes ja no nein End With FilterTolerance Returns or sets a number below which numeric differences will be ignored Double value Syntax expression
12. FilterT olerance expression A variable representing a Settings object Example This example compares the worksheets using filters With Settings Filters FilterFlag Enabled FilterFlag StringCase FilterFlag StringSpace FilterTolerance 0 01 FilterEquivalents yes ja no nein End With Formats Returns or sets a series of options specifying which formatting properties to compare FormatsFlag constant Syntax expression Formats expression A variable representing a Settings object Remarks 2000 2015 XL Consulting GmbH Swizerland 98 Synkronizer 11 User Manual If the ENABLED flag is not set all other flags are ignored Example This example compares font formats With Settings Formats FormatFlag Enabled FormatFlag Font End With 8 1 1 6 2 6 Highlight Returns or sets a constant specifying which Highlighting to perform Uses HighlightType constants Syntax expression Highlight expression A variable representing a Settings object Example This example highlights the differences With Settings Highlight HighlightType Standard End With 8 1 1 6 2 7 Outline Returns or sets a series of options specifying which group of Rows and Columns to Show or Hide Uses OutlineFlag constants Syntax expression Outline expression A variable representing a Settings object Remarks If the ENABLED flag is not set all other flags are ignored Example Th
13. I K HorizontalAlig Co Co Co Co A ColumnWidth 20 B K ColumnWidth D E ColumnWidth F H ColumnWidth lumns lumns lumns lumns AUSA If p DBKeys Then Columns F H EntireColumn Application SheetsInNewWorkbook oProj Pairs Count iWksCount iffs ize UBound val 1 1 wan nment xlLeft nment xlRight 8 32 18 Hidden True 2000 2015 XL Consulting GmbH Switzerland UBound val 122 Synkronizer 11 User Manual End If End With iPair iPair 1 Next p DoEvents Application ScreenUpdating True End Sub this procedure creates a log file with all differences Public Sub Logfile PrintDiffs oProj As Synkronizer Project Dim p As Pair Dim sText As String Dim sLine As String source file sText Source File sLine sText String 34 Len sText amp oProj Files sidelD src Print 1 sLine target file sText Target File sLine sText String 34 Len sText amp oProj Files sideID tgt Print 1 sLine heading row differences sText Worksheet names sLine sText amp String 32 Len sText sLine sLine amp Total sLine sLine amp _ Columns amp _ DupRecs amp _ DupKeys amp _ Rows amp _ Contents amp _ 7 Values If CBool oProj Settings Formats And FormatFlag Enabled Then sLine sLine amp Formats End
14. e Reset defaults to factory All settings of the Select tab will be reset to the factory settings Undo settings A strength of Synkronizer is its ability to highlighted differences and hide unwanted ones with the outlining feature The downside of this option is that these changes will possibly remain in the Excel worksheets In this area you can choose what should happen with the highlighted and outlined changes The following options are available Show dialog When closing the Excel file a dialog box is displayed allowing you to select whether the changes should be reset or not Never undo The Synkronizer changes will not be reset Always undo The Synkronizer changes are reset Your Excel files will therefore be in the same state as prior to the comparison except the transferred differences Color themes If you don t like the colors Synkronizer uses to highlight the differences you have the option to define your own The colors can be reset to the original Synkronizer colors with the button Reset defaults to factory Do not use color If certain difference types should not be highlighted then simply select the desired difference type and click No Color 2000 2015 XL Consulting GmbH Switzerland Project Tab 51 43 System In this menu you will find some information about your Synkronizer license BHA gt SELECT 00 a Edition Professional Edition Editi on Registration amp Activa
15. none 8 1 1 2 2 Properties 8 1 1 2 2 1 8 1 1 2 2 2 Files Container for the Files workbooks of the project Read only Files object Syntax expression Files expression A variable representing a Project object IsDisposed This property checks if the project was deactivated and internal object set to nothing Read only Boolean property Syntax expression IsDisposed expression A variable representing a Project object 2000 2015 XL Consulting GmbH Switzerland Developer Edition es 8 1 1 2 2 3 IsValid Checks if files are valid and pairs exist and are all valid Read only Boolean property Syntax expression IsValid expression A variable representing a Project object 8 1 1 2 2 4 Pairs Container for the pairs worksheets of the project Read only Pairs object Syntax expression Pairs expression A variable representing a Project object 8 1 1 2 2 5 ReportWorkbook Report workbook when created Read only Workbook object Syntax expression ReportWorkbook expression A variable representing a Project object 8 1 1 2 2 6 Results Container for the combined Results of all pairs Read only Results object Syntax expression Results expression A variable representing a Project object 8 1 1 2 2 7 Settings Container for all settings of the project Read only Settings object Syntax expression Settings expression A variable representing a Project object 2000 2015 XL Consulting GmbH Swi
16. Next p End With DoEvents Application ScreenUpdating True End Sub this procedure creates a new workbook and writes all pair differences in a separate worksheet Public Sub DumpDetails Pairs oProj As Synkronizer Project Dim wkb As Workbook Dim wks As Worksheet Dim p As Pair Dim iPair As Integer Dim val As Variant Dim rng As Range Dim iWksCount As Integer check if project is active Debug Assert Not oProj Is Nothing Debug Assert Not oProj IsDisposed create workbook 2000 2015 XL Consulting GmbH Switzerland Developer Edition 121 iWksCount Application SheetsInNewWorkbook Set wkb Workbooks Add Application SheetsInNewWorkbook loop through pairs iPair 1 For Each p In oProj Pairs name worksheet Set wks wkb Worksheets iPair wks Name p SheetName 0 get results p Results ArrayDetails wks Cells 1 val Set rng write title With rng Value p Sh Font Size Font Bold End With tNam 12 True 0 write down differences If IsEmpty val Then no differences found rng Offset 2 0 Value se differences found Set rng rng Offset 2 2 EL rng Clear rng Resize 6 NumberFormat rng Resize 2 HorizontalAlig rng VerticalAlignment xlTop rng Value2 val End If val ne d EO 0 Res Empty format With rng Rows 1 Fon range t Bold True Columns
17. ak idiak POS AE O AE AE EEEn ActiveProject BAGEA DREA Pr ject elia AAA Methods Close Save Properties Files DIS POS Ca eins A AAA ln inh anhalt IsValid Pairs Egea Aiz asala AREA 83 Results AA adi wid eA A AAA AA E ea ek 83 Settings daragoio ak idiak 83 EVGRIS Anta O EU 84 POS ii A E E A heat 84 Files Object marinan n a a AAA AAA AA ia Methods Load Save NN 85 FileName S tr AA AAA Aids 85 AUN ana a A tE fae e Ia ieee ote 85 AAA 86 MAiA ella 86 Pairs Object Methods Balea EAEKO EO ea EO ALIA aN 89 Item 2000 2015 XL Consulting GmbH Switzerland 4 Synkronizer 11 User Manual WatchinclUde A idea AAA TE 89 Match LY DEN TO dia A A A 90 Pass Word Lenin iid hain A A AA EA Aid 90 Bidea gia AAE 91 o Aia ET ENEA TN A KTA EN EET 91 ACtiVale ii aala AEE 91 Properties DBKeys DBRow Range RANGE AO ati AE a a E AE A a E E 94 GGE Gre a A aaa ba rak Eee 94 SheciNaM iia iaa 94 WSO ueu 95 Settings ODE A A AA AAA an an redid Avie Methods ais Ada Clear Properties Contents FilterEquivalents ci Ai bd eis 96 EUGI ebe aint disk Resin el Ah E 97 FilterToleranGe gaturik dg auda 97 Formats Highlight Outline Report Results Ol a 1e AENA Salazar LAGE 100 Array Delia A A AA att 100 Dif erentCo MENS AA E TN Ea ai arara Era 101 DifferentContents BAU Elu DifferentNames comi IA rea anand Aided bar g haad DifferentV aluesiiic acca neivisiiaescia ei i el eae Migs A Pa ee te D
18. and Excel Start the CommandLine Utility of Synkronizer using synk exe Enter the source file with the command sre file name Enter the target file with the command tgt file name Enter the Synkronizer project file which contains the settings xml file name Compare the files with the command d 10 Close the Synkronizer CommandLine Utility with the command x MO OO Y AU b WN b 2000 2015 XL Consulting GmbH Switzerland 12 Synkronizer 11 User Manual Synkronizer CommandLine Utility D Documents matching 1 lt AL1ByName gt report 1 lt Standard gt sre Source Filei xlsx tgt Target File2 xlsx xml1 Projects S ynkPro ject xml gt c Direct command for batch file or Windows shortcut C Program Files Synkronizer Synkronizer 111Synk exe dir D Documents src s 2000 2015 XL Consulting GmbH Switzerland Index Normal worksheets 19 A actions highlighting create report outlining 11 activation 64 65 activation process 64 ActiveProject 80 Add 87 91 AddMatched 87 Admin rights 54 Administrative Rights 54 Alignment 26 Automatically pair worksheets B Border 26 Cx Clear amp highlight differences 29 Close 81 Colorthemes 49 COM Add In 7 12 14 Comments 26 96 105 comparison options 11 Content 96 105 Contents 26 Count 89 D Database flag 105 database options 17 Database settings 17 database structure 17 DBKeys 92 DBOptions 92 2000 20
19. between the worksheets at a glance Worksheet Customer List Customer Profile Customers by Countries Sales Summery Sales by Categories Sales by Countries Sales by Categories by Products Sales Detail Compare with Last Month Product Catalog Employee Profile x rr i e resi un P ofl 312 25 22 22 N N Pon In the columns of the list box the various differences are displayed In this screenshot for example 2000 2015 XL Consulting GmbH Switzerland Results Tab the following differences were found in the worksheet Customer List Missing columns O 1 no different column in source worksheet one different column in target worksheet Missing rows 3 2 three different rows in source worksheet two different rows in target worksheet Differences in entered values 9 differences in entered values formulas formulas Differences in calculated 0 formulas The following difference types are reported in this list box WW A Missing columns These columns exist only in one worksheet Duplicate records redundant rows These records are completely identical These records should be deleted Appears only in the database comparison mode Duplicate keys These records have the same primary key Appears only in the database comparison mode Missing rows These rows exist only in one worksheet Differences in entered cell values And or formulas The entered cell values and or formulas are di
20. can navigate to the next or previous difference Bb Transfer different columns rows from source to target worksheet SA With these buttons the columns rows are copied from the source to the target worksheet from left to right Itis possible to copy just the selected column row or all columns rows Once the columns rows have been copied they are displayed in red 2000 2015 XL Consulting GmbH Switzerland a Synkronizer 11 User Manual ee ce H bx XX A ii E E EE TA aS E Be Sort by row Sort by column Group by row These commands are also available in columns that contain at least one difference Transfer different columns rows from target to source worksheet As above but in the other direction Delete different columns rows in the source worksheet With these buttons the columns rows in the source worksheet left will be deleted It is either possible to delete all or only the selected column row Delete different columns rows in the target worksheet As above Transfer cell differences comments from source to target worksheet With these buttons the different cell values or comments are copied from the source to the target worksheet from left to right It is either possible to copy all or only the marked difference Once the difference has been copied it is shown in red Transfer cell differences comments from target to source worksheet As above With this button the sorting of
21. differences of entered values formulas 7 Values Overall differences of calculated values 8 Formats Overall differences of format differences 9 Comments Overall differences of cell comments 10 Names Overall differences of cell Excel names ArrayDetails Returns an array with the detail information of the specified differences found as text Read only Array Syntax expression ArrayDetails Type expression A variable representing a Results object 2000 2015 XL Consulting GmbH Switzerland Developer Edition or Parameters Type ResultType Constant selects group of results to return If not set all result types are returned Description of Array This property returns an array Each difference is is represented by a single element of the array with 11 columns The first element of the array contains the column headings 0 Source Name of source worksheet 1 Adr1 Range address of source worksheet 2 Adr2 Range address of target worksheet 3 Vall Cell value formula of source worksheet 4 Val2 Cell value formula of target worksheet 5 Key Primary key only if database 6 Fld Field name only if database 7 TypeName Type of difference The following difference types are displayed MissingCol different columns DuplicateRec duplicate records DuplicateKey duplicate keys MissingRow different rows DifferentContent different entered values formulas DifferentValue different calculated formulas results
22. matched by AddMatched method MatchType constant Syntax expression MatchType expression A variable representing a Pairs object Example This example adds all worksheets with equal names Protected and hidden sheets are included With Pairs include protected and hidden sheets MatchInclude MatchIncludeFlag ProtectedSheets MatchIncludeFlag HiddenSheets match all sheets with same name MatchType MatchType AllByName add sheets AddMatched End With 8 1 1 4 2 6 Passw ordList 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 Example This example adds two password protected worksheet pairs With Pairs add worksheets AddPair Customer List Customer List AddPair Customer Profile Customer Profile enter passwords separated by semicolons PasswordList abc def End With 2000 2015 XL Consulting GmbH Switzerland Developer Edition ot 8 1 1 5 Pair Object Container for a pair of worksheets to compare Methods Activate Activates the pair object Properties 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 DBRow Returns or sets the index for the row which contains the headers field names IsValid Returns true
23. servervirtual Edition EditionPro_Release11 Activation ActKey YXGDIWI N7NYMRW YSSNRR amp OK Hardware UUID E812A037 C3E6 FBD6 A75A EA38F6CAA83D OK DISK 646FB2FB OK SYST MYPC SYNKRONIZER SERVER oK Deactivate License Manage li 6 If you are working with a network license click on the Nodes Info tab and make a screenshot of it 2000 2015 XL Consulting GmbH Switzerland Synkronizer 11 User Manual P License Computer Edition MYPC Professional Edition License s PRO NETWO3Z CNP4TOS K4EG3LM E Add Key Info Nodes Info 4 NodelD Description optional MYPC Administrator EUR MKT DELL 2 Hilda Frankfurt USA MKT HP 1 George Seattle USA MKT HP 2 Bill Little Rock USA MKT HP003 Steve Redmont IND DEV ACER 1 Prawash Mumbai IND DEV ACER 2 Rajya Mumbai IND DEV ACER 3 Pratibha Mumbai IND DEV ACER 4 Narasimha Mumbai IND DEV ACER 5 Mammohan Mumbai Add this node Copy list 10 GO 10 Save Cancel License Manage 7 Attach the file license xml to the e mail The license file is stored in the following folder Local license C ProgramData Synkronizer Synkronizer 11 Network license Shared network folder 8 Check if a error file was generated in the Windows temp folder Open the Windows Explorer end enter the folder TEMP If a file named synkronizerError txt is there attach it to the e mail 9 Send the screenshots and files to supportOsynkronizer com along with any furthe
24. to O as soon as you select 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 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 lt Filenamel gt C Folder SubFolder Fi lt Files gt lt Pairs gt lt MatchInclude gt 3 lt MatchInclude gt lt MatchType gt 0 lt MatchType gt 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 s lt Filename0 gt s lt Filenamel gt 2000 2015 XL Consulting GmbH Switzerland e Synkronizer 11 User Manual 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
25. variable representing a Results object 8 1 1 7 1 14 SumByType Returns the number of specified differences found Read only Long value Syntax expression SumByType Type id expression A variable representing a Results object Parameters Type ResultType Constant selects group of results to return id sidelD Optional Only needed for MissingCols and MissingRows Leave blank to return combined results Example This example prints out some differences With oProj Results print out entered value formula differences Debug Print Different values formulas amp SumByType ResultType DifferentConter print out missing rows in source file Debug Print Missing Rows Source amp SumByType ResultType MissingRow sidelD sr print out missing rows in target file Debug Print Missing Rows Target amp SumByType ResultType MissingRow sidelD tc End With 8 1 1 7 1 15 SumText Returns a formatted text that can be used in a messagebox Read only String value Syntax expression SummaryText expression A variable representing a Results object 2000 2015 XL Consulting GmbH Switzerland Developer Edition 105 8 1 1 7 1 16 SumTextBy Type Returns the number of specified differences found as text Read only String value Syntax expression SumTextByType Type expression A variable representing a Results object Parameters Type ResultType Constant selects group of results to return 8 1 1 8 Enum
26. 1 George Seattle USA MKT HP 2 Bill Little Rock USA MKT HP 3 Steve Redmont IND DEV ACER 1 Prawash Mumbai IND DEV ACER 2 Rajya Mumbai IND DEV ACER 3 Pratibha Mumbai IND DEV ACER 04 Narasimha Mumbai IND DEV ACER 5 Mammohan Mumbai Addthis node Copy Paster 10 10 Save Cancel License Manage 2000 2015 XL Consulting GmbH Switzerland Install Uninstall st 5 3 Scripted Installation The setup executable is a so called bootstrapper for the windows installer files contained within it also takes care of the prerequisites NET framework and VSTO runtime Command line arguments or help displays help dialog extract lt directory gt extracts all files in lt directory gt listlangs lists languages supported by this setup exenoui launches the EXE setup without UI exebasicui launches the EXE setup with basic UI exelang lt langId gt launches the EXE setup with the specified language username username used by the proxy password password used by the proxy exelog lt path_to_log file gt creates a log file at specified path exenoupdates does not check for a newer version passive licensepath lt Server Path License xml gt used for network installation defines the path and name of the license file lt msiOptions gt options for msiexec exe running the MSI package Example Standard install without displaying user interface UI Synkronizer 11 0 xxxx exe exe
27. 15 XL Consulting GmbH Switzerland Developer Edition Print 1 End If read target files i 0 sFile Dir sFolderTgt amp xls Do While Len sFile gt 0 ReDim Preserve aFiles i aFiles i sFil dz dd 1 sFile Dir Loop loop all files For i 0 To UBound aFiles sFileTgt sFolderTgt amp aFiles i sFileRep sFolderRep amp Difference Report amp aFiles i sFileRep Left sFileRep InStrRev sFileRep 1 xlsx create new project Set oProj snk NewProject With oProj load files Files Load sFileSrc sFileTgt match all worksheets with same name With Pairs MatchType MatchType AllByName MatchInclude MatchIncludeFlag HiddenSheets MatchIncludeFlag ProtectedSheets AddMatched End With highlight amp create report With Settings HighlightType Standard If bHighlight Then Highlight If sFolderRep lt gt Then End With compare Execute log differences If sFolderLog lt gt Then Print 1 aFiles i vbTab amp Call Logfile PrintDiffs oProj End If If Results Sum Then if differences found create 2000 2015 XL Consulting GmbH Swizerland Report ReportType Standard Results Sum report 117 ns Synkronizer 11 User Manual If sFolderRep lt gt Then If Len Dir sFileRep gt 0 Then Kill sFileRep If Not ReportWorkbook Is Nothing Then With ReportWorkbook SaveAs fi
28. 15 XL Consulting GmbH Swizerland DBRow 93 deactivate 67 Deactivate software 55 67 Deactivate your license online 67 DefaultsSave 96 Deinstall software 55 delete Synkronizer colors 49 Delete different columns rows 39 Delete unwanted differences 34 delete worksheets 12 Detail information of differences 39 Details with each difference 100 Differences in calculated formulas 36 Differences in entered values formulas 36 Different calculated values 102 Different cell comments 101 Different cell formulas 102 Different cell results 102 Different cell values 102 Different Excel names 102 Different formats 102 DisplayStatus 80 Duplicate keys 19 36 102 Duplicate records 103 Duplicate Records Redundant records 19 Duplicate records redundant rows 36 Dupliocate primary keys 102 ae empty worksheets 12 Equivalent values 27 Errors 72 example code 125 Excel Add In 7 Excel names 26 96 105 Execute 82 E FileName 85 Files 82 Fill 26 Filter options 42 FilterEquivalents 96 Filters 11 27 97 1 Synkronizer 11 User Manual FiltersFlag 105 FilterTolerance 97 Font 26 Formats 26 97 FormatsFlag 106 Qu General Information About Databases 19 Group differences 39 Group records 17 H hidden worksheets 12 Highlight 29 98 Highlight differences 29 Highlight options 42 HighlightType 106 Hyperlinked report 29 Ignore case 27 Ignore constants 27 Ignore data type 27 Ignore formulas 27 Igno
29. 5 XL Consulting GmbH Switzerland 24 Synkronizer 11 User Manual 2 3 S New Project E He x SELECT RESULTS 00 a D amp Content 3 Report e E E Formats do Highlight P E Y Fites ZZ Outline Start Project Compare Tools O Workbooks v Worksheets A Choose the worksheets you want to compare a Source worksheet Target worksheet No items No items gt Options Compare as worksheet O Compare with link options Compare as database Ranges optional select range kal select range General General Quality Status Quality Status Financial Overview Financial Overview Milestones Milestones Ribbon In the upper part of the selection form you ll find a toolbar which allows you to define numerous options S Customer Analysis Synkronizer 11 SELECT RESULTS D dd Contents Report gt EO ba Formats 7 Highlight A Y Filters ZE Outline a Project Compare Tools The following options are available BHA GO x 00 a 2000 2015 XL Consulting GmbH Switzerland Select Tab 25 Project options D Reset project All settings are set back to defaults Bb Load a project EO Save project Project Compare options q Contents 7 Compare additional content like comments and Excel names TZ Formats gt Define format options colors fonts number formats etc Y Filters gt Define filters Compare Tools options
30. 7 D17 Rita M ller Berndt Schneider This pane shows the worksheets and the D29 D28 Holger Schmidt Peter Franken number of differences found D52 D51 Philippe Cr tien Jean Fresni re Differences for the selected worksheet B76 B75 London Birmingham category are shown in the lower pane D76 D75 Josh Gallagher Hari Kumar B86 B85 Denver Kirkland D86 D85 Brit Joyner Helvetius Nagy 2000 2015 XL Consulting GmbH Switzerland Synkronizer 11 User Manual 3 1 Actions With the results form you can perform the following actions Overview of differences Synchronize worksheets transfer differences Delete unwanted differences Show hide differences Summary Differences This pane shows each difference found for the selected worksheet category cell in the upper pane You can navigate to each difference and decide if the difference needs to be transferred or not Synkronizer will automatically display the corresponding areas of both worksheets side by side so you can easily and directly overview the differences At this point you may decide with which version you wish to continue You can transfer values from the source sheet to the target sheet or vice versa This is the most efficient way to eliminate all differences and your Excel worksheet will be perfectly updated in no time The upper part of the Results form displays the worksheets and their differences You can see the number of differences that were found
31. Example 9 compare one source file against a series of target files a difference report will be created for each file set a log file will be created Public Sub Example9 Dim sMsg As String Paramater description sFileSrc Source file sFolderTgt Folder with target files to be compared bHighlight Select True if differences should be highlighted sFolderLog If difference reports are needed enter folder Optional sFolderLog If a log file is needed enter folder Optional sMsg SynkSrcFolder sFileSrc FILESRC MsgBox sMsg End Sub sFolderTgt FOLDERTGT bHighlight True _ sFolderRep FOLDERREP sFolderLog FOLDERLOG 2000 2015 XL Consulting GmbH Swizerland Developer Edition 135 8 2 CommanodLine Utility With the Developer Edition of Synkronizer 11 it is also possible to compare Excel files via the command line commands The Excel files can be compared fully automated with a single mouse click without opening Excel How to use the CommandLine Utility synk exe is a handy program which allows you to compare Excel files without opening Excel You can enter all variables like file names folders and comparison options in a single command The program then generates different reports and log files with all differences While executing the program will start a hidden instance of Excel After the comparison process the Excel instance will be closed again Which ta
32. ICK 10 Note the activation key and return to the license manager of Synkronizer 11 Enter the activation key in the input field Activation Key 12 The software is now activated If you have any problems with the registration activation process please read chapter Problems with registration Deactivate Software You can transfer your license to another computer at any time Simply deactivate the license on the old computer and activate it on the new one 1 Start Synkronizer 2 Select Project Settings License Manager 2000 2015 XL Consulting GmbH Switzerland e Synkronizer 11 User Manual P License x Professional Edition Version 11 0 40416 1430 License file Type Local Path C ProgramData Synkronizer Synkronizer 11 license xml License Manage lt lt 3 Select the tab Manage 2000 2015 XL Consulting GmbH Swizerland Registration e P License x Computer Edition MYPC Professional Edition License s PRO DEMOVGW KIRRAL3 HLLC7MA e Add Key Info Name Value Valid License Regkey DEMOV6W KIRRAL3 HLLC7MA oK Number egal eel Type Temporary2week exp 23 04 2014 Install Localvirtual Edition EditionPro_Release11 Activation ActKey 2D36NCI WHKYYID B4AYICK oK Hardware VUID 4C4C4544 9847 3219 8052 C7C04F46354A oK DISK BASDA69B oK SYST DELL INC PRECISION T3600 OK License Manage 4 Click Deactivate 2000 2015 XL Consulting GmbH Switzerland
33. If If CBool oProj Settings Contents And ContentFlag Comments Then sLine sLine amp Comments End If If CBool oProj Settings Contents And ContentFlag Names Then sLine sLine amp Names End If 2000 2015 XL Consulting GmbH Switzerland Developer Edition 123 Print 1 sLine pair differences For Each p In oProj Pairs With p Results sText p SheetName sideID_ src sLine sText amp String 32 Len sText sText CStr Sum sLine sLine amp String 7 Len sText amp sText sText SumTextByType ResultType MissingCol sLine sLine amp String 9 Len sText amp sText sText SumTextByType ResultType DuplicateRec sLine sLine amp String 9 Len sText amp sText sText SumTextByType ResultType DuplicateKey sLine sLine amp String 9 Len sText amp sText sText SumTextByType ResultType MissingRow sLine sLine amp String 9 Len sText amp sText sText SumTextByType ResultType DifferentContent sLine sLine amp String 9 Len sText amp sText sText SumTextByType ResultType DifferentValue sLine sLine amp String 9 Len sText amp sText If CBool oProj Settings Formats And FormatFlag Enabled Then sText SumTextByType ResultType DifferentFormat sLine sLine amp String 9 Len sText amp sText End If If CBool oPro
34. IncludeFlag_None Visible worksheets with content will be included Default setting MatchIncludeFlag_ProtectedSheets Protected sheets will be included by AddMatched 8 1 1 8 7 MatchType List of valid types of matching MatchType AllByCodeName All included sheets are matched by code name MatchType_AllByIndex All included sheets are matched by index MatchType_AllByName All included sheets are matched by name MatchType FirstByCodeName The first matched sheets with the same code name are added MatchType_FirstByIndex The first matched sheets are added MatchType_FirstByName The first matched sheets with the same name are added MatchType_Manual Pairs were modified or added with Add 8 1 1 8 8 OutlineFlag Constants used to by the Outline property in Settings OutlineFlag_DifferentRows Hide different column linked some cells are different OutlineFlag_DuplicateRows Hide rows with either duplicate keys or records only in database mode OutlineFlag_Enabled Enable or Disable all other Outline options OutlineFlag_IdenticalRows Hide identical rows linked without cell differences OutlineFlag_MissingCols Hide missing columns not occurring in the other worksheet OutlineFlag_MissingRows Hide missing rows not occurring in the other worksheet OutlineFlag_None No rows columns will be hidden 8 1 1 8 9 ReportType Constants used by the Report property in Settings ReportType_Hyperlinks A workbook is created range addresses are hy
35. Len Dir PROTSRC gt 0 If PROTTGT lt gt Then Debug Assert Len Dir PROTTGT gt 0 End Sub this function returns the not matched files Public Function Get_NotMatchedWorksheets sSrcFolder As String sTgtFolder As String As Variant ReDim aFiles 0 As String ReDim aNotMatched 0 As String 2000 2015 XL Consulting GmbH Switzerland oo OOOD Developer Edition d Dim sFile As String Dim i j As Integer For i 1 To 2 sFile Dir Choose 3 i sSrcFolder sTgtFolder amp xls Do While Len sFile gt 0 ReDim Preserve aFiles UBound aFiles 1 aFiles UBound aFiles sFile sFile Dir Loop For j 1 To UBound aFiles If Len Dir Choose i sSrcFolder sTgtFolder aFiles j 0 Then ReDim Preserve aNotMatched UBound aNotMatched 1 aNotMatched UBound aNotMatched Choose i sSrcFolder sTgtFolder amp aFiles j End If Next j ReDim aFiles 0 Next i Get_NotMatchedWorksheets aNotMatched End Function this function returns the differences of a project if on pair is compared all detailed differences are returned if multiple pairs are compared the total differences per pair are returned Public Function GetDifferences oProj As Project As String Dim p As Pair Dim sMsg As String Dim i As Integer If oProj Pairs Count 1 Then one pair return detailed differences sMsg oProj Results SumText Else multiple pairs r
36. Synkronizer 11 User Manual 2000 2015 XL Consulting GmbH Switzerland 2 Synkronizer 11 User Manual Table of Contents Foreword Part Welcome 7 NEGA gu e 7 2 GAUA ENEA 8 Part Il Select Tab 11 1 Workbooks AAO 12 2 NN Obi e aea a aa aa EE a aek de ezan BOA Ja EAE Eu EAO Databases General Information About DeGtGRGSSS rrura 19 LINK Data T OO BAA A A OT 3 RID ON cs caved sive deco ae E era rea E EE eea erea PEOJECE etzaik EA EEEk aa TA E Ek a aiak Compare eU ALGO AAE Compare Formats COM Pare GU EAEE TOONS Ba LANA Tools Report NBEAN Start Compare files Part Ill Results Tab A tees gots ani cou E A E tees deca seed dousevesents deed ceuenvexegteaees couersesent unas 36 SAGU GLERA 39 3 RIDDON iii ri A 42 Part IV Project Tab 45 Ti Pr 6 CS iii ii ri 45 Fil GEUK 46 A A NT 49 Syste AAA 51 License Manage a E area EE EEE A E E eee 52 Part V Install Uninstall 54 ALE GUE 55 2 Network OS a AE ns 55 AGING NODS ii ii 57 Ezg LABE GUE LA AAE 61 2000 2015 XL Consulting GmbH Switzerland Contents 3 Part VI Registration 64 Manual Activation 10 a canas 65 2 Deactivate Software ui a Ad EEOAE PUDEL LE EIEEE 67 Part VII Problems Errors 72 ST AAA 73 Part VIIl Developer Edition 78 1 Visual Basic for Applications VBA NA 78 Object Model Reference Application ODE ATEN REA eed niin she nid EA ET A a AEk Methods ri AA ta EE urre KEATS visio iat db A A adie et baiaren Op POJEC Lui AA AAA
37. The button above will open a webpage on the activation server with instructions to create a de activationkey That key must be entered in the field below Activation Key License Register A data string that contains the activation data specific to your hardware is displayed and copied to your clipboard 8 Click on Open browser or the following link http www synkronizer com generate activation key The following page appears 2000 2015 XL Consulting GmbH Switzerland 6 2 Registration synkronizer ee Gei Software Activation 1 Enter the data string containing the activation deactivation data in the field below ACT DEMOV6WKIRRAL3HLLC7MA 0001 4C4C4544 0047 3210 8052 C7C04F46354A 8A5DA69B DELL INC PRECISION T3600 01CCCEC9 3 ZUERICH 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 ar 9 Paste the datastring into the input field 1 and click on the activate symbol 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 2D36NCI QHKYYJD B4AY
38. The individual network users install Synkronizer normally on their local computer and point in the license manager to the license file that is located in the network folder 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 Adding nodes Once the license is activated you can add the nodes network users who will work with Synkronizer This can be done from any workstation installed on Synkronizer as long as the user has write access to the license file Add Individual Nodes workstations users 1 Select the Project System License Manager 2000 2015 XL Consulting GmbH Switzerland s Synkronizer 11 User Manual 2 Click on Manage 3 Click on the tab Nodes Info P License x Computer Edition MYPC Trial Edition License s PRO NETWO3Z CNP4TOS K4EG3LM x Add Key Info Nodes Info Name Value Valid License NETWO3Z CNP4TOS K4EG3LM oK Computers 10 Install Servervirtual Edition EditionPro_Release11 Activation ActKey XXX Status ActkeyInvalidcode XXX Hardware UUID 812A 87 C3E6 FBD6 A7SA EA38F6CAA83D OK DISK 646FB2FB OK SYST MYPC SYNKRONIZER SERVER OK Activate License Manage 4 Now you can add the computer users who will work with Synkronizer Simply enter the correct netw
39. Valid Checks if files are valid and pairs exist and are all valid Pairs Container for the Pairs worksheets of the project ReportWorkbook Results Report workbook when created Container for the combined Results of all Pairs Settings Container for all Settings of the project Events Name Progress Raised during comparison execution to indicate running subprocess 8 1 1 2 1 Methods 8 1 1 2 1 1 Close Closes the project results Optionally closes the workbooks without saving Syntax expression Close CloseFiles DisplayUndo expression A variable representing a Project object Parameters CloseFiles Boolean Closes the compared files workbooks DisplayUndo Boolean Optional If true and highlighting or outline are activated the user will be given the option to undo Return Value none 2000 2015 XL Consulting GmbH Switzerland 82 Synkronizer 11 User Manual 8 1 1 2 1 2 8 1 1 2 1 3 Remarks If you want the files to be saved with possible highlighting and outline you must do so prior to calling the Close method Execute Executes the compare process Syntax expression Execute expression A variable representing a Project object Save Saves the project s configuration to the specified path Syntax expression Save FilePath expression A variable representing a Project object Parameters FilePath String Path and file name of the project file File extension is xml Return Value
40. able representing a WSOptions object Example This example links the rows and columns 1 on 1 With oProj Pairs Item 0 WSOptions WSOptionFlag_Rowslonl WSOptionFlag_Colslonl End With 8 1 1 6 Settings Object Container for a group of properties that specify how files will be compared and what actions will be performed Methods Clear Clears all settings Properties Contents Return or sets the additional content to be compared FilterEquivalents Returns or sets the equivalent values which will be ignored Filters Returns or sets a series of options specifying which differences to filter ignore FilterTolerance Returns or sets a number below which numeric differences will be ignored Formats Returns or sets a series of options specifying which formatting properties to compare Highlight Returns or sets a variable specifying which highlighting to perform Outline Returns or sets a series of options specifying which group of rows and columns to show or hide Report Returns or sets a variable specifying which report to create Events none 2000 2015 XL Consulting GmbH Switzerland Synkronizer 11 User Manual 8 1 1 6 1 Methods 8 1 1 6 1 1 Clear Clears all settings Syntax expression Clear expression A variable representing a Settings object Return Value none 8 1 1 6 2 Properties 8 1 1 6 2 1 Contents Returns or sets a series of options specifying which formatting properties to compare
41. air which contains a database structure 2 Click with the mouse on the check box Compare as a database Customer List Customer List Options Ss Database settings Compare as worksheet _ Compare with link options Compare as database Rang s optional seleQyrange select range 4 The tab Database settings appears Now you have to define the primary key by which the databases will be compared To do this select the necessary columns and confirm your selection with the Plus button Customer List Customer List E O Options E Database settings Define a primary key and select options 4 2 City v Headers row 1 1 Customer Na _ Relaxed keys _ Sort records Group records Add ID column Important The field names must be identical otherwise the database can not be compared If no identical field name is the following message appears 2000 2015 XL Consulting GmbH Swizerland te Synkronizer 11 User Manual Options E Database settings Define a primary key and select options No items Headers row ae No matching headers _ Relaxed keys found on row L Sort records L Group records Add SynkID column 5 Finally you can set the database options Customer List Customer List s O Options SA Database settings Define a primary key and select options 3 Country v Headers row Es 1 Customer Name Y Relaxed keys 2 City Y Sort records _ Group records Add ID c
42. ase identical formulas will have different New filter functions Invocation via command line With the Developer Edition you can control Synkronizer via the command line The Developer edition will earliest be shipped sometime after summer 2014 Editions There are three different editions of Synkronizer 11 available The functionality of these editions is described in the following table Whole cell range of worksheets Highlight differences Creation of a difference report Transfer differences Results screen Use filters Compare cell values Compare cell formulas Compare cell comments Compare text formats Compare cell formats Compare Excel names Outline differences Compare multiple worksheets Database comparison Use Projects Call by command line VBA access Developer Trial 2000 2015 XL Consulting GmbH Swizerland Welcome 9 Price EUR 79 EUR 149 Free With the trial edition only the cell range Al until Z100 can be compared 2000 2015 XL Consulting GmbH Switzerland Select Tab 11 2 Select Tab When Synkronizer is started the main form or Select tab is displayed The form is used to select entire projects or two Excel files that are to be compared furthermore virtually all comparison options filters and actions can be defined in this form BEA x m a Ribbon elln this section are projects comparison tool and action options available You deen open save or reset projects defi
43. aveCopyAs sFileSrc Else 2000 2015 XL Consulting GmbH Swizerland Developer Edition 115 Files Workbook sideID src Save End If If Files Workbook sideID tgt FullName lt gt sFileTgt Then Files Workbook sideID tgt SaveCopyAs sFileTgt Else Files Workbook sideID tgt Save End If End If Close CloseFiles Tru DoEvents End With DisplayUndo False Set oProj Nothing DoEvents End If Next i creat nd message in log file If sFolderLog lt gt Then Print 1 Print 1 Comparison time amp Format Timer t0 00 00 s Reset End If display end message SynkFolders finished amp vbLf amp _ n 0 amp workbooks without differences amp vbLf amp _ n 1 amp workbooks with differences s reports theExit Reset Set oProj Nothing Set snk Nothing Exit Function theError Dim sErr As String sErr Err Number amp amp Err Description On Error Resume Next If Not oProj Is Nothing Then oProj Close True False End If SynkFolders sErr Resume theExit End Function this function compares one source file against all files of a target folder 2000 2015 XL Consulting GmbH Switzerland Paramater description sFileSrc Source file sFolderTgt Folder with target files to be compared 116 Synkronizer 11 User Manual bHighlight Select True if differences s
44. cell differences can be changed As required the cell differences can be sorted or grouped differently The cell differences are by default sorted by rows and within the rows by columns The cell differences are sorted first by columns and within columns to rows If you choose this option the differences are grouped by rows The differences can be transferred individually per line or in groups 2000 2015 XL Consulting GmbH Switzerland Results Tab a 4 p T ez Group by row E GA Src Tgi Source Target 47 7 bo E 17 1 2x B17 B17 Stuttgart Aachen D17 D17 Rita Miller Berndt Schneider 29 2 1x 52 5 1x 76 71 2x 86 81 2x Group by If you choose this option the differences are grouped by columns The column differences can be transferred individually per line or in groups q gt EE EA e EZ Group by column Ge Src Tgi Source Target BO 3x D D 6x D7 D7 Elke Bischof Hanna Moos D17 D17 Rita Miller Berndt Schneider D29 D28 Holger Schmidt Peter Franken D52 D51 Philippe Cr tien Jean Fresni re D76 D75 Josh Gallagher Hari Kumar D86 D85 Brit Joyner Helvetius Nagy This option is extremely useful when you are working with databases and need to update cell differences of a column With just one click all the differences of a column can be transferred t Settings Depending on the difference type different options can be set which are described below e Insert entire row or c
45. d ID or index for the pair Return Value none 2000 2015 XL Consulting GmbH Switzerland Developer Edition 8 1 1 4 2 Properties 8 1 1 4 2 1 Count Returns the number of members in the collection Read only long value Syntax expression Count expression A variable representing a Pairs object 8 1 1 4 2 2 IsValid Returns true if pairs exist and each pair is valid Read only Boolean value Syntax expression IsValid expression A variable representing a Pairs object 8 1 1 4 2 3 Item Returns a single item Pair from the collection Pair object Syntax expression Item index expression A variable representing a Pairs object Parameters index Long A valid ID or index for the pair 8 1 1 4 2 4 Matchinclude Returns or sets options specifying types of worksheets to include by AddMatched method MatchIncludeFlag constant Syntax expression MatchInclude expression A variable representing a Pairs object Example This example adds all worksheets with the same name Protected and hidden sheets are included With Pairs include protected and hidden sheets MatchInclude MatchIncludeFlag ProtectedSheets MatchIncludeFlag HiddenSheets 2000 2015 XL Consulting GmbH Switzerland e Synkronizer 11 User Manual match all sheets with same name MatchType MatchType AllByName add sheets AddMatched End With 8 1 1 4 2 5 MatchType Returns or sets a variable specifying how worksheets will be
46. der otherwise enter a file name You can enter an absolute path file or a path file name relative to the parent directory dir tgt Target folder file Required Folder of the target files or the name of the target file If you want to compare all files of a folder you need to enter a folder otherwise enter a file name You can enter an absolute path file or a path file name relative to the parent directory dir xml Synkronizer Project file Optional Name of the Synkronizer project file All other settings like Contents Formats or Filters are taken from this project file If no project file is defined the Synkronizer default settings are used rep Folder for difference reports Optional Name of the folder in which the difference reports are saved If this parameter is empty the difference reports are written into the parent folder dir log Name of Synkronizer log file Optional Name of the log file If this parameter is defined a log file will be created Note If the folders or file names contain spaces and you are working with batch files and or Windows shortcuts you need to use apostrophes double quotes for addressing the folders file names 2000 2015 XL Consulting GmbH Swizerland 138 Synkronizer 11 User Manual 8 2 2 Tools matching m Optional Matching options You can define how the worksheets shall be matched The following options are allowed 1 Compare all worksheets by name default setti
47. e MatchIncludeFlag ProtectedSheets MatchIncludeFlag HiddenSheets match all sheets with same name MatchType MatchType AllByName add sheets AddMatched End With 8 1 1 4 1 2 AddPair Adds a pair to the Pairs collection Syntax expression AddPair Sheet0 Sheet1 Range0 Range1 DBoptions WSoptions DBrow DBkeys expression A variable representing a Pairs object 2000 2015 XL Consulting GmbH Switzerland Synkronizer 11 User Manual 8 1 1 4 1 3 8 1 1 4 1 4 Parameters Sheet0 String A worksheet name of the source file Sheetl String A worksheet name of the target file Range0 String A range address of the source worksheet Rangel String A range address of the target worksheet DBOptions DBOptionFla If the worksheet is a database enter database options g here WSoptions WSOptionFla If the worksheet should be linked 1 on 1 enter linking g options DBRow Long If database comparison enter row with database column field names DBKeys String If database comparison enter the column number s which contain s the primary key s Separate numbers by semicolon s Return Value Returns a Pair object Clear Clears the pairs collection Syntax expression Clear expression A variable representing a Pairs object Return Value none Remove Removes specified pair from the collection Syntax expression Remove index expression A variable representing a Pairs object Parameters index Long A vali
48. e pairs and clicking on the Delete button No worksheets found 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 details please refer to the chapter Automatically pair worksheets Ranges O Worksheets A Choose the worksheets you want to compare det w Source worksheet Target worksheet No items No items gt Customer List Customer List O Options Compare as worksheet Compare with link options Compare as database Ranges optional select range If you do not want to compare the entire worksheet you can define the desired range Proceed as follows 1 Select the worksheet which contains the range you want to compare 2 Under the worksheet pair the options area appears 3 options Now you can specify the desired range by using of the range buttons 3 The range address is also listed next to the worksheet name 2000 2015 XL Consulting GmbH Switzerland Select Tab 17 Note You can also enter named ranges Multiple ranges are not allowed 2 2 3 Databases If your worksheets contain a database structure then we recommend a database comparison If you do not know exactly what a database is and how it works please read the chapter General Information About Databases first Proceed as follows to enter the database options 1 Select the worksheet p
49. ed under inactive then go to the ComAdd in dropdown and activate it Now the Synkronizer logo should be visible in the toolbar If not proceed as follows 1 Open the Windows Explorer and select the following directory C Program Files Synkronizer Synkronizer 11 2 Double click the file AddinLoad bat 3 Select 1 Load in Current User HKCU 4 Close the window Excel 2013 Protected sheets Excel 2013 is very slow when working with protected sheets this may cause annoying delays when opening projects or adding passwords Excel 2007 Digital signature is invalid and cannot be trusted Excel 2007 cannot handle the latest digital signatures and only supports the now deprecated shal standards It has been superseded by newer versions of the Office suite http support2 microsoftcom lifecycle p1 8753 Proceed as follows to fix this bug e Make sure you have installed the latest service pack Excel 2007 SP3 12 0 6683 5002 with MSO 12 0 6683 5000 e Make sure that XL Consulting GmbH is listed under Trusted Publishers Do the following Click Windows Start button 2000 2015 XL Consulting GmbH Switzerland Problems Errors Enter cmd Enter certmgr the Certificate Manager will be opened Select Trusted Publishers If it is not listed then add XL Consulting GmbH to trusted publishers as follows Close Excel Open Windows Explorer Navigate to installation folder C Program File
50. ences Highlight HighlightType WithReset show only rows columns with differences Outline OutlineFlag Enabled _ OutlineFlag DifferentRows OutlineFlag MissingRows OutlineFlag_ OutlineFlag MissingCols also compare comments and names Contents ContentFlag_ Comments ContentFlag_Names also compare also font formats Formats FormatFlag Enabled FormatFlag Font End With start Synkronizer Execute get the message string before closing the project sMsg GetDifferences oProj save project If snk DisplayUI False Then Save FILEPRJ End If close project CloseProject 2000 2015 XL Consulting GmbH Switzerland Developer Edition End With display message sgBox sMsg vbOKOnly vbInformation Synkronizer Exit Sub Err Example sgBox Err Description vbExclamation Synkronizer End Sub Example 2 Compare a project Public Sub Example2 Dim oProj As Synkronizer Project Dim sMsg As String On Error GoTo Err Example check if defined constants are valid Check Folders File get access to the Synkronizer application object InitSnk load Synkronizer project Set oProj snk OpenProject FILEPRJ check if source target files are valid Debug Assert oProj Files IsValid start Synkronizer oProj Execute get the message string before closing the project sMsg GetDifferences oProj
51. erations 8 1 1 8 1 Content Flag Constants used to by the Contents property in Settings ContentFlag Comm Tf this flag is set the comments will be compared ents ContentFlag_Names If this flag is set the Excel names will be compared ContentFlag None No additional content will be compared 8 1 1 8 2 DBOptionFlag Flags for pair s DBoptions DBOptionFlag_AddID Adds an ID in the last column of the database to aid sorting and filtering DBOptionFlag Group Records are grouped by result after comparing DBOptionFlag_Relaxed When selecting this option primary key differences in spaces and upper Keys lower case are ignored DBOptionFlag_Sort Records are sorted on fields in primary key before comparing 8 1 1 8 3 FilterFlag Constants used to by the Filters property in Settings FilterFlag_Constants All constants are ignored Only formulas are compared 2000 2015 XL Consulting GmbH Switzerland 106 Synkronizer 11 User Manual FilterFlag_Datatype Differences in datatype are ignored Use to ignore differences between date values and string values representing dates FilterFlag_Enabled Enable or disable all other filters options FilterFlag_Formulas All formulas are ignored Only constants are compared FilterFlag_HiddenCo Data in hidden columns will be ignored Is FilterFlag_HiddenRo Data in hidden rows will be ignored ws FilterFlag_None No filters will be used FilterFlag SameValu Different cell values form
52. eturn total differences per pair i l For Each p In oProj Pairs sMsg sMsg amp p SheetName sideID src amp vbTab amp _ p Results Sum amp vbNewLine ZE gd A If i gt 20 Then Exit For Next p 2000 2015 XL Consulting GmbH Switzerland 112 Synkronizer 11 User Manual Just display the first 20 pairs If i gt 20 Then sMsg sMsg amp amp vbNewLine End If End If GetDifferences sMsg End Function this function compares the files of two folders 1 Parameter description Paramater description sFolderSrc Folder with source files to be compared sFolderTgt Folder with target files to be compared bHighlight Select True if differences should be highlighted sFolderLog If difference reports are needed enter folder Optional sFolderLog If a log file is needed enter folder Optional Public Function SynkFolders sFolderSrc As String sFolderTgt As String bHighlight As Boolean _ Optional sFolderRep As String _ Optional sFolderLog As String As String Dim oProj As Synkronizer Project Dim sFile As String Dim aFiles As String Dim i As Integer Dim j As Integer Dim sFileSrc As String Dim sFileTgt As String Dim sFileRep As String Dim sFileLog As String Dim vNotMatchedFiles As Variant Dim n 0 To 1 As Long Dim t0 As Date check if folders are valid Debug Assert Len Dir sFolderSrc vbDirectory Debug Assert Len Dir sFolde
53. fferent in the underlying worksheets Differences in calculated cell formulas The results of the cell formulas are different the entered cell formulas are identical Different cell formats Appears only when formats are compared Different comments Appears only when comments are compared Different names Appears only when names are compared For each difference the detailed information can be displayed in the lower part of the form Just click with the mouse on the desired type of difference and you will find further details below In the following example the difference type Entered values amp formulas is selected Nine differences were found in total in the upper part of the form The detailed information of each difference is displayed in the lower part of the form 2000 2015 XL Consulting GmbH Swizerland 38 Synkronizer 11 User Manual Synkronizer 11 E A SELECT RESULTS 00 a Y Ea Y Filters Report Highlight Outline All X sheets Filters Tools Refresh Worksheet E zz EE Customer List oi 32M di Customer Profile 128 27 e Customers by Countries H e 2 Sales Summery A rd Sales by Categories 4 2 Sales by Countries fifi 4 2 Sales by Categories by Products 8 Sales Detail YO 15 2 Compare with Last Month d al e Product Catalog e 26 1 e Employee Profile 22122 3 5 E NK EE ta d E Src Tgi Source Stuttgart Rita M lle
54. first sheets compare formats comments names use filters f xreate a report Public Sub Example Dim oProj As Synkronizer Project Dim sMsg As String On Error GoTo Err Example check if defined constants are valid Check Folders File get access to the Synkronizer application object InitSnk create project Set oProj snk NewProject With oProj define files Files Load FILESRC FILETGT define first worksheet of each file With Pairs MatchType MatchType FirstByName AddMatched End With With Settings also compare comments and names Contents ContentFlag_Comments ContentFlag_Names compare also font formats Formats FormatFlag Enabled FormatFlag Font use some filters Filters FilterFlag Enabled FilterFlag StringCase FilterFlag StringSpac 2000 2015 XL Consulting GmbH Switzerland 130 Synkronizer 11 User Manual FilterTolerance 0 01 FilterEquivalents yes ja no nein create a report Report ReportType Standard nd With E compare files Execute If Not ReportWorkbook Is Nothing Then delete report if it already there If Len Dir FILEREP gt 0 Then Kill FILEREP T save report With ReportWorkbook SaveAs file name FILEREP Close SaveChanges Fals End With End If get the message string before closing the project
55. for Sales Status of Current Activities Status of Current Activities 2000 2015 XL Consulting GmbH Switzerland ta Synkronizer 11 User Manual 2 2 1 Settings Automatically pair worksheets When you select two files Synkronizer automatically tries to pair the worksheets The pairing settings of the worksheets are set in the following drop down element O Worksheets a Choose the worksheets you want to compare B gt Include sheets ft worksheet ems Hidden ner List CodeName Match on workbook change f select range V A First mer Profile Manual mers by Countries Defaults Summery Load Save Reset py Categories CRIBS bi Tz erez CSIR bo Casinteine The following options can be changed in this item e Include sheets These types of worksheets are also listed e Match by Depending on the setting the worksheets are matched by their names index position within the file or codename internal worksheet name e Match on workbook change Here you can specify how the worksheets are paired If you select all all worksheets with the same name are transferred to the worksheets list by selecting first only the first worksheets are transferred If the worksheets are not automatically paired you can switch to Manual 2000 2015 XL Consulting GmbH Swizerland Select Tab 15 Manually pair worksheets The worksheets can also be paired manually This can be done in
56. 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 2000 2015 XL Consulting GmbH Switzerland Project Tab 4 2 Settings In this menu general settings can be changed Neues Projekt HEHA x PROJECT SELECT RESULTS De a O New Language E Open English X E Save Project Settings A Save As EO Load defaults to project Save project as defaults O Reload a da O Reset defaults to factory Recent Undo settings Settings Select what Synkronizer should do when it releases worksheets ZO Show dialog Highlighting Show dialog Outlining Highlighting colors X Columns 4 Duplicate records 4 4 Duplicate keys Rows 4 4 Entered values 8l formulas 4 4 4 Calculated values 4 Formats O Reset defaults to factory Color themes MITO Project Settings Here you can save reload or reset your personal project settings The following options are available e Load defaults to project Your preferred personal preferences are loaded see Save project as default e Save project as default With this button you can save your preferred settings All settings regarding contents formats 2000 2015 XL Consulting GmbH Switzerland s Synkronizer 11 User Manual filters report highlighting and outline are saved
57. he target file needs to be saved Patho String Optional Specifies the path if the source file needs to be saved in a different location Path1 String Optional Specifies the path if the target file needs to be saved in a different location Return Value none 8 1 1 3 2 Properties 8 1 1 3 2 1 FileName Returns the full path and file name of either of the Files Read only String value Syntax expression file name id expression A variable representing a Files object Parameters id sideID Selects the source or target file 8 1 1 3 2 2 IsValid Returns true if both files are loaded and can be compared Read only Boolean value Syntax expression IsValid 2000 2015 XL Consulting GmbH Switzerland s Synkronizer 11 User Manual expression A variable representing a Files object 8 1 1 3 2 3 Window 8 1 1 3 2 4 8 1 1 4 Returns the window object of either of the files Read only Window object Syntax expression Window id expression A variable representing a Files object Parameters Name id Workbook sidelD Selects source or target file Returns the workbook object of either of the files Read only Workbook object Syntax expression Workbook id expression A variable representing a Files object Parameters NETAS id Pairs Object Collection of pair objects Methods Name AddMatched AddPair Clear Remove Properties NEME Count IsValid Item MatchInclude side
58. he worksheet s is are newly compared Report You can create a difference report at any time Proceed as follows 1 Click the arrow below the report icon 2 Select the difference report you want to create 3 The difference report will be generated Highlight Here you can choose whether only certain types of difference are to be shown Proceed as follows to display only certain types of difference Outline Here you can decide whether only certain types of difference are to be shown Proceed as follows to display only certain types of difference 1 Click the arrow below the Outline icon 2 Select Enable outlining the Outline icon changes 3 Click on the difference types to be displayed 4 Close the Outline dialogue 5 Click on the Outline icon 6 Only the selected different types will be displayed 2000 2015 XL Consulting GmbH Switzerland Results Tab e Refresh gt By clicking on this icon the files are compared anew You can choose whether all worksheets or only the active worksheet shall be re compared 2000 2015 XL Consulting GmbH Swizerland Project Tab as 4 Project Tab In this tab you can manage projects change project settings and find information about your Synkronizer edition Se ee EEE x the following tasks can be determined PROJECT SELECT RESULTS D0 within this tab O New Recent projects dE Manage projects E Open 1 Current Activities xml b
59. hould be highlighted sFolderLog If difference reports are needed enter folder sFolderLog If a log file is needed enter folder Optional Public Function SynkSrcFolder sFileSrc As String _ sFolderTgt As String bHighlight As Boolean _ Optional sFolderRep As String Optional sFolderLog As String Dim oProj As Synkronizer Project Dim aFiles As String Dim i As Integer Dim sFile As String Dim sFileTgt As String Dim sFileRep As String Dim sFileLog As String Dim n 0 To 1 As Long Dim t0 As Date check if files folders are valid Debug Assert Len Dir sFileSrc vbDirectory Debug Assert Len Dir sFolderTgt vbDirectory If sFolderRep lt gt Then Debug Assert Len Dir sFolderRep vbDirectory If sFolderLog lt gt Then Debug Assert Len Dir sFolderLog vbDirectory tO Timer On Error GoTo theError check if defined constants are valid Check Folders File get access to the Synkronizer application object InitSnk create log file If sFolderLog lt gt Then sFileLog sFolderLog synkronizer log _ amp Format Now dd _HH MM SS amp txt Reset Open sFileLog For Output As 1 Print 1 Synkronizer Logfile Print 1 A Print 1 Print 1 Date Format Date yyyy mm dd Print 1 Time amp Format Time hh nn ss Print 1 Optional As String yyyy mm 2000 20
60. if the pair is valid Range Returns the range object for the specified member of the pair RangeAddr Returns or sets an optional range address for the specified member of the pair Results Returns the number of differences found Sheet Returns the worksheet object for the specified member of the pair SheetName Returns the name of the worksheet for the specified member of the pair WSOptions Returns the row column linking options Remarks Specify a Range Normally the entire data range of the sheet is used but you can limit the range by using the RangeAddr property Compare as Database You can compare the sheets as databases if 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 If you want the records sorted or the results grouped use DBOptions 8 1 1 5 1 Methods 8 1 1 5 1 1 Activate Activates the pair Syntax expression Activate 2000 2015 XL Consulting GmbH Switzerland 2 Synkronizer 11 User Manual expression A variable representing a Pair object 8 1 1 5 2 Properties 8 1 1 5 2 1 8 1 1 5 2 2 DBKeys Returns or sets a semicolon delimited list of column indices used as primary key for database String value Syntax expression DBKeys expression A variable representing a Pai
61. is example shows only the differences Identical rows are hidden With Settings Outline OutlineFlag Enabled _ OutlineFlag DifferentRows OutlineFlag MissingRows OutlineFlag_Dupl OutlineFlag MissingCols End With 2000 2015 XL Consulting GmbH Switzerland Developer Edition so 8 1 1 6 2 8 Report Returns or sets a constant specifying which Report to create Uses ReportType constants Syntax expression Report expression A variable representing a Settings object Example This example creates a difference report With Settings Report ReportType Standard End With 8 1 1 7 Results Object Container for the number of differences found Methods none Properties ArrayCounts Returns an array with the overall differences found ArrayDetails Returns an array with the individual differences found DifferentComments Returns the number of cells with cell comments DifferentContents Returns the number of cells with different entered values and formulas DifferentFormats Returns the number of cells with different formats DifferentNames Returns the number of different Excel names DifferentValues Returns the number of cells with different calculated values or results DuplicateKeys Returns the number of duplicate keys database only DuplicateRecords Returns the number of duplicate records database only MissingCols Returns the number of missing columns or fields MissingRo
62. j Settings Contents And ContentFlag Comments Then sText SumTextByType ResultType DifferentComment sLine sLine amp String 9 Len sText amp sText End If If CBool oProj Settings Contents And ContentFlag Names Then sText SumTextByType ResultType DifferentName sLine sLine amp String 9 Len sText amp sText End If Print 1 sLine End With Next p 2000 2015 XL Consulting GmbH Switzerland 12 Synkronizer 11 User Manual total differences If oProj Pairs Count gt 1 Then With oProj Results sText Total sLine sText String 32 Len sText sText CStr Sum sLine sLine amp String 7 Len sText amp sText sText SumTextByType ResultType MissingCol sLine sLine amp String 9 Len sText amp sText sText SumTextByType ResultType DuplicateRec sLine sLine amp String 9 Len sText amp sText sText SumTextByType ResultType DuplicateKey sLine sLine amp String 9 Len sText amp sText sText SumTextByType ResultType MissingRow sLine sLine amp String 9 Len sText amp sText sText SumTextByType ResultType DifferentContent sLine sLine amp String 9 Len sText amp sText sText SumTextByType ResultType DifferentValue sLine sLine amp String 9 Len sText amp sText If CBool oProj Setting
63. lD Selects source or target file Adds matched pairs using MatchType and Matchinclude properties Adds a pair to the Pairs collection Clears the pairs collection Removes specified pair from the collection Returns the number of members in the collection Returns true if pairs exist and each pair is valid Returns a single item pair from the collection Returns or sets options specifying types of worksheets to include by 2000 2015 XL Consulting GmbH Switzerland Developer Edition 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 passwords needed to access protected sheets Events none 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 Clear Method 8 1 1 4 1 Methods 8 1 1 4 1 1 AddMatched Adds matched pairs using MatchType and Matchinclude properties Syntax expression AddMatched expression A variable representing a Pairs object Example This example adds all worksheets with the same name Protected and hidden sheets are included With Pairs include protected and hidden sheets MatchInclud
64. le name sFileRep End With End If End If Else no differences noted close report without saving n 0 n 0 1 End If save target file if differences are highlighted If bHighlight Then If Files Workbook sideID_tgt FullName lt gt sFileTgt Then Files Workbook sideID tgt SaveCopyAs sFileTgt Else Files Workbook sideID tgt Save End If Files Workbook sideID tgt Save End If Close CloseFiles True DisplayUndo False DoEvents End With Set oProj Nothing Next i return value SynkSrcFolder finished amp vbLf amp _ n 0 amp workbooks without differences amp vbLf amp _ n 1 amp workbooks with differences s reports writ nd message in log file If sFolderLog lt gt Then Print 1 Print 1 Comparison time amp Format Timer t0 00 00 s Reset End If theExit Reset Set oProj Nothing Set snk Nothing 2000 2015 XL Consulting GmbH Swizerland Developer Edition 119 Exit Function theError im sErr As String Err Err Number amp Err Description n Error Resume Next f Not oProj Is Nothing Then HOn YD oProj Close True False End If SynkSrcFolder sErr Resume theExit End Function this procedure writes all project differences in a new workbook Public Sub DumpDetails Project oProj As Synkronizer Project Dim wkb As Workbook Dim
65. license file lt xml version 1 0 encoding UTF 16 standalone yes gt lt SynkLic gt 4 Save the file 5 Start Excel 6 Start Synkronizer and open the License Manager with the following command Project System License manager The following form appears Pa License x Trial Edition No License key Version 11 0 50120 0 License file Type Network 7 MYPC Synkronizer Server License xml License Register Click on the path button see arrow and choose the network directory Note The network directory must be an UNC Uniform Naming Convention folder which looks as follows ComputerName SharedFolder Resource license xml 7 Sign Up and activate the software 2000 2015 XL Consulting GmbH Switzerland 5 2 1 Install Uninstall 8 Add the network users who work with Synkronizer see section below Installations of network users The installation of the network users is very easy The individual network users install Synkronizer normally on their local computer After that they need to start the license manager and select the License file on the shared folder If the software is activated and the network users are assigned the users can immediately begin working with Synkronizer Scheme Network LE LE Network user 2 Network user 3 LE E Network user 1 Network user 4 Shared Folder on a Network License xml The license file License xml is located on a shared network folder
66. linking icon appears next to the worksheet pair Sales Summery Sales Summery di Sales by Categories Sales by Categories EZA Sales by Countries Sales by Countries 2 2 5 Passwords With Synkronizer password protected worksheets can also be compared The disadvantage is that password protected worksheets are not updated and that the differences can not be marked or outlined But if you know the password passwords of the worksheet s you can unlock the worksheets as follows 1 Open the files which contain password protected worksheets 2 If the worksheets should not appear in the worksheets list you might need to enable the Protected option in the pairing worksheet element 2000 2015 XL Consulting GmbH Switzerland 22 Synkronizer 11 User Manual S Synkronizer 11 i da x SELECT RESULTS 00 a TORS H Project Compare Tools O Workbooks v Worksheets A Choose the worksheets ugan to compare tt worksheet Protecte gt Match by y Name Index CodeName Match on workbook change VIA First Manual Select Protected and click the Add worksheets button the protected worksheets are now displayed 3 The password protected worksheets are displayed in green successfully unlocked worksheet or red protected worksheet In addition a tab Unprotect appears in which you can enter the password to unlock the worksheets 2000 2015 XL Consulting GmbH Switzerland
67. mber It is compared whether the number format of the cells is different 2000 2015 XL Consulting GmbH Switzerland Select Tab ar e Alignment Compares the alignment of the cells left center etc e Font All the font formattings are compared font name size color underline etc e Border The cell borders are compared e Fill The background color of the cells is compared e Protection The cell protection and visibility of the cell to be compared Proceed as follows to compare the cell formats 1 Click on formats 2 Select Enable formats The format icon changes from gray to black 3 Select all of the formats that you want to compare In this example the number formats and font sizes are compared S Synkronizer 11 BSH gt SELECT RESULTS 00 D J Contents Report E ome a Highigt P a y Enable formats x Project Number O y Alignment E Y Font O V Border Choose Fill E Protection ii 2 3 4 Compare Filters The filtering function enables you to reduce the number of differences that will be found and displayed Just click Enable filters and choose the filter s which you want to apply 2000 2015 XL Consulting GmbH Swizerland 28 Synkronizer 11 User Manual Ignore case If this box is checked differences in upper and lower case writing are ignored e g John Wayne equals to JOHN WAYNE Ignore whitespace If this box i
68. mple 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 tab 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 2000 2015 XL Consulting GmbH Swizerland Project Tab ar 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 HighlightType gt lt ShowHide gt 0 lt ShowHide gt lt Settings gt lt Files gt lt Filename0 gt C Folder SubFolder File lt Filenamel gt C Folder SubFolder File 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 s lt Filename0 gt s lt Filenamel gt For a project with pairs of individual settings the file will look like this MatchType will be set
69. nProject FILEPRJ check if source amp target files are valid Debug Assert oProj Files IsValid start Synkronizer oProj Execute create a workbook with all detail differences DumpDetails Project oProj create a workbook with all detail differences all pair differences will be written in a separate worksheet DumpDetails Pairs oProj get the message string before closing the project sMsg GetDifferences oProj close project CloseProject display message MsgBox sMsg vbOKOnly vbInformation Synkronizer Exit Sub Err Example MsgBox Err Description vbExclamation Synkronizer End Sub Example 8 compare all Excel files with the same names of two folders a difference report will be created of each file set a log file will be created 2000 2015 XL Consulting GmbH Swizerland 13 Synkronizer 11 User Manual Public Sub Examples Dim sMsg As String Paramater description sFolderSrc Folder with source files to be compared sFolderTgt Folder with target files to be compared bHighlight Select True if differences should be highlighted sFolderLog If difference reports are needed enter folder Optional sFolderLog If a log file is needed enter folder Optional sMsg SynkFolders sFolderSrc FOLDERSRC sFolderTgt FOLDERTGT bHighlight True _ sFolderRep FOLDERREP sFolderLog FOLDERLOG MsgBox sMsg End Sub
70. ne comparison options like contents al formats filters define the tools settings like report highlight outline and start the comparison process Select workbooks Open and select the workbooks to be compared Select worksheets Select the worksheets to be compared The following options are available define the worksheet types all protected hidden select and delete worksheets manually select worksheets Define worksheet options Define worksheet settings like define ranges compare worksheets as database link worksheets 1 on 1 compare as normal worksheet The individual settings are described in the following chapters Note All empty workbooks and all workbooks in protected view will be closed when the form starts 2000 2015 XL Consulting GmbH Switzerland 12 Synkronizer 11 User Manual 2 1 2 2 Workbooks O Workbooks A Choose the workbooks to compare Source workbook E E E In this section you can specify which files are to be compared in addition there are two combo boxes The first file is entered in the upper box and the second file in the lower box The Excel files can also be selected with the Open File symbols Alternatively the files can be selected using the combo boxes If you click this combo box a list of recently opened Excel files will appear Projects If you have saved the files in a project the files can als
71. ng 2 Compare all worksheets by index 1 Compare first worksheets by name 2 Compare first worksheets by index report r Optional Difference report options The following options are allowed 1 A standard difference report is created default setting 2 A difference with hyperlinks will be created 0 No difference report will be created Commands n Synkronizer CommandLine Utility help All commands and settings are displayed f Files to be compared You can see which files will be compared and which not IV The settings of the current project are displayed You can see the folders file names and comparison settings Ic Starts the comparison process You can enter ctrl c to terminate the comparison process x The Synkronizer CommandLine Utility will be closed Examples This section contains some examples on how you can compare Excel files with the Synkronizer CommandLine Utility Example 1 Compare two files 1 Start the CommandLine Utility of Synkronizer using synk exe 2 Enter the source file with the command src file name 3 Enter the target file with the command tgt file name 4 Compare the files with the command c 5 Close the Synkronizer CommandLine Utility with the command x 2000 2015 XL Consulting GmbH Switzerland Developer Edition 139 Synkronizer 11 lo Synkronizer CommandLine Utility ariables dir D Documents src tgt xml rep log ools matching 1 A1LILByName gt rep
72. ng inserted deleted rows 2000 2015 XL Consulting GmbH Switzerland Developer Edition 1 8 1 2 VBA Helper Functions Option Explicit PKKKKKKKKKKKKKKKKAKKKKKKAKKKKKKKKAKKKKAKKKKKKKKAKKAKKKKAKKKKKKKKKKKKKAKK Tx EO SYNKRONIZER 11 SO JE HELPER PROCEDURES FUNCTIONS ES EFE requires a DEVELOPER license of Synkronizer 11 E requires a reference to Synkronizer 11 Object Library a FE PK KK KKKKKKKKKKKKKKKKAKKKKKKKKKKKKKKKKKKKKAKKKKKKKKKKKKKKKKKKAKKKKAK Public snk As Synkronizer Application this wrapper procedure initializes the Synkronizer application object Public Sub InitSnk Optional bWithUI As Boolean False If snk Is Nothing Then Dim cai As COMAddIn Set cai Application COMAddIns Synkronizer Addin If Not cai Connect Then see documentation if following throws permission errors Note During setup the Addin is installed for All Users by writing the instructions to the registry under HKLM Software Microsoft Office Excel Addins Your code should always test that the ComAddin E Connect property returns TRUE Tf it returns FALSE then you can activate the addin from Comaddin Dialog If you want your code to change the ComAddin Connect property then it must be loaded from HKCU Current user To make the necessary registry changes we ve provided the AddinLoad bat batch file in the installation folder cai Connect True End If Se
73. 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 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 e The first row contains unique column names e 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 record and facilitates the sorting and synchronizing of your data The primary key does not need to be a single field e g employee ID It may consist of multiple fields e g family name first name and city to create a unique identifier and avoid duplicate keys records Following paragraphs describe the difference between a duplicate key and duplicate records Duplicate keys Duplicate keys occur when data records contain the same primary key Assuming the primary key is formed from the family and first name and given you have several customers called John Miller in your database Synkronizer will not know which data records to compare So make sure you operate with unique primary keys when working with Synkronizer Duplicate Records Redundant records Synkronizer also checks whether your database contains duplicate records or redundant rec
74. noui 2000 2015 XL Consulting GmbH Swizerland 62 Synkronizer 11 User Manual 2000 2015 XL Consulting GmbH Switzerland e Synkronizer 11 User Manual 6 Registration Upon purchase 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 In case firewall settings prevent the program s direct communication with our server this process can also be performed via website or email What happens during the registration process The registration key and some hardware codes are 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 be stored in the license file The license file is now uniquely coupled to your PC and your software is activated Registration process 1 Start Synkronizer 2 Open the license manager with the following command Project System License Manager 3 Select the tab Register Pa License x Registration Registration key required field Identification Select a question Your answer Mother s family name y Activate your license online License Register 2000 2015 XL Consulting GmbH Switzerland Registration zz 4 Enter the registration key In the case of a multi user license you must additi
75. o be opened using the menu command Project Open For details refer to the Projects section Supported file types Synkronizer can open all files that are supported by Microsoft Excel Worksheets This section displays the worksheets are displayed which are to be compared Moreover in this section worksheet specific settings such as ranges database or comparison options are defined 2000 2015 XL Consulting GmbH Switzerland Select Tab 13 5 Customers B Ho xX SELECT RESULTS M a D amp Content 3 Report 7 e E TS Formats hh Highlight o gt EY Y Filters ZG Outline q Project Compare Tools O Workbooks Y O Worksheets a Choose the worksheets you want to compare Source worksheet Target worksheet No items No items gt Customer ist Customerlist O Options 8 Compare as worksheet O Compare with link options Compare as database Ranges optional select range select range ES Customer Profile Customer Profile Customers by Countries Customers by Countries Sales Summery Sales Summery Sales by Categories Sales by Categories Sales by Countries Sales by Countries Sales by Categories by Products Sales by Categories by Products Sales Detail Sales Detail Compare with Last Month Compare with Last Month Invoice Invoice Product Catalog Product Catalog Account Statement Account Statement Employee Profile Employee Profile Top 5 Employees for Sales Top 5 Employees
76. olderTgt amp aFiles i sFileRep sFolderRep amp Difference Report amp aFiles i sFileRep Left sFileRep InStrRev sFileRep 1 amp xlsx check if target is there 2000 2015 XL Consulting GmbH Swizerland 1 Synkronizer 11 User Manual If Len Dir sFileTgt gt 0 Then create new project Set oProj snk NewProject With oProj load files Files Load sFileSrc sFileTgt match all worksheets with same name With Pairs MatchType MatchType AllByName MatchInclude MatchIncludeFlag HiddenSheets MatchIncludeFlag_ProtectedSheets AddMatched End With highlight amp create report With Settings If bHighlight Then Highlight HighlightType Standard If sFolderRep lt gt Then Report ReportType Standard End With compare Execute log differences If sFolderLog lt gt Then Print 1 aFiles 1 amp vbTab Results Sum Call Logfile PrintDiffs oProj End If If Results Sum Then if differences found create report n 1 n 1 1 If sFolderRep lt gt Then If Len Dir sFileRep gt 0 Then Kill sFileRep With ReportWorkbook SaveAs file name sFileRep End With End If Else no differences noted close report without saving n 0 n 0 1 End If save files if differences are highlighted If bHighlight Then If Files Workbook sideID src FullName lt gt sFileSrc Then Files Workbook sideID src S
77. olumn The meaning of the database options are as follows Relaxed keys When selecting this option differences in spaces and upper lower case are ignored By default the primary keys are matched case sensitive e g John Miller and JOHN MILLER Sort records The records are sorted by the primary key Group records The data are grouped The date records are grouped as follows 1 Linked records 2 New deleted records 3 Duplicate keys 4 Duplicate records redundant records Add SynkID column At the end of each database record a new column called SynkID is appended which contains the difference types You can then sort the databases by the SynkID and or filter them auto filter The following IDs are written in the SynkID column 0 0K Row with no differences 1 DIFF Row with differences 2 MISS Missed row 3 DUPKEY Duplicate keys 4 DUPREC Duplicate records redundant records 2000 2015 XL Consulting GmbH Swizerland Select Tab 1o 6 When you have completed the database settings a database icon appears in the worksheet list next to the worksheet pair Source worksheet Target worksheet gt Customer List Customer List e Customer Profile Customer Profile Customers by Countries Customers by Countries 2 2 3 1 General Information About Databases Prior to describing the types of differences we want to give a brief comparison of normal worksheets versus databases Normal worksheets do
78. olumn This option is only important if you are working with cell ranges Normally when you insert a row the row will be also inserted outside the cell range By disabling this option the rows are inserted only within the cell range This option is only applicable for missing columns and rows e Empty cells may overwrite data If this box is checked cells holding a value will be overwritten by empty cells so that the previous value is lost If this is not desired you can disable the check box 2000 2015 XL Consulting GmbH Switzerland e Synkronizer 11 User Manual Differences displayed with an at sign O If a number date or Boolean value true false is formatted as text the corresponding differences will be displayed with an at sign O E g the number 123 formatted as text will be displayed as 3 3 123 Ribbon In addition various actions can be performed in the results form With these actions you can selectively and efficiently manage the differences D 1 Filter If necessary you can define a new filter and compare anew This way you can immediately see whether you get better comparison results or not Proceed as follows to create filter and start a new comparison 1 Click on the arrow below the filter icon 2 Select Enable filters the filter icon changes 3 Click on any filter that you want to enable or disable 4 Close the filters dialogue 5 Click the Refresh icon 6 T
79. onally enter the sequence number of the license next to the registry key field 5 Please enter then the secret question 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 enter the same answer The secret answer must be at least 5 characters long The following characters are allowed A Z and 0 9 6 Click Activate your license online The registration data is now sent to the activation server and the software is activated If you have no internet connection you ll need to activate manually Please read chapter Manual Activation If you have any problems with the registration activation process please read chapter Problems with registration 6 1 Manual Activation This chapter explains how you can manually activate Synkronizer The registrations steps 1 to 6 are explained in the chapter Registration 7 If no Internet connection could be established the following screen is displayed 2000 2015 XL Consulting GmbH Switzerland zz Synkronizer 11 User Manual License x P Registration Registration key DEMOV6W KIRRAL3 HLLC7MA Identification Select a question Your answer Place of birth v ZUERICH Activate your license online Manual Activation ACT DEMOV6WKIRRAL3HLLC7MA 8881 4C4C4544 8847 3218 8852 C7C84F46354A 8A5DAGIB DELL INC PRECISION T3688 81CCCEC9 3 ZUERICH Open browser
80. onizer CommandLine Utility Variables dir D Documents sre tgt xml rep log Tools matching 1 lt AL1LByName gt report 1 Standard gt sre Source tgt Target E Direct command for batch file or Windows shortcut C Program Files Synkronizer Synkronizer 111Synk exe dir D Documents src s Example 4 Create no difference report but a logfile Start the CommandLine Utility of Synkronizer using synk exe Enter the source file with the command src file name Enter the target file with the command tgt file name Enter the name of the logfile log file name Define report settings Enter r 0 Compare the files with the command d Close the Synkronizer CommandLine Utility with the command x NOU db D Ki b 2000 2015 XL Consulting GmbH Switzerland fr Developer Edition tar Synkronizer CommandLine Utility Variables dir D Documents sre tgt xml rep log Tools matching 1 C AllByName gt report 1 lt Standard gt sre Source Filei xlsx tgt Target File2 xlsx log synklog txt pe c gt gt gt gt gt Direct command for batch file or Windows shortcut C Program Files Synkronizer Synkronizer 11 Synk exe dir D Documents src Example 5 Compare two files using filters and formats Start Excel amp Synkronizer Define the files filters and formats which you d like to compare Save the settings as a project Close Synkronizer
81. ords These are records where all fields not just the key fields are equal If Synkronizer finds any of these records they will be highlighted in gray Duplicate records do not serve any purpose and should be deleted The comparison of databases is highly efficient The databases may be sorted or altered 2000 2015 XL Consulting GmbH Switzerland Synkronizer 11 User Manual 2 2 4 Inserting and deleting of columns and rows is allowed Synkronizer will recognize all differences between the two files Scheme Database Ana Trujillo Emparedados y helados M xico D F Antonio Moreno Taquer a M xico D F Mexico Antonio Moreno Around the Horn London UK Thomas Hardy Berglunds snabbk p Lule Sweden Christina Berglund Blanka Delikatessen Mannheim Germany Elke Bischof 8 Blondel p re et fils Sa Strasbourg France Fr d rique Citeaux 9 B lido Comidas preparadas Madrid Spain Martin Sommer Data Botero Markets Tsawassen Canada Elizabeth Lincoln Mexico Ana Trujillo B s Beverages London UK Victoria Ashworth Cactus Comidas para llevar Buenos Aires Argentina Patricio Simpson Centro comercial Moctezuma M xico D F Mexico Francisco Chang Chop suey Chinese Bern Switzerland Yang Wang 5 Com rcio Mineiro S o Paulo Brazil Pedro Afonso Cottam Holdings London UK Elizabeth Brown Degen Fr chtehandel Stuttgart Germany Rita M ller A Germany Sven Ottlieb Duplicate primary key Germany Otto M ller key col
82. ork user ID and the description in the input fields If you want to add the computer to which you are working click on Add this node 2000 2015 XL Consulting GmbH Switzerland Install Uninstall so P License x Computer Edition MYPC Trial Edition License s PRO NETWO3Z CNP4TOS K4EG3LM v Add Key Info Nodes Info a Node D Description optional EUR MKT DELL 1 Roger Paris EUR MKT DELL 2 Hilda Frankfurt Add this node Copy list Paste list 2 10 Save Cancel License Manage 5 Confirm the mutations with the Save button Add a Series of Nodes workstations users 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 2015 XL Consulting GmbH Switzerland eur mkt dell001 4 Open the Manage tab of Synkronizer e Synkronizer 11 User Manual 5 Press Paste list The list is filled with a maximum number of licensed computers pP License Computer Edition MYPC Trial Edition License s PRO NETWO3Z CNP4TOS K4EG3LM Add Key Info Nodes Info 4 Node D Description optional EUR MKT DELL 1 Roger Paris EUR MKT DELL 2 Hilda Frankfurt USA MKT HP
83. ort 1 Standard gt sre Source Filei xlsx tgt Target File2 xlsx gt c Direct command for batch file or Windows shortcut C Program Files Synkronizer Synkronizer 11 Synk exe dir D Documents src Example 2 Compare one master file with various target files 1 Start the CommandLine Utility of Synkronizer using synk exe 2 Enter the source file with the command src file name 3 Enter the target folder which contains the target files with the command tgt target folder 4 Compare the files with the command c 5 Close the Synkronizer CommandLine Utility with the command x f fal Synkronizer 11 i Gei Synkronizer CommandLine Utility ariables dir D Documents sre tgt xml rep log ools matching 1 lt A1L1ByName gt report 1 lt Standard gt sre Source Filei xlsx tgt Target c Direct command for batch file or Windows shortcut C Program Files Synkronizer Synkronizer 11 Synk exe dir D Documents src 2000 2015 XL Consulting GmbH Swizerland 140 Synkronizer 11 User Manual Example 3 Compare all files in two folders 1 Start the CommandLine Utility of Synkronizer using synk exe 2 Enter the source folder with the command src source folder 3 Enter the target folder with the command tgt target folder 4 Compare the files with the command e 5 Close the Synkronizer CommandLine Utility with the command x E E7 Synkronizer 11 Synkr
84. p en masse Suppose you need to compare checklists that have a column called Completed In the first checklist the values in this column are represented by yes no but in the other checklist by ja nein or 1 0 This would cause a large number of false positives to be reported in a normal comparison You can now filter out these differences by defining the corresponding equivalents e g 1 yes 1 ja yes ja O no O nein no nein etc Proceed as follows to activate one or more filters 1 Click Filters 2 Select Enable filters The filter icon changes from gray to black 3 Select the filter s that you want to activate 4 Close the filter window 2 35 Tools Highlight In this menu you can choose whether the differences are to be marked in the underlying Excel spreadsheets or not The following options are available e No highlighting Undo highlighting The Excel worksheets remain unchanged e Highlight differences All differences are highlighted in the underlying Excel worksheets e Clear amp highlight differences All differences are highlighted in the underlying Excel worksheets Additionally the background colors of the Excel worksheets are deleted prior to the comparison Depending upon your choice the check icon is displayed gray or black If the differences are to be marked the symbol is black otherwise gray 2 3 6 Tools Report In the Report menu you can choose whether an additional diffe
85. perlinks to the original 2000 2015 XL Consulting GmbH Switzerland 108 Synkronizer 11 User Manual document ReportType_None No report is created ReportType Standard A workbook is created range addresses are plain text 8 1 1 8 10 ResultType Constants to be used for the ResultType property in Settings ResultType_DifferentComm Number or detail information of different comments ent ResultType DifferentConte Number of different entered cell values formulas nt ResultType_DifferentForma Number of different formats t ResultType_DifferentName Number of different Excel names ResultType_DifferentValue Number of different calculated cell values results ResultType DuplicateKey Number of duplicated primary keys ResultType DuplicateRec Number of duplicated or redundant records ResultType_MissingCol Number of different columns ResultType_MissingRow Number of different rows ResultType_MissingWks Number of missing worksheets 8 1 1 8 11 sidelD Constants to identity the member of a set or pair sideID src Selects the 1st member Source of the set NOTE value is 0 sideID_tgt Selects the 2nd member Target of the set NOTE value is 1 8 1 1 8 12 WSOptionFlag Constants used by the WSOptionFlag property in Pair WSOptionFlag Colslon1 Columns will be linked 1 on 1 without finding inserted deleted columns WSOptionFlag_None Linking is disabled WSOptionFlag Rowslon1 Rows will be linked 1 on 1 without findi
86. r Berndt Schneider Holger Schmidt Peter Franken Philippe Cr tien Jean Fresni re London Birmingham Josh Gallagher Hari Kumar Denver Kirkland Brit Joyner Helvetius Nagy 2000 2015 XL Consulting GmbH Switzerland Results Tab 39 3 2 Differences In the lower part of the form the details of the difference selected in the upper part are displayed Toolbar for Missing Columns Worksheet D E sis SU Rikd Hw Customer List DU 312 ee Customer Profile 1218 f 27 Customers by Countries Toolbar for Missing Rows K ETZ E BSK E e Sales Summery Sales by Categories Sales by Countries or e un NN Ron Sales by Categories by Products Sales Detail Compare with Last Month Product Catalog Toolbar for Duplicate Database Records K BRA Y Employee Profile rr 1 e D gt Bao Pari bu Src Tgi Source Target e Toolbar for Duplicate Database Keys D7 D7 Elke Bischof Hanna Moos TEL SE e B17 B17 Stuttgart Aachen D17 D17 Rita Muller Berndt Schneider D29 D28 Holger Schmidt Peter Franken D52 D51 Philippe Cr tien Jean Fresni re 876 B75 London PERES Toolbar for Different Cells D76 D75 Josh Gallagher Hari Kumar gt ES e7 El Sort by row gt He B86 B85 Denver Kirkdand D86 D85 Brit Joyner Helvetius Nagy Toolbar for Different Comments gt BSB SE Sort by row gt All differences can be processed transferred or deleted with the following keys 4 Select difference With these buttons you
87. r comments or observations you may have made Please include information about your environment e g network Citrix server terminal server subnets etc that might help us to evaluate and resolve the problem During business days you will get an answer from our support team within 24 hours 2000 2015 XL Consulting GmbH Switzerland Synkronizer 11 User Manual 8 8 1 Developer Edition Visual Basic for Applications VBA With the Developer edition of Synkronizer you are able to create recurring standardized and or complex comparisons To access the automation functionality of Synkronizer you need to have knowledge about Visual Basic for Applications VBA With VBA you are able to you create your own powerful comparison macros We have provided some simple VBA Examples and a Wrapper Procedure to simplify you work To test the examples download the example file synkllvba zip from our server unzip it and copy the files to a folder of preference You can also manually test the examples by using the following steps Ensure that Synkronizer 11 Developer 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 11 0 Object Library check it and press ok 7 Select menu Insert Module 8 Copy the Helper Functions into this module 9 Select menu Inser
88. r object Example This example groups the records With oProj Pairs Item 0 row number of database heading DBRow 3 create primary key column no of source file DBKeys 2 3 group records DBoptions DBOptionFlag Group End With DBOptions Returns or sets options for sorting or grouping database records DBOptionFlag value Syntax expression DBOptions expression A variable representing a Pair object Example This example groups the records With oProj Pairs Item 0 row number of database heading DBRow 3 create primary key column no of source file DBKeys 2 3 group records DBoptions DBOptionFlag Group End With 2000 2015 XL Consulting GmbH Switzerland Developer Edition 93 8 1 1 5 2 3 DBRow Returns or sets the index for the row which contains the headers Long value Syntax expression DBRow expression A variable representing a Pair object Example This example defines the 3rd row as database heading With oProj Pairs Item 0 row number of database heading DBRow 3 create primary key column no of source file DBKeys 2 3 group records DBoptions DBOptionFlag Group End With 8 1 1 5 2 4 IsValid Returns true if the pair is valid Read only Boolean object Syntax expression IsValid expression A variable representing a Pair object 8 1 1 5 2 5 Range Returns the range object for the specified member of the pair Read only Range object Syn
89. rTgt vbDirectory If sFolderRep lt gt Then Debug Assert Len Dir sFolderRep vbDirectory If sFolderLog lt gt Then Debug Assert Len Dir sFolderLog vbDirectory t0 Timer On Error GoTo theExit 2000 2015 XL Consulting GmbH Switzerland Developer Edition 113 check if defined constants are valid Check Folders File get access to the Synkronizer application object InitSnk create log file If sFolderLog lt gt Then sFileLog sFolderLog amp synkronizer log amp Format Now yyyy mm dd_HH MM SS amp txt Reset Open sFileLog For Output As 1 Print 1 Synkronizer Logfile Print Fl NM Print 1 Pri Format Date yyyy mm dd Pri 1 Time Format Time hh nn ss Pri DE O pd YD pl He de Se ge He Se E D d o Pri End If read source files i D sFile Dir sFolderSrc amp xls Do While Len sFile gt 0 ReDim Preserve aFiles i aFiles i sFil i i 1 sFile Dir Loop log not matched worksheets vNotMatchedFiles Get _NotMatchedWorksheets sFolderSrc sFolderTgt If UBound vNotMatchedFiles gt 0 Then Print 1 Not matched files For i 1 To UBound vNotMatchedFiles Print 1 vNotMatchedFiles i Next i Print 1 Print 1 End If loop all source files For i 0 To UBound aFiles sFileSrc sFolderSrc aFiles i sFileTgt sF
90. re formulas with same results 27 Ignore hidden columns 27 Ignore hidden rows 27 Ignore whitespace 27 Installation Methods 54 IsDisposed 82 83 IsValid 85 Item 89 K Known Problems 72 dize license file 55 License manager 52 55 Link Data 10n1 20 Load 84 Load defaults to project 49 load save projects 11 main form 11 Manual Activation 65 Manually pair worksheets 12 Match all worksheets 12 14 Match by 12 Matchinclude 89 MatchincludeFlag 107 MatchType 90 107 message 104 Missing columns 36 103 Missing rows 36 103 Missing worksheets 103 N Names 26 network directory 55 Network installation 55 network users 57 new project 79 No worksheets found 14 Number 26 Numerical tolerance 27 O Open project 25 80 Outline 30 Outline options 42 OutlineFlag 107 Overview of differences 34 36 P Pair IsValid 93 pair worksheets 12 14 Pairs 83 PasswordList 90 Passwords 21 Permissions 55 primary key 17 2000 2015 XL Consulting GmbH Switzerland Progress 84 Project 25 45 Project file format 46 Project Settings 49 Project tab 45 protected worksheets 12 Protection 26 Purchase 51 R Range 93 RangeAddr 94 ranges 16 Redundant records 103 registration 64 registration key 64 Relaxed keys 17 Remove 88 Remove software 67 Remove worksheet pairs 12 14 Report 29 Report options 42 ReportType 99 ReportWorkbook 83 Reset defaults to factory 49 ResetPairs 88 Results 83 Results Fo
91. rence report should be generated or not The following options are available e No report No difference report will be generated e Standard report 2000 2015 XL Consulting GmbH Swizerland Synkronizer 11 User Manual 2 3 7 A normal difference report will be created which contains all differences e Hyperlinked report A difference report will be generated which contains all differences and each difference holds a hyperlink pointing to the corresponding difference The report icon is displayed either gray or black depending on its status If a difference report is selected the symbol is black otherwise gray Tools Outline In the Outline menu you can choose whether you want to see only certain types of differences or not In this way you can check and process the differences very efficiently The following difference types can be selected e Missing columns These columns are only found in one worksheet e Missing rows These rows were either deleted or added e Duplicate rows Applies only to database mode these rows include either duplicate redundant records or rows with duplicate keys e Identical rows These rows are completely identical and have no cell differences e Different rows These rows contain cell differences Proceed as follows if only certain differences need to be displayed 1 Click Outline 2 Select Enable outlining The outline symbol will change from gray to black 3 Select the
92. rm 34 ResultType 108 Se Save 82 save project 25 Save project as default 49 Saves source and target files 85 Scripted Installation 61 secret answer 64 secret question 64 Select difference 39 Select differences 36 select Excel files 11 Select Form 11 Select tab ribbon 24 select workbooks 12 2000 2015 XL Consulting GmbH Swizerland select worksheets 12 selected worksheets 11 set database options 11 Settings 83 Setup 54 shared folder 55 Sheet 94 SheetName 94 95 Show hide differences 30 34 ShowHide 98 sidelD 108 Sort differences 39 Sort records 17 Standard report ZO start button 31 Sum 104 SumByType 104 summary 104 summary message 104 SumTextByType 105 Support 73 SynkID column 17 Synkronizer editions 8 syReportType 107 System information 51 E d Transfer cell differences comments 39 Transfer different columns rows 39 Transfer software 67 types of worksheets 12 BEE Undo settings 49 uninstall 67 Uninstall software 55 Unload 81 Upgrade 51 V vba 125 Version Info 51 146 Synkronizer 11 User Manual W Window 86 Workbook 86 Worksheet linking options 108 worksheet options 12 worksheet properties ranges 11 worksheet settings 14 wrapper 125 2000 2015 XL Consulting GmbH Switzerland
93. rs none 2000 2015 XL Consulting GmbH Switzerland e Synkronizer 11 User Manual 8 1 1 1 1 2 Return Value none Remarks If another project is active it will be closed first OpenProject Opens a project as defined in specified xml file Syntax expression OpenProject file name expression A variable representing an Application object Parameters file name 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 closed first 8 1 1 1 2 Properties 8 1 1 1 2 1 8 1 1 1 2 2 ActiveProject Returns the project that is currently active Read only Syntax expression ActiveProject expression A variable representing an Application object Display Ul Shows or hides the Synkronizer user interface Syntax expression DisplayUI expression A variable representing a Application object 2000 2015 XL Consulting GmbH Switzerland Developer Edition at 8 1 1 2 Project Object The project is the central object for working with Synkronizer Methods Name Close Closes the current project Optionally closes the source target and if available difference report files Execute Executes the compare process Save Saves the project s configuration to the specified path Properties Name Files Container for the Files workbooks of the project IsDisposed Project was deactivated and internal object set to nothing Is
94. s Formats And FormatFlag Enabled Then sText SumTextByType ResultType DifferentFormat sLine sLine amp String 9 Len sText amp sText End If If CBool oProj Settings Contents And ContentFlag Comments Then sText SumTextByType ResultType DifferentComment sLine sLine amp String 9 Len sText amp sText End If If CBool oProj Settings Contents And ContentFlag_ Names Then sText SumTextByType ResultType DifferentName sLine sLine amp String 9 Len sText amp sText End If Print 1 sLine End With End If Print 1 Print 1 End Sub 2000 2015 XL Consulting GmbH Swizerland Developer Edition 125 8 1 3 VBA Examples Option Explicit PKKKKKKKKKKKKKKKKAKKKKKKAKKKKKKKKKKKKAKKAKKKKKKAKKKKKKAKKKKKKAKKKKKKKKAKKKAKK a SYNKRONIZER 11 d da VBA EXAMPLES B Ez To test the examples you need B to have a DEVELOPER license of Synkronizer 11 E I to create a reference to Synkronizer 11 Object Library E e include the module snk helper functions which contains He helper procedures and functions PK KKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKK KKK KKK KKKKAKKKKKKAKKKKKKKKAKKKAHK define folders Public Const FOLDI Public Const FOLDI Public Const FOLD Public Const FOLDI Public Const FOLDI define files Public Const FI Public Const FIL Public Const FI Public Const FI Public Const PROTSRC
95. s Synkronizer Synkronizer 11 Right click synkronizer dll Select Properties Select tab Digital Signatures Select XL Consulting GmbH Click Details button Click View Certificate Click Install Certificate Certificate Import Wizard dialog Click Next Select Place All certificates in the following store Click Browse Select Trusted Publishers Click Ok Click Next Click Finish Close the open dialogs Start Excel 7 1 Support 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 System License manager The license manager is opened 4 Create a screenshot of the license screen 2000 2015 XL Consulting GmbH Swizerland Synkronizer 11 User Manual Pa License x Professional Edition Version 11 0 40617 0 License file Type Network Path MYPC Synkronizer Server License xml License Manage 5 Select the Manage tab and create a screenshot of it 2000 2015 XL Consulting GmbH Swizerland Problems Errors P License x Computer Edition MYPC Professional Edition License s PRO NETWO3Z CNP4TOS K4EG3LM X Add Key Info Nodes Info Name Value Valid License RegKey NETWO3Z CNP4TOS K4EG3LM OK Computers 10 Install
96. s checked spaces are ignored at the beginning and end of words e g the values John Wayne and John Wayne additional spaces at the beginning and at the end are considered as equal Ignore data type It is possible that some numbers are formatted as number and others as text This will logically be evaluated as a difference although the numbers are identical With this option these differences are ignored Ignore formulas with same results Supposed you have two different formulas which contain the same results See the following example Cell source file Formula D50 Result 100 Cell target file Formula D 50 Result 100 These types of differences are filtered out by activating this option Ignore constants If this option is active all cells with constants entered values are ignored Ignore formulas All formula fields are filtered out Ignore hidden columns All columns that are hidden in the source file are ignored Hidden columns of the target file are compared Ignore hidden rows Works like hidden columns but with rows Numerical tolerance For numeric fields you can instruct Synkronizer how to deal with deviations Supposed only deviations greater than 0 10 need to be shown then enter 0 10 Deviations up to and including 0 10 will then be ignored Equivalent values 2000 2015 XL Consulting GmbH Swizerland Select Tab Chances are that in some worksheets the same difference shows u
97. sMsg GetDifferences oProj close project CloseProject End With display message sgBox sMsg vbOKOnly vbInformation Synkronizer Exit Sub Err Example sgBox Err Description vbExclamation Synkronizer End Sub Example 5 database comparison highlight differences Public Sub Example Dim oProj As Synkronizer Project Dim sMsg As String On Error GoTo Err Example check if defined constants are valid 2000 2015 XL Consulting GmbH Switzerland Developer Edition 131 Check_Folders_File get access to the Synkronizer application object InitSnk create project Set oProj snk NewProject With oProj define files Files Load FILESRC FILETGT define worksheets amp database options Pairs AddPair Sheet0 Customer List Sheet1 Customer List DBRow 1 _ DBReys 1 2 _ DBOptions DBOptionFlag Group highlight differences With Settings Highlight HighlightType WithReset nd With E start Synkronizer Execute li get the message string before closing the project sMsg GetDifferences oProj close project CloseProject End With display message sgBox sMsg vbOKOnly vbInformation Synkronizer Exit Sub Err Example sgBox Err Description vbExclamation Synkronizer End Sub Example 6 link rows d gr 1 highlight differences Public Sub Example6
98. sks does the CommandLine Utility support The following tasks are supported e Compare two files e Compare a source file against a bundle of source files e Compare all Excel files with equal file names in two folders e Create a difference report e Create a log file which contains all differences e Format and filter options are supported Which tasks are not supported by the CommandLine Utility The following tasks are not supported e Source and target files cannot be saved e Differences cannot be highlighted e Differences cannot be outlined shown hidden The CommandLine Utility of Synkronizer is invoked as follows 1 Start the Windows Explorer 2 Select the Synkronizer folder c Program Files Synkronizer Synkronizer 11 synk exe 3 Double click on synk exe 4 The Sykronizer CommandLine Utility appears 2000 2015 XL Consulting GmbH Swizerland E 13 Synkronizer 11 User Manual W Synkronizer 11 Synkronizer CommandLine Utility Variables dir D Documents src tgt xml rep log Tools matching 1 lt A1L1ByName gt report 1 Standard gt Start CommandLine Utility with a batch file or Windows shortcut We recommend that you create a batch file or a Windows shortcut In this way you can start the CommandLine Utility with a single mouse click Creating a batch file Proceed as follows Start a text editor and create a new document Write the following code echo off C Program Files S
99. t Module 10 Copy the VBA Examples into this module Aum ah 0 b E The Object Model Reference of Synkronizer 11 is described in the following chapters Object Model Reference Object Model Reference of Synkronizer 11 The object model of Synkronizer 11 looks as follows Excel ComAddins Synkronizer AddIn Object Synkronizer Application Project Files Pairs Pair Results Settings 2000 2015 XL Consulting GmbH Switzerland Developer Edition Results 8 1 1 1 Application Object Top level object Entry point for all automation Methods NewProject Returns a project with empty files default settings and manual matching OpenProject Creates a new project with empty files default settings and manual matching Properties ActiveProject Project that is currently active DisplayUI Determines if user interface is displayed or not Remarks The application object is only accessible when you have a licensed Developer Edition Example To access the Synkronizer Application you should do the following Dim cai As COMAddIn Dim snk As Synkronizer Application Set cai Application COMAddIns Synkronizer Addin If Not cai Connect Then cai Connect True End If Set snk cai Object 8 1 1 1 1 Methods 8 1 1 1 1 1 New Project Creates a new project with empty files default settings and manual matching Syntax expression NewProject expression A variable representing an Application object Paramete
100. t snk cai Object End If Ensure any existing project is silently closed If Not bWithUI And snk DisplayUI Then 2000 2015 XL Consulting GmbH Switzerland m Synkronizer 11 User Manual hiding the UI will dispose the project snk DisplayUI False ElseIf bWithUI And Not snk DisplayUI Then snk DisplayUI True End If End Sub this procedure closes a project Public Sub CloseProject If Not snk ActiveProject Is Nothing Then If Not snk ActiveProject IsDisposed Then snk ActiveProject Close CloseFiles False DisplayUndo False End If End If End Sub this procedure checks if the defined folders amp files are valid Public Sub Check Folders File check folders If ROOT lt gt Then Debug Assert Len Dir ROOT vbDirectory gt 0 If FOLDERSRC lt gt Then Debug Assert Len Dir FOLDERSRC vbDirectory gt If FOLDERTGT lt gt Then Debug Assert Len Dir FOLDERTGT vbDirectory gt If FOLDERREP lt gt Then Debug Assert Len Dir FOLDERREP vbDirectory gt If FOLDERPRJ lt gt Then Debug Assert Len Dir FOLDERPRJ vbDirectory gt If FOLDERLOG lt gt Then Debug Assert Len Dir FOLDERLOG vbDirectory gt check files If FILESRC lt gt Then Debug Assert Len Dir FILESRC gt 0 If FILETGT lt gt Then Debug Assert Len Dir FILETGT gt 0 If PROTSRC lt gt Then Debug Assert
101. tax expression DuplicateKeys id expression A variable representing a Results object Parameters id sideID Selects either member of the Pair 2000 2015 XL Consulting GmbH Swizerland Developer Edition 103 8 1 1 7 1 9 DuplicateRecords Returns the number of duplicate records database only Read only Long value Syntax expression DuplicateRecords id expression A variable representing a Results object Parameters id sidelD Selects either member of the Pair 8 1 1 7 1 10 MissingCols Returns the number of missing columns or fields Read only Long value Syntax expression MissingCols id expression A variable representing a Results object Parameters id sidelD Selects either member of the Pair 8 1 1 7 1 11 MissingRow s Returns the number of missing rows or records Read only Long value Syntax expression MissingRows id expression A variable representing a Results object Parameters id sidelD Selects either member of the Pair 8 1 1 7 1 12 MissingSheets Returns the number of missing worksheets auto matching only Read only Long value Syntax expression MissingSheets id expression A variable representing a Results object Parameters 2000 2015 XL Consulting GmbH Switzerland 104 Synkronizer 11 User Manual id sidelD Selects either member of the Pair 8 1 1 7 1 13 Sum Returns the total number of differences found Read only Long value Syntax expression Sum expression A
102. tax expression Range id expression A variable representing a Pair object Parameters id sidelD Selects source or target worksheet Remarks To change the Range use the RangeAddr property 2000 2015 XL Consulting GmbH Switzerland sa Synkronizer 11 User Manual 8 1 1 5 2 6 8 1 1 5 2 7 8 1 1 5 2 8 RangeAddr Returns or sets an optional range address for the specified member of the pair Read only String value Syntax expression RangeAddr id expression A variable representing a Pair object Parameters id sidelD Selects source or target worksheet Example This example sets the range address With Pairs Add db1 dbl Item 1 RangeAddr sideID src A1 H50 Item 1 RangeAddr sideID tgt Al H50 End With Sheet Returns the Worksheet object for the specified member of the pair Read only Worksheet object Syntax expression Sheet id expression A variable representing a Pair object Parameters id sidelD Selects source or target worksheet SheetName Returns the name of the worksheet for the specified member of the pair Read only String value Syntax expression SheetName id expression A variable representing a Pair object Parameters id sidelD Selects source or target worksheet 2000 2015 XL Consulting GmbH Switzerland Developer Edition os 8 1 1 5 2 9 WSOptions Returns or sets the name of the linking options Syntax expression WSOptions expression A vari
103. the header of the worksheets list O Worksheets A Choose the worksheets you want to compare gt ES Source worksheet Target worksheet Customer List E Customer List Customer Profile Customers by Countries Sales Summery Sales by Categories Sales by Countries Sales by Categories by Products Sales Detail Compare with Last Month Product Catalog Employee Profile Top 5 Employees for Sales Status of Current Activities Income Statement Historical Income Statement Above the worksheets table there are two drop down list boxes see chart Using these boxes the desired worksheets can be selected Select the desired worksheets and confirm the selection with the arrow button to the right The worksheet pair will then be added to the worksheets list Match all worksheets You can also match all worksheets This is done using the following button O Worksheets A Choose the worksheets you want to compare E as Source worksheet Target worksheet y GE All equally named worksheets will be paired Remove worksheet pairs If you do not want to compare all worksheet pairs you can empty the worksheets list using the Delete button 2000 2015 XL Consulting GmbH Switzerland 16 Synkronizer 11 User Manual 2 2 2 O Worksheets Choose the worksheets you want to compare beruna Source worksheet Target worksheet No items No items gt As a further option the unwanted worksheet pairs can be removed by selecting th
104. tion In this part you see the information about your edition O Beloa a Registration amp Activation Recent By clicking on the License Manager button the license Settings manager is started In the License Manager you ll find more system pr ane information about the installed license You can also register activate and deactivate the software Furthermore you can administrate the nodes network users if you have a network license Version In this part you can check if you are working with the latest build If this isn t the case a download button appears with which you can download the latest build 2000 2015 XL Consulting GmbH Swizerland 52 Synkronizer 11 User Manual 4 3 1 License Manager In the license manager you can see information about your license Professional Edition Version 11 0 40423 0 License file Type Local Path C ProgramData Synkronizer Synkronizer 11 license xml License Manage The following information is visible can be set within this form e detailed information about your license register activate license deactivate license e define nodes network users applies only to network license 2000 2015 XL Consulting GmbH Switzerland s Synkronizer 11 User Manual 5 Install Uninstall Synkronizer 11 is a COM addin for Excel 2013 2010 and 2007 Synkronizer works both in 32 and 64 bit mode Installation 1 Download the latest version 2 Log on to Windows
105. types of difference which you want to view 4 Close the dialogue Outline 2000 2015 XL Consulting GmbH Swizerland Select Tab 31 2 3 8 Start Compare files After you have selected all the worksheets settings and comparison options you can start comparing the worksheets Just click on the Start icon and the files will be compared Synkronizer 11 B H x SELECT RESULTS 00 a D amp Contents 3 Report e ba Formats Go Highlight 2 E Y Filters GE Outline tart Project Compare Tools O Workbooks Y Worksheets A Options Choose the worksheets you want to compare SGS DEZ Source worksheet Target worksheet No items No items gt O Compare as worksheet O Compare as database Historical Income Statement Ranges optional select range Sa select range Customer Profile Customer Profile Customers by Countries Customers by Countries Sales Summery Sales Summery Sales by Categories Sales by Categories Sales by Countries Sales by Countries Sales by Categories by Product Sales by Categories by Product Sales Detail Sales Detail Compare with Last Month Compare with Last Month Product Catalog Product Catalog Employee Profile Employee Profile Top 5 Employees for Sales Top 5 Employees for Sales Status of Current Activities Status of Current Activities Income Statement Income Statement Historical Income Statement
106. tzerland ss Synkronizer 11 User Manual 8 1 1 2 3 Events 8 1 1 2 3 1 Progress Raised during compare execution to indicate running subprocess Remarks You can use this event to display a progress indicator 8 1 1 3 Files Object Container for the two files workbooks to compare Methods Load Loads the two files from disk Save Saves the source file and the target file Properties file name Returns the full path and file name of either of the files IsValid Returns true if both files are loaded and can be compared Window Returns the window object of either of the files Workbook Returns the workbook object of either of the files Events none 8 1 1 3 1 Methods 8 1 1 3 1 1 Load Loads the two files from disk Syntax expression Load file name0 file name1 expression A variable representing a Files object Parameters file name0 String Full path for 1st file also referred to as source file namel String Full path for 2nd file also referred to as target NoArrange Boolean Optional Set true if workbooks don t need to be arranged horizontally or vertically 2000 2015 XL Consulting GmbH Swizerland Developer Edition as Return Value none 8 1 1 3 1 2 Save Loads the two files from disk Syntax expression Save Save0 Savel PathO Path1 expression A variable representing a Files object Parameters Save0 Boolean Set true if the source file needs to be saved Savel Boolean Set true if t
107. ulas with same result will be ignored es FilterFlag_StringCas Text compare case Insensitive ABC vs abc will be considered as equal e FilterFlag_StringSpa Text compare trim text ABC vs ABC will be considered as equal ce 8 1 1 8 4 FormatFlag Constants to be used for the Formats property in Settings FormatFlag_ Alignment Compare cell alignment properties FormatFlag_Border Compare cell border properties FormatFlag_Enabled Enable or disable all other formats options FormatFlag_Font Compare cell font properties FormatFlag_None No formats are compared FormatFlag_Number Compare cell number numberformat properties FormatFlag_Pattern Compare cell pattern interior fill properties FormatFlag Protection Compare cell protection properties 8 1 1 8 5 HighlightType Constants to be used for the Highlight property in Settings HighlightType_None Differences are not highlighted HighlightType Standard Differences are highlighted No Cell coloring for all cells if not removed before processing HighlightType_WithReset Differences are highlighted Cell coloring for all cells is removed before processing 2000 2015 XL Consulting GmbH Switzerland Developer Edition 107 8 1 1 8 6 MatchincludeFlag Flags specifying a group of sheets to include during Matching MatchIncludeFlag_EmptySheets Empty sheets will be included by AddMatched MatchIncludeFlag_HiddenSheets Hidden sheets will be included by AddMatched Match
108. umn contains the same value Du monde entier France Janine Labrune Eastern Connection UK Ann Devon Familia Arquibaldo S o Paulo Brazil Aria Cruz FISSA Fabrica Inter Salchichas S A Madrid Spain Diego Roel Folio gourmandes Lille France Martine Ranc Folk och f HB Br cke Sweden Maria Larsson Duplicate record all cells contain the same values Paolo Accorti 29 Frankenversand M nchen Germany Holger Schmidt Link Data 1 on 1 If you are sure that your worksheets do not contain new or deleted rows columns then you can compare your files with the 1 on 1 method In this case the rows and columns are compared 1 on 1 and only cell differences will be detected Proceed as follows to compare the rows and or columns by the 1 on 1 linking method 1 Select the worksheet pair which should be compared 1 on 1 2 Click Compare as worksheet The tab Linking Options appears Customer List Customer List O Options d Linking Options _ Compare as worksheet Compare with link options pare as database Ranges gtional select range E select range 4 The tab Linking Options appears Click Link columns 1 on 1 or Link rows 1 on 1 2000 2015 XL Consulting GmbH Switzerland Select Tab 21 Sales Summery Sales Summery O Options Fa Linking Options Select to override default linking V Link Columns 1 on 1 Y Link Rows 1 on 1 5 After confirming the 1 on 1 linking method the
109. uplicateKeys DuplicateRecords ARO leal MISSINO ROW SAA a dic AEN EAE SUM eai oga ru e a gaietara dienak A ie EO SUMBY ATE ura Eire Laida SUMTOX aN EEE a aT A A E SUMTEXtBY ERRA EUDIEN abar a aitare iia E e E ar Content Flag AAA BUGI EO E idee se A el ea ai ee edie ees Eller AAA FormatFlag HighlightType MESIE Aga ENEA EN E a cain A nis aia ea eae Match Ty Pe Ea a sisi nav eae i lena el ar 2000 2015 XL Consulting GmbH Switzerland Contents 5 QUIMERA ASA eee anand lene ak 107 REPORT Pin A eh ene ieee 107 ESU AUT 108 GIS siii AA A A adi AA 108 MARIO FAG mica A AA E ETETE era 108 VBA A DE aak a a E OT 109 MAS SUA AEE 125 2 CommandLine Utility cocina 135 REF RON CG ii A ii 137 Exam Ea 138 Index 143 2000 2015 XL Consulting GmbH Switzerland 1 Welcome Dear Synkronizer users Thanks for choosing Synkronizer 11 Synkronizer 11 is the most powerful Excel comparison program in the market Synkronizer 11 is the only program worldwide that can compare and update regular Excel files and Excel databases Synkronizer 11 allows you to e compare Excel files update transfer of differences highlight the differences in the underlying worksheets create a difference report merge different Excel worksheets or databases find differences duplicates automate processes by using comparison projects create syntax for command line and Visual Basic VBA This manual introduces
110. with administrator privileges 3 Close Excel 4 Start the downloaded setup executable and follow instructions 5 Start Excel 6 Synkronizer can now be started from the Add Ins Tab Dag Ls E Er es Book1 Excel ls H x HOME INSERT Format PAGE LAYOUT FORMULAS DATA REVIEW VIEW DEVELOPER ADD INS Sign in Synkronizer 11 Vergleichen 7 Register the software Versions amp Updates The installer will automatically uninstall older builds of Synkronizer 11 when found on your system Previous versions of Synkronizer are not affected Administrator Privileges If you do not have administrative rights you can not install the software Installation for Network License A network license enables the use of a centralized license file stored on a network folder The path to that location is stored in the registry and can be specified during installation with a command line option only best suited for scripted installs It can also be set or changed in the application itself see Registration Folders All files are located in the installation folder typically C Program Files Synkronizer Synkronizer 111 The license file is located in C ProgramData Synkronizer Synkronizer 11 2000 2015 XL Consulting GmbH Swizerland Install Uninstall 55 The Network License location is stored in the registry under Key HKLM Software Wow6432Node Synkronizer Synkronizer 11 Name LicensePath String Value
111. wks As Worksheet Dim val As Variant Dim rng As Range Dim p As Pair Dim iWksCount As Integer Debug Assert Not oProj Is Nothing Debug Assert Not oProj IsDisposed create workbook iWksCount Application SheetsInNewWorkbook Application SheetsInNewWorkbook 1 Set wkb Workbooks Add Application SheetsInNewWorkbook iWksCount Set wks wkb Worksheets 1 name worksheet wks Name Project Differences val oProj Results ArrayDetails If wks UsedRange Cells CountLarge 1 Then Set rng wks Cells 1 Else With wks UsedRange Set rng wks Cells Row Rows Count Column End With End If If IsEmpty val Then rng Offset 0 0 Value no diffs Else Set rng rng Offset 0 0 Resize UBound val 1 1 UBound val 2 2000 2015 XL Consulting GmbH Swizerland 12 Synkronizer 11 User Manual 1 rng Clear rng Resize 11 NumberFormat rng Resize 4 HorizontalAlignment xlLeft rng VerticalAlignment xlTop rng Value2 val End If format range With rng Rows 1 Font Bold True Columns I K HorizontalAlignment xlRight Columns A ColumnWidth 20 Columns B K ColumnWidth 8 Columns D E ColumnWidth 32 Columns F H ColumnWidth 18 Columns F G EntireColumn Hidden True For Each p In oProj Pairs If p DBKeys lt gt Then Columns F G EntireColumn Hidden False Exit For End If
112. ws Returns the number of missing rows or records MissingSheets Returns the number of missing worksheets only for projects with automatching Sum Returns the total number of differences found SumByType Returns the number of specified differences found SumText Returns the results as a formatted text for message box SumTextByType Returns the number of specified differences found as text 2000 2015 XL Consulting GmbH Switzerland 100 Synkronizer 11 User Manual Events none Remarks e If the results object is a child of the Project object then combined results are returned e If the results object is a child of the Pair object then individual results are returned 8 1 1 7 1 Properties 8 1 1 7 1 1 8 1 1 7 1 2 Array Counts Returns an array with the overall number of differences found as text Read only Array Syntax expression ArrayCounts expression A variable representing a Results object Description of Array This property returns an array Each difference is represented by a single element of the array with 11 columns The first element of the array contains the column headings 0 Source Name of source worksheet 1 Total Total differences of worksheet pair 2 Columns Overall differences of missing columns sourcejtarget 3 DupRecs Overall differences of duplicate records 4 DupKeys Overall differences of duplicate keys 5 Rows Overall differences of missing rows sourcejtarget 6 Content Overall
113. ynkronizer Synkronizer 11 Synk exe Save the file as synk bat in a folder of preference Creating a Windows shortcut You can also create a Windows shortcut Proceed as follows WN NOW b Right click an open area on the desktop point to New and then click Shortcut Click Browse Locate synk exe which is stored in the following folder C Program Files Synkronizer Synkronizer 11 click Open and then click Next Type a name for the shortcut and click Finish button Right click on the created shortcut Enter in the field Start in the folder in which the Synkronizer files should be stored Click OK and close the shortcut 2000 2015 XL Consulting GmbH Switzerland 8 2 1 Developer Edition 137 Reference This page contains the syntax of the command line utility and a description of all variables commands and settings Syntax synk exe dir Parent folder src Source folder fi tgt Target folder file xml Synkronizer project file rep Report folder log Log file m Matching options r Report options c x Variables dir Parent folder Optional Name location of the parent folder The difference reports and log files will be stored within this folder if not defined separately src Source folder file Required Folder of the source files or the name of the source file If you want to compare all files of a folder you need to enter a fol
114. you to the Synkronizer 11 environment and explains the essential functions Of course Synkronizer 11 may not be perfect We are always interested in learning from our users so your feedback is always welcome For any suggestions or questions e g menu items settings forms or about this manual feel free to contact us directly Sincerely Thomas Str bi amp Jurgen Volkerink XL Consulting GmbH 1 1 What s new Although you might be already familiar with the previous vervions Synkronizer 11 contains some new features that we would like to present e Synkronizer 11 is a COM add in e The program works with the following versions of Excel Excel 2013 32 bit and 64 bit Excel 2010 32 bit and 64 bit 2000 2015 XL Consulting GmbH Switzerland 8 Synkronizer 11 User Manual 1 2 Excel 2007 32 bit The following operating systems are supported Windows 8 32 bit and 64 bit Windows 7 32 bit and 64 bit Windows Vista 32 bit and 64 bit Windows XP 32 bit and 64 bit Windows Server 2012 Windows Server 2008 The program has a new user interface Comments can be compared Excel names can be compared There are two new categories of differences Entered Values amp Formulas and Calculated Values These help to identify cell differences more accurately The category Entered values amp formulas will report different cell valuesdr formulas The Calculated Values type will report a difference in c

User Manual - Synkronizer Excel Compare

Contents

Download Pdf Manuals

Related Search

Related Contents