Synkronizer with Visual Basic for Applications
Synkronizer can also be controlled using Visual Basic for Applications (VBA). This has the advantage that recurring, standardized or complex comparison processes can be automated.
Syntax
The syntax of the Synkronizer function is as follows:
Synkronizer(sFileOld As String,
sFileNew As String,
vSheetOld As Variant,
vSheetNew As Variant,
Optional sRangeOld As String,
Optional sRangeNew As String,
Optional sCompareType As String,
Optional sHide As String,
Optional sFormats As String,
Optional sKeyFields As String,
Optional sDBOptions As String,
Optional sLink1on1 As String,
Optional sAction As String,
Optional sReportFile As String,
Optional bHyperlinks As Boolean,
Optional bDeleteBGColor As Boolean,
Optional sFilters As String,
Optional sIgnoreCols As String,
Optional dTolerance As String,
Optional sTextfilter As String) As Variant
Return Value
Depending upon the result, the function Synkronizer returns the following values:
| "Err" | Synkronizer could not interpret one or more of the parameters or errors during processing. |
| Array | Synkronizer detected differences and returns the differences in a two dimensional array which is arranged as follows: |
| Element(1, 1): Caption "Total differences" Element(1, 2): Number of total differences Element(2, 1): Caption "Deleted worksheets" Element(2, 2): Number of deleted worksheets Element(3, 1): Caption "Added worksheets" Element(3, 2): Number of added worksheets Element(4, 1): Caption "Deleted columns" Element(4, 2): Number of deleted columns Element(5, 1): Caption "Added columns" Element(5, 2): Number of added columns Element(6, 1): Caption "Duplicate indexes" Element(6, 2): Number of duplicate indexes Element(7, 1): Caption " Duplicate records " Element(7, 2): Number of duplicate records Element(8, 1): Caption "Deleted rows" Element(8, 2): Number of deleted rows Element(9, 1): Caption "Added rows" Element(9, 2): Number of added rows Element(10, 1): Caption "Different Cell Entries" Element(10, 2): Number of different cell entries Element(11, 1): Caption "Different Formats" Element(11, 2): Number of different formats Element(12, 1): Caption "Used filters:" Element(12, 2): Used filters |
Parameters
The function contains the following parameters.
| sFileOld | File (incl. path) of the 1st file (Master). You can use Excel-, text- and odc-files. Access databases must be entered in an odc file. | |
| sFileNew | File (incl. path) of the 2nd file (Changes). | |
| vSheetOld | Worksheet name or number of the 1st file. You can enter the following values: | |
| 1 3 "sheet1" "" |
the first worksheet of the 1st file will be compared the third worksheet of the 1st file will be compared the worksheet with the caption "sheet1" will be compared all worksheets will be compared. |
|
| vSheetNew | Worksheet name or number of the 2nd file. | |
| sRangeOld | Optional. A1-Range of 1st worksheet. If this argument is empty, all cells will be compared. | |
| aRangeNew | Optional. A1-Range of 2nd worksheet. | |
| sCompareType | Optional. This parameter contains the comparison type. You can choose the one or multiple of the following characters: | |
| v f c |
Comparison by values Comparison by formulas Comparison by comments |
|
| If this paramer is empty, a comparison by values is done. E.g. you want to make "case sentive"-comparison, just enter "vs". | ||
| sHide | Optional. With this parameter you can set the data types to hidden. You can choose one or multiple of the following options: | |
| c r d v f i |
unlinked columns unlinked rows duplicates different values (or formulas, comments) different formulas identical cells |
|
| sFormats | Optional. With this parameter you determine the formats to compared: | |
| n a f b p t |
Number formattings Alignments Fonts Borders Patterns Cell protections |
|
| sKeyFields | Optional. If you want to make a database-comparison, please enter the names of the keys in this section. The keys are divided with semicolons ";" (e.g. Family name; Fist name; City). If you do not make a database-comparison, leave the field empty. | |
| sDBOptions | Optional. If you are making a database-comparison, you can also set the following options: | |
| s g i |
the data records will be sorted the data records will grouped a Synkronizer ID will be added at the end of the database |
|
| sLink1on1 | Optional. If the columns or rows shall be linked 1 on 1 (the worksheets contain no added/deleted columns or rows), you can set these options: | |
| c r |
the columns are linked 1 on 1 the rows are linked 1 on 1 |
|
| sAction | Optional. This argument contains the action function. You can enter the following arguments: | |
| h r n |
the differences are highlighted a difference report is created the navigator is started |
|
| sReportFile | Optional. If you want to create a difference report, just enter the filename (incl. Path) for the report to be created (e.g. C:\Documents\Report.xls). | |
| bHyperlinks | Optional. If the difference report should contain hyperlinks, enter True. | |
| bDeleteBGColor | Optional. If true then existing background colors will be removed before the differences are highlighted. | |
| sFilters | Optional. Combination(s) of following filters: | |
| s t d c r k f |
(case) sensitive comparison trim whitespace ignore datatype ignore hidden columns ignore hidden rows ignore constants ignore formulas |
|
| sIgnoreCols | Optional. Ignore specified columns. A semicolon (;) delimited string. Use column numbers (relative to range) or Fields names in database mode. | |
| dTolerance | Optional. Ignore (numeric) difference below the tolerance. | |
| sTextfilter | Optional. Sets text filter. Must start with like: or regex: Then append a valid pattern. | |
| Examples: Like RegEx |
like:done regex:yes|no |
|
Using Synkronizer in your VBA Scripts
To execute Synkronizer with VBA, set a reference to Synkronizer in your scripts. To set a reference, proceed as follows:
| 1. | open the Excel spreadsheet that contains the VBA script |
| 2. | open the VBA Editor (Keyboard Shortcut Alt-F11) |
| 3. | set the focus to your VBA script |
| 4. | go to the Tools menu and select References |
| 5. | find 'Synkronizer95' in the list, and put a check next to it |
Now you are able to use the Synkronizer-function. You can see some examples of VBA scripts below. Please feel free to use them.
VBA-Examples
This example highlights the differences in the spreadsheets "Addresses" of both files.
Public Sub Example_1()
Dim vSynk As Variant
Dim sMsg As String
Dim i As Integer
On Error GoTo errH
vSynk = Synkronizer(sFileOld:="C:\Documents\FileOld.xls", _
sFileNew:="C:\Documents\FileNew.xls", _
vSheetOld:="Addresses", _
vSheetNew:="Addresses", _
sAction:="h")
Beep
If Not IsArray(vSynk) Then
MsgBox vSynk, vbExclamation
ElseIf vSynk(1, 2) = 0 Then
MsgBox "No differences noted.", vbInformation
Else
sMsg = "Synkronizer found differences:" & vbNewLine & vbNewLine
For i = 1 To UBound(vSynk)
sMsg = sMsg & vSynk(i, 1) & ": " & vSynk(i, 2) & vbNewLine
Next i
MsgBox sMsg, vbInformation
End If
Exit Sub
errH:
MsgBox Err.Description, vbExclamation
End Sub
This example highlights the differences in all spreadsheets of both files.
Public Sub Example_2()
Dim vSynk As Variant
Dim sMsg As String
Dim i As Integer
On Error GoTo errH
vSynk = Synkronizer(sFileOld:="C:\Documents\FileOld.xls", _
sFileNew:="C:\Documents\FileNew.xls", _
vSheetOld:="", _
vSheetNew:="", _
sHide:="i", _
sAction:="h")
Beep
If Not IsArray(vSynk) Then
MsgBox vSynk, vbExclamation
ElseIf vSynk(1, 2) = 0 Then
MsgBox "No differences noted.", vbInformation
Else
sMsg = "Synkronizer found differences:" & vbNewLine & vbNewLine
For i = 1 To UBound(vSynk)
sMsg = sMsg & vSynk(i, 1) & ": " & vSynk(i, 2) & vbNewLine
Next i
MsgBox sMsg, vbInformation
End If
Exit Sub
errH:
MsgBox Err.Description, vbExclamation
End Sub
This procedure compares the first spreadsheets of both Excel files and creates a difference report called "Report.xls".
Public Sub Example_3()
Dim vSynk As Variant
Dim sMsg As String
Dim i As Integer
On Error GoTo errH
vSynk = Synkronizer(sFileOld:="C:\Documents\FileOld.xls", _
sFileNew:="C:\Documents\FileNew.xls", _
vSheetOld:=1, _
vSheetNew:=1, _
sAction:="r", _
sReportFile:="C:\Documents\Report.xls")
Beep
If Not IsArray(vSynk) Then
MsgBox vSynk, vbExclamation
ElseIf vSynk(1, 2) = 0 Then
MsgBox "No differences noted.", vbInformation
Else
sMsg = "Synkronizer found differences:" & vbNewLine & vbNewLine
For i = 1 To UBound(vSynk)
sMsg = sMsg & vSynk(i, 1) & ": " & vSynk(i, 2) & vbNewLine
Next i
MsgBox sMsg, vbInformation
End If
Exit Sub
errH:
MsgBox Err.Description, vbExclamation
End Sub
This example compares the databases "Addresses". The primary key is "ID". The data records will be grouped and the Synkronizer IDs are added to the databases. Finally the differences are highlighted in both files and the Navigator is started.
Public Sub Example_4()
Dim vSynk As Variant
Dim sMsg As String
Dim i As Integer
On Error GoTo errH
vSynk = Synkronizer(sFileOld:="C:\Documents\FileOld.xls", _
sFileNew:="C:\Documents\FileNew.xls", _
vSheetOld:="Addresses", _
vSheetNew:="Addresses", _
sHide:="b", _
sKeyFields:="ID", _
sDBOptions:="gi", _
sAction:="hn")
Beep
If Not IsArray(vSynk) Then
MsgBox vSynk, vbExclamation
ElseIf vSynk(1, 2) = 0 Then
MsgBox "No differences noted.", vbInformation
Else
sMsg = "Synkronizer found differences:" & vbNewLine & vbNewLine
For i = 1 To UBound(vSynk)
sMsg = sMsg & vSynk(i, 1) & ": " & vSynk(i, 2) & vbNewLine
Next i
MsgBox sMsg, vbInformation
End If
Exit Sub
errH:
MsgBox Err.Description, vbExclamation
End Sub
This procedure compares one Master-file (Master.xls) with all files within the path "C:\Documents\New\" and produces a difference report for every comparison. The difference reports are saved in the path "C:\Documents\Reports\".
Public Sub Example_5()
Dim sFile As String
Dim vSynk As Variant
Dim sFileOld As String
Dim sFileNew As String
Dim sPathNew As String
Dim sPathReport As String
Dim sFileReport As String
On Error GoTo errH
sPathReport = "C:\Documents\Reports\"
sFile = Dir("C:\Documents\Reports\*.*")
sFileOld = "C:\Documents\Old\Master.xls"
sPathNew = "C:\Documents\New\"
sPathReport = "C:\Documents\Reports\"
sFileNew = Dir(sPathNew & "*.xls")
Do While sFileNew <> ""
sFileReport = "Difference Report " & sFileNew
vSynk = Synkronizer(sFileOld:=sFileOld, _
sFileNew:=sPathNew & sFileNew, _
vSheetOld:=1, _
vSheetNew:=1, _
sAction:="r", _
sReportFile:=sPathReport & sFileReport)
If Not IsArray(vSynk) Then
MsgBox vSynk, vbExclamation
End
End If
Workbooks(sFileNew).Close SaveChanges:=True
Workbooks(sFileReport).Close SaveChanges:=False
sFileNew = Dir
Loop
Beep
MsgBox "Finished"
Exit Sub
errH:
MsgBox Err.Description, vbExclamation
End Sub