Office: (Office 2019) VBA Code Daten aus anderer Tabelle holen klappt nicht

Hallo Zusammen,

Ich habe einen VBA Code, welcher mir automatisch Daten aus einer anderen geschlossenen Tabelle holt. Dieser funktioniert einwandfrei in einer schon länger genutzten Tabelle. Nun habe ich eine weitere Tabelle, mit der ich ebenfalls diese Daten abholen möchte. Dafür habe ich den VBA Code in ein Modul gepackt und bereits gewisse Vorgaben, wie Range und Sheetnamen angepasst. Voller Vorfreude auf die gleich auftauchende Daten wollte ich das Makro mit F8 durchlaufen lassen. Leider ohne Erfolg. Die erste Anweisung ist das Aufheben eines Passwortes. Diese wird komplett übersprungen und er stoppt beim Deklarieren der Variablen ( Dim Connection As New ADODB.Connection) mit der Meldung "Fehler beim Kompilieren: Benutzerdefinierter Typ nicht definiert". Ich komme einfach nicht drauf, wo mein Fehler liegt. Die Pfade passen und und auch die Range ist definiert, aber bis dahin kommt er ja gar nicht. Hat jemand eventuell eine Idee?

Hier der Code

Code:

Sub Daten()

'Passwort entfernen
Sheets("Daten").Unprotect Password:="ts"

'Variablen Deklarieren
Dim Connection As New ADODB.Connection
Dim Query As String
Dim rs As New ADODB.Recordset

'Connection herstellen
Connection.Open "Provider=MSDASQL.1;DSN=Excel Files;DBQ=C:\Users\schub\Desktop\Tagesquittungen\Daten.xlsm;HDR=Yes';"

'Query definieren und ausführen
Query = "SELECT * FROM [Daten$B2:F103]"
rs.Open Query, Connection

'Daten ins Tabellenblatt laden
Tabelle1.Range("B3").CopyFromRecordset rs

'Connection schließen
Connection.Close

'Passwort setzen
Sheets("Daten").Protect Password:="ts"


End Sub

Packe noch einen Screenshot, von der Meldung bei. Herzlichen Dank für eure Hilfe.

 

Hallo

Bei mir klappt das Daten holen ohne Probleme ohne VBA mit Power Query.

 

Da bin leider nicht so ein Held, was das Thema angeht. Lädt Power Query die Daten automatisch aus geschlossenen Tabellen ab, oder muss man das anstoßen? Mein VBA Makro wird beim öffner der Tabelle automatisch und ohne Eingriff ausgeführt und so muss es auch sein. Wie gesagt, mein Code oben funktioniert prima in einer anderen Tabelle.

 

Hallo

Wenn du möchtest das ich dir weiter helfe, dann Datei die dem Original im Aufbau entspricht und dein Wunschergebnis.

 

Gerne und Danke. Hier ist eine Datei mit dem Namen "Daten". Darin befinden sich die Daten, die mit dem Makro, (welches in Tabelle "Test" ausgeführt werden muss), in die Tabelle "Test" übertragen werden soll. Musst lediglich noch den Pfad zur Datei "Daten" im Makro ändern. Wenn du mich nach meinem Wunschergebnis fragst, müssen einfach alle Daten in der Datei "Test" ankommen. Falls es für dich ein leichtes ist, sollen beim Übertragen allerdings noch zwei Spalten getauscht werden. Das kann ich leider in den Ursprungsdaten nicht tun, da eben noch andere Tabellen dort die Daten in dieser Reihenfolge benötigen und abholen. In meiner Datei "Test" benötige ich die Spalten C und D vertauscht. (Name, Vorname, Steuernummer, Stadt). Das nur, weil du nach meinem Wunschergebnis gefragt hast.

Hier nochmal der VBA Code für die "Test" Datei:

Code:

Sub Daten_holen()

