Excel VBA - Vergleiche von Werten unterschiedlicher Tabellen

Naja, ich poste trotzdem mal die SQL-Lösung. Denn für eine Andere Lösung habe ich keine Geduld.
Siehe Anhang
Beim klicken auf den Butten wir das Testscript gestartet. Die Resultate werden einem Sheet namens "Check" gespeichert.
Zahl bedeutet, eine Abweichung. Die Zahl selber ist die Tatsächliche Zahl des Tages (Bsp E3)
0 bedeutet, der Datensatz ist vorhanden, hat aber keine Abweichung (Bsp D2)
Leer bedeutet, der Tag hat den entsprechenden Datensatz nicht drin (Bsp E2)
2017-04-11_125701.png
 

Anhänge

  • test123.zip
    32,7 KB · Aufrufe: 3
Hallo Yaslaw,

vorab schonmal ein nettes Danke für die schnelle Hilfe.

Auch wenn deine Lösung für mich im Moment noch nicht ganz nachvollziehbar ist,so würde sie für meine Zwecke völlig ausreichen.

Verstanden hab ich folgendes:

E2 =leer > diesen Datensatz(Tour) gibt es in entsprechendem Tagesbericht nicht
D2 und F2 = 0 > diese Datensätze sind konform,also keine Abweichungen

Was ich nicht verstehe:

E3 = 0 > Die Zahl selber ist die Tatsächliche Zahl des Tages (Bsp E3)

Da ich deine Lösung noch nicht an meine Datei angepasst habe,noch ein paar Fragen

1. Die Zeilennummer in "Check" entspricht auch der Zeilennummer im jeweiligen Tagesbericht?
2. Das Modul "lib_adodb_for_xls" kann ich zum besseren Verständnis umbenennen?
3. Tabellenblatt "Check" wird durch KLICK selbst erzeugt(für Folgemonate) bzw kann oder muss ich die eventuellen Fehlermeldungen löschen?

Nochmals Danke für die Lösung

Lg Frank
 
Was ich nicht verstehe:
E3 = 0 > Die Zahl selber ist die Tatsächliche Zahl des Tages (Bsp E3)
Nein. Das ist wie bei den anderen. Schau dir die Testdaten an.

Dauerfahrten: B->C 6km
1. April: B->C 5.2km
2. April: C->B 6km
10. April: C->B 5.5km


1) Nicht ganz. Es ist nach von, nach, km der [Dauerfahrten] sortiert (in genau der Reihenfolge)

2) Modulnamen sind problemlos anpassbar. Nimm keine geschützten Namen und keinen Namen deiner Funktion.

3) [Check] wird gelehrt oder erstellt, falls nicht vorhanden. Es sollte eigentlich keine Fehlermeldung kommen
Visual Basic:
    'Checksheet auswählen oder erstellen
On Error Resume Next
    Set wsCheck = ActiveWorkbook.Sheets(C_CHECK_SHEET_NAME)
    If Err.Number <> 0 Then
        Set wsCheck = ActiveWorkbook.Sheets.Add(ActiveWorkbook.Sheets(C_MAIN_SHEET_NAME))
        wsCheck.Name = C_CHECK_SHEET_NAME
    End If
On Error GoTo 0
   
    'Check-Tabelle leeren
    wsCheck.Cells.Clear

Zu Beachten.
- Der Reguläre Ausdruck musst du ggf. deinem Namensshema für die Tages-Tabellen apassen
- Im SQL wird davon ausgegangen, dass die Tabellen "einfach* sind. Erste Zeile die Namen, Rest die Daten. ggf. muss dort der Range angepasst werden
http://wiki.yaslaw.info/doku.php/vba/excel/adodbsql#beliebiger_range. Dann sollte man den Range vorher ermitteln und über Variablen definieren
 
Hi Yaslaw,

habe nun versucht deine Module in meine Datei zu kopieren was auch soweit geklappt hat. Nur zum Laufen bring ich es nicht.Mögliche Fehlerquelle meinerseits könnte folgendes sein:

