Developer Edition > Visual Basic for Applications (VBA)

Option Explicit

'*******************************************************************

'* *

'* SYNKRONIZER 11 *

'* VBA EXAMPLES *

'* *

'* To test the examples you need *

'* - to have a DEVELOPER license of Synkronizer 11 *

'* - to create a reference to 'Synkronizer 11 Object Library' *

'* - include the module 'snk_helper_functions' which contains *

'* helper procedures and functions *

'* *

'*******************************************************************

'define folders

Public Const ROOT As String = "D:\Documents\"

Public Const FOLDERSRC As String = ROOT & "Source\"

Public Const FOLDERTGT As String = ROOT & "Target\"

Public Const FOLDERREP As String = ROOT & "Reports\"

Public Const FOLDERPRJ As String = ROOT & "Projects\"

Public Const FOLDERLOG As String = ROOT & "Log\"

'define files

Public Const FILESRC As String = FOLDERSRC & "Source 1.xlsx"

Public Const FILETGT As String = FOLDERTGT & "Target 2.xlsx"

Public Const FILEREP As String = FOLDERREP & "Synkronizer_Difference_Report.xlsx"

Public Const FILEPRJ As String = FOLDERPRJ & "SynkProject.xml"

Public Const PROTSRC As String = FOLDERSRC & "Protected 1.xlsx"

Public Const PROTTGT As String = FOLDERTGT & "Protected 2.xlsx"

'-----------------------------------------------------------

'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 Example1()

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

'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 differences

.Highlight = HighlightType_WithReset

'show only rows/columns with differences

.Outline = OutlineFlag_Enabled + _

OutlineFlag_DifferentRows + OutlineFlag_MissingRows + OutlineFlag_DuplicateRows + _

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

End With

'display message

MsgBox sMsg, vbOKOnly + vbInformation, "Synkronizer"

Exit Sub

Err_Example:

MsgBox 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)

'close project

CloseProject

'display message

MsgBox sMsg, vbOKOnly + vbInformation, "Synkronizer"

Exit Sub

Err_Example:

MsgBox Err.Description, vbExclamation, "Synkronizer"

End Sub

'-----------------------------------------------------------

'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

'display message

MsgBox sMsg, vbOKOnly + vbInformation, "Synkronizer"

Exit Sub

Err_Example:

MsgBox Err.Description, vbExclamation, "Synkronizer"

End Sub

'-----------------------------------------------------------

'Example 4

'- compare first sheets

'- compare formats, comments, names & use filters

'- create a report

Public Sub Example4()

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_StringSpace

.FilterTolerance = 0.01

.FilterEquivalents = "yes,ja;no,nein"

'create a report

.Report = ReportType_Standard

End With

'compare files

.Execute

If Not .ReportWorkbook Is Nothing Then

'delete report if it already there

If Len(Dir(FILEREP)) > 0 Then Kill FILEREP

'save report

With .ReportWorkbook

.SaveAs file name:=FILEREP

.Close SaveChanges:=False

End With

End If

'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 5

'- database comparison

'- highlight differences

Public Sub Example5()

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 worksheets & database options

.Pairs.AddPair Sheet0:="Customer List", _

Sheet1:="Customer List", _

DBRow:=1, _

DBKeys:="1;2", _

DBOptions:=DBOptionFlag_Group

'highlight differences

With .Settings

.Highlight = HighlightType_WithReset

End With

'start Synkronizer

.Execute

'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 6

'- link rows 1 on 1

'- highlight differences

Public Sub Example6()

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 worksheets & link options

.Pairs.AddPair Sheet0:="Customer List", _

Sheet1:="Customer List", _

WSOptions:=WSOptionFlag_Rows1on1

'highlight differences

With .Settings

.Highlight = HighlightType_WithReset

End With

'start Synkronizer

.Execute

'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

'- 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

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

'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

Public Sub Example8()

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

'-----------------------------------------------------------

'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, _

sFolderTgt:=FOLDERTGT, _

bHighlight:=True, _

sFolderRep:=FOLDERREP, _

sFolderLog:=FOLDERLOG)

MsgBox sMsg

End Sub

VBA Examples

VBA Examples