'Variablen Deklarieren
Dim Connection As New ADODB.Connection
Dim Query As String
Dim rs As New ADODB.Recordset

'Connection herstellen
Connection.Open "Provider=MSDASQL.1;DSN=Excel Files;DBQ=C:\Dein_Pfad\Daten.xlsx;HDR=Yes';"

'Query definieren und ausführen
Query = "SELECT * FROM [Daten$A1:D10]"
rs.Open Query, Connection

'Daten ins Tabellenblatt laden
Tabelle1.Range("A1").CopyFromRecordset rs

'Connection schließen
Connection.Close

End Sub

Dateien siehe Anhang.

Herzlichen Dank für deine Hilfe.

 

Hallo
Habe dir eine Mail geschickt, nicht angekommen?

 

Vielen Dank für deine Antwort. Gerade bemerkt, dass ich eine Email habe. Es ist halt so, dass ich die Daten nicht in das Beispiel kopieren kann. Mit der Tabelle, wo die Daten landen sollen, arbeiten andere Personen, die sich nicht wirklich auskennen. Da ist es leider schon zu viel verlangt, wenn sie manuell aktualisieren müssen. Im Endeffekt landen diese Daten in einer Tabelle, wo manuell weitere Kriterien pro Position erfasst werden um im Nachgang einen automatischen Quittungsdruck per Serienbrief anzustoßen. Im Endeffekt soll die Tabelle geöffnet werden und die Kundendaten müssen sich automatisch aktualisieren. Daher hatte ich mir genau den VBA Code herausgesucht, der ja auch in einer anderen Tabelle ohne Probleme die Daten aktualisiert. Die Frage ist nur, warum das eben mit anderen Tabellen nicht funktioniert.

 

Hallo
Die PQ kann auch einstellen das sie automatisch Aktualisiert werden, entweder per VBA oder intern von PQ.
Man auch die ganzen Daten von den anderen zusammenfassen und bearbeiten ist auch kein Problem.

 

Hallo,

dann hast du die entsprechende Library nicht aktiviert - unter Tab "Extras" > "Verweise..." >

Den markierten Eintrag in der Liste "Verfügbare Verweise:" suchen und anhaken
Wenn du dann im VBA-Code eingibst: Dim Connection As New ADODB.
erscheint nach Eingeben des Punktes sofort die Liste der enthaltenen Objekte - ein Zeichen, dass die Bibliothek aktiviert ist.

 

das würde ich sehr gerne ausprobieren, aber leider finde ich bei meinen Excel 2019 den Tab "Extras" nicht.

 

Ah, hab es gefunden und angehakt. Ich bin allerdings in der Zwischenzeit auf eine PowerQuery Abfrage umgestiegen und habe zur Abfrage den folgenden VBA Code eingefügt. Dort hängt er sich jetzt bei "Dim DatenVerbindung As WorkbookConnection auf" Muss ich dafür bei den verfügbaren Verweisen noch weiteres dort aktivieren? Herzlichen Dank für eine Antwort.

Code:

Sub Daten_auffrischen()

Dim SucheVerbindung As Long
Dim DatenVerbindung As WorkbookConection

    On Error Resume Next
 
    For Each DatenVerbindung In ThisWorkbook.Connections
        SucheVerbindung = InStr(1, DatenVerbindung.OLEDBConnection, "Provider=Microsoft.Mashup.OleDb.1", vbTextCompare)
    
        If Err.Number <> 0 Then
            Err.Clear
            Exit For
        End If
    
        If SucheVerbindung > 0 Then DatenVerbindung.Refresh
    
    Next DatenVerbindung
    
End Sub

 

Das hast sich erledigt, da ich zur alten und hier ursprünglich beschriebenen Variante zurück gewechselt bin. Es ist jetzt alles so, wie es sein soll. Herzlichen Dank für eure Hilfe.

 

Hallo,

die Deklaration muss lauten:
Dim DatenVerbindung As WorkbookConnection