Alle Tagesberichte fangen mit den zu überprüfenden Werten in Zeile 5 an,also B5,C5 und D5 und gehen dann weiter Zeile 7,9,11 usw.Dasselbe gilt für die Dauerfahrten.Sollte das ein Problem sein,kann ich das ändern.
Dann deine Schaltfläche.Ich habe die Vermutung das du eine andere Version von Excel nutzt als ich,weil ich die Schaltfläche nicht habe.Ein CommandButton verhält sich bir nämlich ganz anders(rechtklick - Code anzeigen fehlt hier).

Wenn ich das Makro "Check" nun manuell starte,bekomme ich folgende Fehlermeldung :

Laufzeitfehler '-2147217904(80040e10)':
Für mindestens einen erforderlichen Parameter wurde kein Wert angeben.Beim Klick auf Debuggen bleibt der Code in der "Public Funktion openRS" in Zeile "rst.Open cmd" stehen.

Desweiteren leuchten mir die Zellen F1-F3 in "Dauerfahrten" immer noch nicht ein sowie die 0 in E3 von "Check",wenn ich deine Erklärung mit dem Bild zu Grunde lege.

Lg Frank
 
Lass dir mal beim debuggen im Direktfenster die Variable cmd ausgeben und poste das Resultat hier,
Code:
? cmd

F1-F3 ins Dauerfahrten ist eine Altlast. Wollte den Check zuerst dort machen. Löschen, ignorieren

Check!E3:
Dauerfahrten: B->C 6km
2. April: C-B 6km.

Also ist die Strecke B->C in beiden Sheets gleich gross. ALso keine Abweichung. -> 0
 
Ich bin mir nicht ganz sicher ob ich das richtig mache.Datei geöffnet,VBA Editor geöffnet und das Direktfenster geöffnet: Da steht folgendes drin:
[auto_open] <
[SetupFunctionIDs] <
[SetupFunctionIDs] >
[PickPlatform] <
[PickPlatform] >
[VerifyOpen] <
[VerifyOpen] > 1
[RegisterFunctionIDs] <
[RegisterFunctionIDs] >
[auto_open] >

Makro "check" manuell gestartet und wo der Code stehen bleibt dann im Direktfenster ? cmd eingeben und Enter gedrückt.Folgende Meldung erscheint auf dem Bildschirm:
Laufzeitfehler 13
Typen unverträglich

Du hast doch den Link zu der Tabelle,kannst du nicht netterweise mal nach dem Fehler schauen?

Lg Frank
 
- Im SQL wird davon ausgegangen, dass die Tabellen "einfach* sind. Erste Zeile die Namen, Rest die Daten. ggf. muss dort der Range angepasst werden
http://wiki.yaslaw.info/doku.php/vba/excel/adodbsql#beliebiger_range. Dann sollte man den Range vorher ermitteln und über Variablen definieren

Ich habe die Ranges angepasst. Zudem eine kleine Anpassung im RegExp damit das ä in März akzeptiert wird
Visual Basic:
Option Explicit


'http://wiki.yaslaw.info/doku.php/vba/excel/adodbsql

Private Const C_MAIN_SHEET_NAME = "Dauerfahrten"
Private Const C_CHECK_SHEET_NAME = "Check"
Private Const C_DEFAULT_RANGE = "B3:D100"

'Der Inhalt von C_SQL_PATTERN in lesbarer Formatierung:
'
'select
'    switch(
'        act.km <> main.km ,     act.km,
'        not isnull(act.km),     0
'    ) as [{#fld_name}]
'from
'    [{#tbl_main}$] main
'    left join (
'        select von, nach, km from [{#tbl_act}$]
'        union select nach, von, km from [{#tbl_act}$]
'    ) act
'    on main.von = act.von
'    and main.nach = act.nach

Private Const C_SQL_PATTERN = _
            "select switch(act.km <> main.km , act.km, not isnull(act.km), 0) as [{#fld_name}] " & _
            "from [{#tbl_main}] main left join ( " & _
                "select von, nach, km from [{#tbl_act}] where von <> '' " & _
                "union select nach, von, km from [{#tbl_act}] where von <> '' " & _
            ") act " & _
            "on main.von = act.von and main.nach = act.nach " & _
            "where main.von <> '' " & _
            "order by main.von, main.nach"

'/**
' * erstellt ein Check-Sheet
' */
Public Sub check()
    Dim ws As Worksheet
    Dim wsCheck As Worksheet
    Dim SQL As String
    Dim colNr As Long
     
    'Checksheet auswählen oder erstellen
