Synkronizer mit Visual Basic for Applications
Synkronizer kann auch mit Visual Basic for Applications (VBA) benutzt werden. Dies hat den grossen Vorteil, dass wiederkehrende, standardisierte oder komplexe Vergleichsprozesse automatisiert werden können.
Syntax
Die Syntax der Synkronizer-Funktion ist wie folgt:
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
Rückgabewert
Je nach Ergebnis retourniert die Funktion Synkronizer folgende Werte:
| "Err" | Synkronizer konnte die Parameter nicht auswerten oder es sind andere Fehler aufgetaucht. |
| Array | Synkronizer konnte Abweichungen erkennen und retourniert die Differenzen in einem zweidimensionalen Array, welches wie folgt gegliedert ist: |
|
Element(1, 1): Bezeichnung "Total Differenzen" Element(1, 2): Anzahl Total Differenzen Element(2, 1): Bezeichnung "Gelöschte Tabellen" Element(2, 2): Anzahl Gelöschte Tabellen Element(3, 1): Bezeichnung "Hinzugefügte Tabellen" Element(3, 2): Anzahl Hinzugefügte Tabellen Element(4, 1): Bezeichnung "Gelöschte Spalten" Element(4, 2): Anzahl Gelöschte Spalten Element(5, 1): Bezeichnung "Hinzugefügte Spalten" Element(5, 2): Anzahl Hinzugefügte Spalten Element(6, 1): Bezeichnung "Doppelte Indizes" Element(6, 2): Anzahl doppelte Indizes Element(7, 1): Bezeichnung "Doppelte Datensätze" Element(7, 2): Anzahl doppelte Datensätze Element(8, 1): Bezeichnung "Gelöschte Zeilen" Element(8, 2): Anzahl Gelöschte Zeilen Element(9, 1): Bezeichnung "Hinzugefügte Zeilen" Element(9, 2): Anzahl Hinzugefügte Zeilen Element(10, 1): Bezeichnung "Unterschiedliche Werte" Element(10, 2): Anzahl Unterschiedliche Werte Element(11, 1): Bezeichnung "Unterschiedliche Formate" Element(11, 2): Anzahl Unterschiedliche Formate Element(12, 1): Bezeichnung "Verwendete Filter" Element(12, 2): Verwendete Filter |
Parameter
Die Synkronizer-Funktion umfasst folgende Parameter:
| sFileOld | Datei (inkl. Verzeichnis) der 1. Datei (Master). Es können Excel-, Text- und ODC-Dateien eingegeben werden. Access-Datenbanken müssen über eine ODC-Datei angesteuert werden. | |
| sFileNew | Datei (inkl. Verzeichnis) der 2. Datei (Änderungen). | |
| vSheetOld | Tabellennummer oder -name der 1. Datei. Sie können folgende Werte eingeben: | |
| 1 3 "sheet1" "" |
die erste Tabelle wird verglichen die dritte Tabelle wird verglichen die Tabelle mit dem Namen "Tabelle1" wird verglichen alle Tabellen werden verglichen. |
|
| vSheetNew | Tabellennummer oder -name der 2. Datei | |
| sRangeOld | Optional. A1-Bereich der 1. Tabelle. Wenn dieser Parameter leer ist, werden alle Zellen verglichen. | |
| aRangeNew | Optional. A1-Bereich der 2. Tabelle | |
| sCompareType | Optional. Dieser Parameter enthält den Vergleichstyp. Sie können eine oder mehrere Optionen wählen: | |
| v f c |
Vergleich nach Werten (values) Vergleich nach Formeln Vergleich nach Kommentaren |
|
| Bleibt dieser Parameter leer, dann wird ein Vergleich nach "normalen" Werten vorgenommen (siehe auch 2.2.1 Bereich "Inhalte"). Wollen Sie z.B. die Werte "sensitiv" vergleichen, dann geben Sie "vs" ein. | ||
| sHide | Optional. Dieser Parameter enthält die Anzeigeoptionen. Sie können bestimmen, welche Daten ausgeblendet werden sollen. Folgende Optionen stehen zur Verfügung: | |
| c r d v f i b |
Nicht verbundene Spalten Nicht verbundene Zeilen Duplikate Unterschiedliche Zellwerte Unterschiedliche Formate Unveränderte Zellen die Hintergrundfarbe wird vor dem Vergleich gelöscht |
|
| sFormats | Optional. Dieser Parameter enthält die Formatierungsoptionen. Sie können. Folgende Formate stehen zur Auswahl: | |
| n a f b p t |
Zahlen Ausrichtung Schrift Rahmen Muster Schutz |
|
| sKeyFields | Optional. Wenn Sie einen Datenbankvergleich vornehmen wollen, dann müssen Sie hier den Namen des Primärschlüssels eingeben. Bei mehreren Primärschlüsseln müssen die einzelnen Schlüssel mit Semikolons getrennt werden (z.B. "Nachname;Vorname"). Wollen Sie keinen Datenbankvergleich vornehmen, dann bleibt dieses Feld leer. | |
| sDBOptions | Optional. Wenn Sie einen Datenbankvergleich vornehmen, können zusätzlich folgende Parameter eingegeben werden: | |
| s g i |
die Daten werden sortiert die Daten werden gruppiert eine zusätzliche Spalte mit den SynkIDs wird eingefügt |
|
| sLink1on1 | Optional. Wenn die Zeilen und/oder Spalten 1:1 verglichen werden sollen (d.h. wenn keine gelöschten/hinzugefügten Zeilen/Spalten vorhanden sind), können sie diese Optionen wählen: | |
| c r |
die Spalten werden 1:1 verglichen die Zeilen werden 1:1 verglichen |
|
| sAction | Optional. Dieser Parameter enthält die Aktion, welche ausgeführt werden soll. Folgende Zeichen können eingegeben werden: | |
| h r n |
Die Abweichungen werden markiert Ein Abweichungsprotokoll wird erstellt Navigator Symbolleiste |
|
| sReportFile | Optional. Wenn Sie ein Abweichungsprotokoll erstellen wollen, dann müssen Sie den Namen und Verzeichnis des zu erstellenden Protokolls eingeben (z.B. C:\Documents\Report.xls). | |
| bHyperlinks | Optional. Wenn das Abweichungsprotokoll Hyperlinks enthalten soll, dann müssen Sie hier den Wert "True" setzen. | |
| bDeleteBGColor | Option. Falls hier True gesetzt wird, wird die Hintergrundfarbe der Zellen vor dem Vergleich entfernt. | |
| sFilters | Optional. Wenn die Daten gefiltert verglichen werden sollen, dann können folgende Filter gesetzt werden: | |
| s t d c r k f |
sensitiver Vergleich Glätten Datentyp ignorieren unsichtbare Spalten ignorieren unsichtbare Zeilen ignorieren Konstanten ignorieren Formeln ignorieren |
|
| sIgnoreCols | Optional. Wenn bestimmte Spalten vom Vergleich ausgeschlossen werden sollen, dann können Sie mittels dieses Parameters die Spalten eingeben. Geben Sie entweder die Spaltennummern oder Spaltennamen bei einem Datenbankvergleich ein. Die einzelnen Spalten werden mittels Semikolon (;) getrennt. | |
| dTolerance | Optional. Bei numerischen Werten können Sie bestimmen, ob Synkronizer nur Differenzen ab einer bestimmten Grösse als Differenz ausspucken soll. Wenn Sie z.B. 0.10 eingeben, werden nur Differenzen, welche grösser als 0.10 sind, ausgewiesen. | |
| sTextfilter | Optional. Es können auch alphanumerische Felder gefiltert werden. Geben Sie ein "like" oder "RegEx"-Filterkriterium ein. Der Parameter muss wie folgt eingegeben werden: | |
| Like-Kriterium RegEx-Kriterium |
like:done regex:yes|no |
|
Synkronizer in Ihren VBA Skripten benutzen
Damit Sie Synkronizer mit VBA ausführen können, müssen Sie in Ihren Skripten eine Referenz zu Synkronizer herstellen. Führen Sie folgende Schritte aus:
| 1. | öffnen Sie die Excel-Datei, welches das VBA-Programm enthält |
| 2. | öffnen Sie den VBA Editor (Tastaturkombination Alt-F11) |
| 3. | aktivieren Sie Ihre Excel-Datei |
| 4. | Markieren Sie den Menübefehl Extras - Verweise |
| 5. | Suchen Sie in der Liste den Eintrag "Synkronizer95" und markieren das Kontrollkästchen. |
Nun können Sie Synkronizer in Ihren VBA-Programmen verwenden. Nachfolgend sind ein paar VBA-Beispiele aufgeführt, welche Sie gerne verwenden können.
VBA-Beispiele
Dieses Beispiel markiert die Abweichungen in den Tabellen Addresses:
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
Dieses Beispiel markiert die Abweichungen in allen Tabellen der Dateien FileOld.xls und FileNew.xls. Zusätzlich werden alle unveränderten Zeilen ausgeblendet:
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
Diese Prozedur vergleicht die Werte der ersten Tabellen der beiden Dateien miteinander und dokumentiert die Abweichungen in der Datei "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
Diese Prozedur vergleicht die Datenbanken "Addresses" miteinander. Die Datenbanken werden nach dem Primärschlüssel "ID" verglichen. Zudem werden die Daten gruppiert und mit einem ID versehen. Schlussendlich werden die Differenzen markiert und der Navigator wird gestartet.
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
Diese Prozedur vergleicht eine Datei ("Master.xls") mit allen Dateien, welche sich im Verzeichnis "C:\Documents\New\" befinden und erstellt für jeden Vergleich ein separates Abweichungsprotokoll. Die Protokolle werden im Verzeichnis "C:\Documents\Reports" abgelegt.
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\"
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