Live-Forum - Die aktuellen Beiträge
Datum
Titel
19.05.2024 19:42:43
zeilen löschen wenn
19.05.2024 17:55:54
Problem mit Formel countif
19.05.2024 17:28:17
E-Mailadressen Alphabetisch sortieren
19.05.2024 16:12:42
Erweiterung verketten, zweite Kommasetzung und Umbruch
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1920to1924
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1920to1924
1920to1924
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

QueryTables.Count ist Null

QueryTables.Count ist Null
22.02.2023 11:46:28
Alex
Hallo Zusammen,
ich möchte in VBA sicherstellen, dass Teile der Tabelle nach einer Änderung kopiert werden, allerdings nur, wenn die Änderung nicht durch das Aktualisieren der Abfrage (Daten aus Access-DB) erfolgt. Dafür habe ich folgenden Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim qT As queryTable
    Set qT = ActiveSheet.QueryTables(1)
    If Not Intersect(Target, qT.ResultRange) Is Nothing Then
    Else
        ActiveSheet.ListObjects("Tbl_Mittelabfluss_VS2").DataBodyRange.Copy _
        Destination:=Worksheets("V2_Kopiertabelle").Range("A1")
    End If
End Sub

Allerdings kommt immer der Fehler, dass QueryTables(1) nicht existiert. Auch die Abfrage ActiveSheet.QueryTables.Count wirft das Ergebnis "0" aus. Liegt es vielleicht an manuell an die Abfragetabelle angefügten Spalten?
VG
Alex

6
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: QueryTables.Count ist Null
22.02.2023 13:57:23
Yal
Hallo Alex,
im manchen Kontext sind die QueryTable ein Unterobjekt von ListObject, d.h. Du musst
    Set qT = ActiveSheet.ListObjects(1).QueryTable
verwenden.
Aber eigentlich ist Dir nicht die QueryTable relevant sondern das ListObject selbst, also:
Private Sub Worksheet_Change(ByVal Target As Range)
    With ActiveSheet.ListObjects(1)
        If Intersect(Target, .DataBodyRange) Is Nothing Then 'wenn die Zelle, die geändert wurde, nicht teil der betrachtete ListObject ist
            .DataBodyRange.Copy Destination:=Worksheets("V2_Kopiertabelle").Range("A1")
        End If
    End With
End Sub
Vielleicht willst Du aber die Aktualisierung der ListObject abfangen:
Private Sub Worksheet_TableUpdate(ByVal Target As TableObject)
Dim LO As ListObject
    Set LO = Target.ListObject
    '...