On Error Resume Next
    Set wsCheck = ActiveWorkbook.Sheets(C_CHECK_SHEET_NAME)
    If Err.Number <> 0 Then
        Set wsCheck = ActiveWorkbook.Sheets.Add(ActiveWorkbook.Sheets(C_MAIN_SHEET_NAME))
        wsCheck.Name = C_CHECK_SHEET_NAME
    End If
On Error GoTo 0
   
    'Check-Tabelle leeren
    wsCheck.Cells.Clear
   
    'Stammdaten abfüllen
    SQL = "SELECT Von, Nach, Km FROM [" & C_MAIN_SHEET_NAME & "$" & C_DEFAULT_RANGE & "] where von <> '' order by von, nach"
    writeFullData wsCheck.Cells(1, 1), openRs(SQL)
   
    colNr = 3
    'Alle Sheets durchgehen
    For Each ws In ActiveWorkbook.Sheets
        'Prüfen, ob es ein Datumssheet ist
        If rxDataSheet.test(ws.Name) Then
            'Spalte eins nach Rechts rücken
            colNr = colNr + 1
           
            'SQL zusammenschustern
            SQL = Replace(C_SQL_PATTERN, "{#tbl_main}", C_MAIN_SHEET_NAME & "$" & C_DEFAULT_RANGE)
            SQL = Replace(SQL, "{#tbl_act}", ws.Name & "$" & C_DEFAULT_RANGE)
            SQL = Replace(SQL, "{#fld_name}", rxDataSheet.Replace(ws.Name, "$1 $2"))
           
            'Sql öffnen und das Resultat in die Check-Tabelle schreiben
            writeFullData wsCheck.Cells(1, colNr), openRs(SQL)
        End If
    Next ws
End Sub

'/**
' * Regulären Ausdruck, der die Sheetnamen prüft um herauszufinden, ob es sich um ein Datumssheet handelt
' * @return RegExp
' */
Private Property Get rxDataSheet() As Object
    Static rx As Object
    If rx Is Nothing Then
        Set rx = CreateObject("VBScript.RegExp")
        rx.Pattern = "^(\d{1,2})\.\s*(\S+)$"
    End If
    Set rxDataSheet = rx
End Property
 
Hi Yaslaw,

das sieht schon bedeutend besser aus. Bevor ich aber nun behaupte,der Code arbeitet "noch" nicht einwandfrei,hierzu ein paar Fragen:

1. Wenn du mal in den 01.März rein schaust,siehst du,das es eine Tour mehrfach am Tag gibt.Nun habe ich mal bei einer eine andere Km-Zahl eingegeben und Die Überprüfung neu gestartet.Im "Check" zeigt er mir diese geänderte Km-Zahl in Zelle D5 zwar unter 01. März an,aber ohne Bezeichnung von wo nach wo. Hängt es eventuell damit zusammen,das diese Tour quasi die Hin- oder Rücktour ist und diese nicht in den Dauerfahrten vermerkt ist?

2. Habe im 01.März jetzt mal bewusst eine falsche Km-Zahl eingeben,im Check taucht die Tour auch auf,aber mit der Km-Zahl aus den Dauerfahrten aber ansonsten in keinem Datum.Deiner Erklärung nach "Leer bedeutet, der Tag hat den entsprechenden Datensatz nicht drin" hat der Code ja richtig gearbeitet,nur müsste es ja umgekehrt sein. Wenn in Dauerfahrten 21,0 steht und in 01.März-22,0 07.März-20,0 stehen,dann müssten diese Zahlen im Check unter entsprechendem Datum auch stehen,oder?

3. An welcher Stelle im Code kann/muss ich das Datumsformat ändern,wenn ich denn muss? Wenn es tatsächlich mal 31 Tage gibt,wird die Datumsbezeichnung woh zu lang sein.

Ohne Zweifel ist diese SQL basierte Lösung eine geile Sache,nur schade,dass es so umständlich ist,den Code ein wenig zu schrumpfen und so an meine Bedürfnisse anzupassen,da ich eigentlich soviel gar nicht brauche.

Lg Frank
 
Zurück