✔ Excel VBA - Vergleiche von Werten unterschiedlicher Tabellen
- Themenstarter Timotheus1992
- Beginndatum
Inaktiver Nutzer
Grünschnabel
Leider bin ich in dem Bezug völlig unbedarft
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)
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)
Inaktiver Nutzer
Grünschnabel
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
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
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.ClearZu 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
Inaktiver Nutzer
Grünschnabel
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
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,
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
Code:
? cmdF1-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
Inaktiver Nutzer
Grünschnabel
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
[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
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 PropertyInaktiver Nutzer
Grünschnabel
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
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
