In diesem Artikel holen wir Daten aus der geschlossenen Arbeitsmappe in das Listenfeld in Benutzerform mit VBA.
Die Rohdaten für dieses Beispiel befinden sich im Bereich A2:B10 in der Arbeitsmappe „23SampleData.xls“, die im Dateipfad „D:\Excelforum\ExcelForum office\excel tip old code\Shared Macro\23\“ abgelegt wird.
Wir haben zwei Befehlsschaltflächen auf dem Hauptarbeitsblatt erstellt, um zwei verschiedene Benutzerformulare auszuführen. Jede Befehlsschaltfläche ist mit verschiedenen Benutzerformularen verknüpft.
Logikerklärung
In diesem Beispiel werden zwei verschiedene Möglichkeiten verwendet, um Daten aus der geschlossenen Arbeitsmappe abzurufen. Diese sind:-
-
Öffnen Sie die geschlossene Arbeitsmappe und rufen Sie die Daten ab
-
Verwenden der ADODB-Verbindung
Öffnen Sie die geschlossene Arbeitsmappe und rufen Sie die Daten ab
Es ist möglich, die RowSource-Eigenschaft eines ListBox-Steuerelements festzulegen, um Daten aus einer anderen Arbeitsmappe abzurufen, indem Sie der RowSource-Eigenschaft wie folgt einen Wert zuweisen:
‘[Dateiname.xls]Tabelle1?!$B$1:$B$15
ListBox Control zeigt Werte nur an, wenn die andere Arbeitsmappe geöffnet ist.
Um die Daten aus einer geschlossenen Arbeitsmappe abzurufen, erstellen wir ein Makro, um die andere Arbeitsmappe zu öffnen, ohne dass der Benutzer dies bemerkt, und ruft Daten aus der Arbeitsmappe ab, um Elemente in das Listenfeld hinzuzufügen und die Arbeitsmappe zu schließen.
Durch Klicken auf die Schaltfläche „Auswählen“ wird das Benutzerformular „UserForm1“ aktiviert. Das Initialize-Ereignis des Benutzerformulars wird zum Hinzufügen von Elementen im Listenfeld verwendet. Dieses Ereignis öffnet zunächst die geschlossene Arbeitsmappe und weist dann den Wert im Bereich der Variante „ListItems“ zu. Nach dem Zuweisen des Werts wird die Arbeitsmappe geschlossen und Elemente werden dem Listenfeld hinzugefügt.
Das Listenfeld wird verwendet, um einen Namen aus den vorhandenen Listenwerten auszuwählen. Durch Drücken der Taste „OK“ wird der ausgewählte Name angezeigt.
Verwenden der ADODB-Verbindung
ActiveX Data Objects (ADO) ist eine benutzerfreundliche Schnittstelle auf hoher Ebene für die OLE DB-Verbindung. Es ist eine Programmierschnittstelle, um auf Daten in einer Datenbank zuzugreifen und diese zu bearbeiten.
Um eine ADODB-Verbindung zu erstellen, müssen wir die ADO-Bibliothek zum Projekt hinzufügen.
Um eine Referenz hinzuzufügen, wählen Sie im Menü Extras > Referenz.
Durch Klicken auf die Schaltfläche „ADODB-Verbindung“ auf dem Arbeitsblatt wird das Benutzerformular „UFADODB“ aktiviert. Im Initialisierungsereignis dieses Benutzerformulars haben wir die ADODB-Verbindung verwendet, um Daten aus der geschlossenen Arbeitsmappe abzurufen. Wir haben eine benutzerdefinierte benutzerdefinierte Funktion (UDF) "ReadDataFromWorkbook" erstellt, um die Verbindung herzustellen und die Daten aus der geschlossenen Arbeitsmappe in das Array zu holen.
Wir haben eine weitere UDF „FillListBox“ verwendet, um während der Initialisierung des Benutzerformulars Elemente in das Listenfeld hinzuzufügen. Das Listenfeld zeigt die Daten in zwei Spalten an, eine Spalte enthält den Namen und die zweite Spalte enthält das Alter.
Durch Drücken der Taste „OK“ nach Auswahl des Elements im Listenfeld wird die Informationsmeldung zum ausgewählten Element angezeigt.
Bitte folgen Sie unten für den Code
Option Explicit Sub running() UserForm1.Show End Sub Sub ADODBrunning() UFADODB.Show End Sub 'Unten Code im UFADODB-Benutzerformular hinzufügen Option Explicit Private Sub CommandButton1_Click() Dim name1 As String Dim age1 As Integer Dim i As Integer 'Ausgewähltes zuweisen Wert im Listenfeld zu Variable name1 und alter1 For i = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(i) Then name1 = ListBox1.Value age1 = ListBox1.List(ListBox1.ListIndex, 1) Exit For End If Next ' Benutzerformular entladen Unload Me 'Anzeige der Ausgabe MsgBox "Sie haben " & name1 & " ausgewählt. Sein Alter ist " & age1 & " yrs." End Sub Private Sub UserForm_Initialize() 'Füllen von ListBox1 mit Daten aus einer geschlossenen Arbeitsmappe Dim tArray As Variant 'Aufruf der Funktion ReadDataFromWorkbook zum Abrufen von Daten aus dem angegebenen Bereich zum Array 'Pfad entsprechend Ihrer Anforderung ändern, "Sample_data" heißt definierter Bereich tArray = ReadDataFromWorkbook ("D:\Excelforum\ExcelForum office\excel tip old code\Shared Macro\23\23SampleData.xls", "Sample_Data") 'Aufruf der Funktion FillListBox zum Hinzufügen von Einträgen in die Listbox 'Listbox-Objekt und Tarray als Parameter zuweisen FillListBox Me .ListBox1, tArray 'Array-Variablen freigeben und den für ihre Elemente verwendeten Speicher freigeben. Erase tArray End Sub Private Sub FillListBox(lb As MSForms.ListBox, RecordSetArray As Variant) 'Listbox lb mit Daten aus RecordSetArray füllen Dim r As Long, c As Long With lb .Clear 'Wert der Listbox zuweisen For r = LBound(RecordSetArray , 2) To UBound(RecordSetArray, 2) .AddItem For c = LBound(RecordSetArray, 1) To UBound(RecordSetArray, 1) .List(r, c) = RecordSetArray(c, r) Next c Next r 'Kein Element auswählen im Listenfeld standardmäßig .ListIndex = -1 End With End Sub Private Function ReadDataFromWorkbook(SourceFile As String, _ SourceRange As String) As Variant ' erfordert eine Referenz auf die Microsoft ActiveX Data Objects Bibliothek ' (Menü Extras > Referenzen in der VBE ) Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset Dim dbConnectionString As String 'Die Deklaration einer Verbindungszeichenfolge und des Treibers erfordern für den Verbindungsaufbau dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};ReadOnly=1;DBQ =" & SourceFile 'Erstellen einer neuen ADODB-Verbindung Set dbConnection = New ADODB.Connection On Error GoTo InvalidInput 'Öffnen der Datenbankverbindung dbConnection.Open dbConnectionString 'Abrufen des Recordsets aus dem definierten benannten Bereich Set rs = dbConnection.Execute("[" & SourceRange & "]") On Error GoTo 0 'Gibt zwei zurück dimensionales Array mit allen Datensätzen in rs ReadDataFromWorkbook = rs.GetRows 'Recordset und Datenbankverbindung schließen rs.Close dbConnection.Close Set rs = Nichts Set dbConnection = Nichts Exit Function 'Code zur Fehlerbehandlung InvalidInput: MsgBox "Die Quelldatei oder der Quellbereich is invalid!", _ vbExclamation, "Get data from closed workbook" End Function 'Fügen Sie folgenden Code in UserForm1 hinzu Option Explicit Private Sub CommandButton1_Click() Dim name1 As String Dim i As Integer 'Ausgewählten Wert der Variablen name1 zuweisen For i = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(i) Then name1 = ListBox1.Value Exit For End If Next 'Entladen des Benutzerformulars Unload Me 'Anzeige des ausgewählten Namens MsgBox "Sie haben " & name1 & " ausgewählt." End Sub Private Sub UserForm_Initialize() Dim ListItems As Variant, i As Integer Dim SourceWB As Workbook 'Bildschirmaktualisierungen deaktivieren Application.ScreenUpdating = False With Me.ListBox1 'Vorhandene Einträge aus der Listbox entfernen .Clear 'Quellarbeitsmappe als ReadOnly Set öffnen SourceWB = Workbooks.Open("D:\Excelforum\ExcelForum office\excel tip old code\Shared Macro\23\23SampleData.xls", _ False, True) 'Gewünschten Wertebereich abrufen ListItems = SourceWB.Worksheets(1 ).Range("A2:A10").Value 'Quellarbeitsmappe schließen, ohne Änderungen zu speichern SourceWB.Close False Set SourceWB = Nothing Application.ScreenUpdating = True 'Werte in ein vertikales Array umwandeln ListItems = Application.WorksheetFunction.Transpose(ListItems) For i = 1 To UBound(ListItems) 'Listbox ausfüllen .AddItem ListItems(i) Next i 'Standardmäßig keine Elemente auswählen, auf 0 setzen, um das erste Element auszuwählen .ListIndex = -1 End With End Sub
Wenn Ihnen dieser Blog gefallen hat, teilen Sie ihn mit Ihren Freunden auf Facebook. Außerdem können Sie uns auf Twitter und Facebook folgen.
Wir würden uns freuen, von Ihnen zu hören, lassen Sie uns wissen, wie wir unsere Arbeit verbessern und für Sie verbessern können. Schreiben Sie uns auf der E-Mail-Site