End Sub
VG
Yal
Anzeige
AW: QueryTables.Count ist Null
22.02.2023 16:58:16
Alex
Hi, danke schon mal für deine Antwort.
Ich glaube ich muss mein Problem nochmal etwas genauer beschreiben. Ich habe eine Abfrage auf eine Access-DB, die in Excel eine formierte Tabelle erstellt bzw. aktualisiert. An diese formatierte Tabelle habe ich manuell Spalten angefügt, die ich beliebig befülle. Um nach einer Aktualisierung der Abfrage keinen Datensalat in diesen manuellen Spalten zu haben, möchte ich die Spalten VOR der Aktualisierung der Abfrage kopieren, um sie dann danach wieder zurück zu kopieren. Die Zeilen kann ich eindeutig zurodnen über eine ID.
Ich wollte es so lösen, dass die Tabelle im Change-Ereignis nur kopiert wird, wenn die Änderung nicht aus der Aktualisierung der Abfrage kommt. Daher kann ich hier nicht über TableUpdate gehen (weil ich ja bei manuellen Eingaben kopieren möchte - im TableUpdate habe ich erfolgreich das Zurückkopieren programmiert). Gleiches gilt dann für das ListObject, das betrachtet ja dann die komplette Tabelle (Abfrage + manuelle Spalten). Du hattest mir auch schon mal ein paar Tipps gegeben, das Problem über PowerQuery zu lösen, das habe ich aber letztlich doch nicht hinbekommen.
Anzeige
AW: QueryTables.Count ist Null
22.02.2023 17:33:31
Yal
Hallo Alex,
ok, jetzt bin ich bei Dir.
Ich schlage dann eine komplett anderen Vorgehensweise mit Power Query. Eine neue Saue durchs Dorf, die man aber sehr schnell lieb hat:
_ Datenbestand aus Excel per Power Query lesen
_ Daten aus Access per Power Query abrufen
_ beide miteinander kombinieren
_ Ergebnis über den bisherige Datenbestand ablegen.
Da es unter Umstand schief gehen kann, müsste es sicher sein, dass Du die Datei ohne zu speichern schliessen kannst. Was mit Cloud/365 manchmal nicht drin ist.
Es geht so:
_ dein Datenbestand in einer Tabelle umwandeln: Menü "Einfügen", "Tabelle"
_ du kommst in Power Query Editor rein, aber wir gehen sofort mit "Schliessen & laden in ..." wieder raus, "Nur Verbindung herstellen"
_ über Menü "Daten", "Neue Abfrage", "Aus Datenbank", "aus Microsoft Access-Datenbank" eine Abfrage gestalten, der deine bisherige Datenabfrage entspricht. Nicht "Schliessen & Laden"
_ Dein erste Query und die Access-Query kombinieren (siehe dazu pkt 4 in https://excelhero.de/power-query/power-query-ganz-einfach-erklaert )
_ "Schliessen & laden in ...", "Nur Verbindung herstellen"
_ in den Abfrageliste rechts, die kombinierte Abfrage rechtklicken und "Laden in ..." auswählen, in einer neues Arbeitsblatt
_ Du bekommst eine Ergebnistabelle "Tabellex" (Name kannst Du ändern, aber für die Erklärung bleiben wir bei Tabellex)
_ in den Abfrageliste rechts, auf deine erste Query rechtsklick und "Bearbeiten", in der angewendete Schritte "Quelle", der Name der Quelle in "Tabellex" ändern.
Somit haben wir:
_ T1x aus dem Excel (lesend)
_ T2 aus Access (lesend)
_ Tx und T2 kombinieren sich und werden in Tx herausgegeben (schreibend)
Da zuerst gelesen dann geschrieben werden deine manuelle Einträge immer mitgenommen und an der richtige Stelle angelegt, es sei denn der Schlüssel-Element ist in Access nicht mehr vorhanden. Neue Schlüssel-Element werden hinzugefügt und können manuel angreichert.
VG
Yal
Anzeige
AW: QueryTables.Count ist Null
22.02.2023 20:01:18
Alex
Ok, das hat auf jeden Fall funktioniert. Die Logik dahinter habe ich zwar noch nicht ganz verstanden, aber ist ja nicht so schlimm ;-)
Nun habe ich aber ein neues Problem: Ich habe in meinen "manuellen" Daten auch Spalten mit Formeln. Die Bereiche werden jedoch nur als Werte übertragen. Gibt es dafür auch irgendeine Lösungsmöglichkeit?
AW: QueryTables.Count ist Null
22.02.2023 21:18:48
Yal
Hallo Alex,
ja, es gibt einen Sonderfall: Du musst vor der Herausgabe die Spalten entfernen, die die Formelspalten überschreiben würden.
Nehmen wir an, Du hast 5 Spalten a,b,c,d,e. Die 2 letzten d,e sind Formelspalten. Du liest das Vorhandenen in PQ und hole die Access-Daten, machst daraus dein "ready to export"-Abfrage. Diese Abfrage sollte nur 3 Spalten haben: a,b,c. So werden beim herausgeben die Spalten d,e nicht überschrieben und die Formeln bleiben erhalten. Bei Ausgeben einer PQ-Abfrage werden alle Daten der Zieltabelle gelöscht bis auf eine fast leere Zeile: nur die Formeln bleiben. Es führt dazu, dass in jede Zelle deiner Spalte d dieselbe Formel vorhanden ist. Dito in e.
Also deine Formelspalten müsste die rechtesten Spalten deiner Ausgabe/Eingabe-Bereich.
Die Logik dahinter? Du kombinierst 2 Eingänge A und B zu einem Ergbenis C, in dem ein Teil von A durch B ersetzt wird (spaltenweise). Aber A ist das Ergebnis C von deiner vorigen Aktualisierung, angereichert durch deine Eingaben. So entsteht einen Kreislauf.
Was besonders "cool" an Power Query ist, dass es einem zwingt, klar strukturierte Anwendung zu bauen. Recht schnell wirst Du deinen Excels in 2 Kategorien klassifizieren: die klar strukturierten: eine Zeile verstanden, alles verstanden und die ... anderen. Die wirst Du irgendwo zwischen verachten und hassen :-)
VG
Yal
Anzeige
AW: QueryTables.Count ist Null
23.02.2023 08:37:23
Alex
Danke Yal! Wirklich toll wie ausführlich du immer antwortest. Du hast mir sehr geholfen.
VG
Alex

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige