W tym artykule pobierzemy dane z zamkniętego skoroszytu do pola listy w formie użytkownika za pomocą VBA.
Surowe dane dla tego przykładu znajdują się w zakresie A2:B10 w skoroszycie „23SampleData.xls”, który znajduje się w ścieżce pliku „D:\Excelforum\ExcelForum office\excel tip old code\Shared Macro\23\”.
Stworzyliśmy dwa przyciski poleceń w głównym arkuszu do uruchamiania dwóch różnych formularzy użytkownika. Każdy przycisk polecenia jest powiązany z różnymi formularzami użytkownika.
Wyjaśnienie logiczne
W tym przykładzie używane są dwa różne sposoby pobierania danych z zamkniętego skoroszytu. To są:-
-
Otwórz zamknięty skoroszyt i pobierz dane
-
Korzystanie z połączenia ADODB
Otwórz zamknięty skoroszyt i pobierz dane
Możliwe jest ustawienie właściwości RowSource kontrolki ListBox, aby uzyskać dane z innego skoroszytu, przypisując wartość do właściwości RowSource w następujący sposób:
„[Nazwa pliku.xls]Arkusz1?!$B$1:$B$15
ListBox Control wyświetli wartości tylko wtedy, gdy drugi skoroszyt jest otwarty.
Aby pobrać dane z zamkniętego skoroszytu, utworzymy makro, aby otworzyć drugi skoroszyt bez zauważania tego przez użytkownika i pobrać dane ze skoroszytu w celu dodania elementów w polu listy i zamknięcia skoroszytu.
Kliknięcie przycisku „Wybierz” aktywuje formularz użytkownika „UserForm1”. Zdarzenie inicjalizacji formularza użytkownika służy do dodawania elementów w polu listy. To zdarzenie najpierw otwiera zamknięty skoroszyt, a następnie przypisuje wartość z zakresu do wariantu „ListItems”. Po przypisaniu wartości skoroszyt jest zamykany, a elementy są dodawane do pola listy.
Pole listy służy do wybierania nazwy z istniejącej listy wartości. Naciśnięcie przycisku „OK” spowoduje wyświetlenie wybranej nazwy.
Korzystanie z połączenia ADODB
ActiveX Data Objects (ADO) to wysokopoziomowy, łatwy w użyciu interfejs do połączeń OLE DB. Jest to interfejs programistyczny umożliwiający dostęp do danych w bazie danych i manipulowanie nimi.
Aby utworzyć połączenie ADODB, będziemy musieli dodać do projektu bibliotekę ADO.
Aby dodać odniesienie, wybierz z menu Narzędzia > Odniesienie.
Kliknięcie przycisku „Połączenie ADODB” w arkuszu roboczym aktywuje formularz użytkownika „UFADODB”. W przypadku inicjalizacji tego formularza użytkownika użyliśmy połączenia ADODB do pobrania danych z zamkniętego skoroszytu. Stworzyliśmy niestandardową funkcję zdefiniowaną przez użytkownika (UDF) „ReadDataFromWorkbook”, aby nawiązać połączenie i pobrać dane z zamkniętego skoroszytu do tablicy.
Użyliśmy innego UDF „FillListBox”, aby dodać elementy w polu listy podczas inicjowania formularza użytkownika. Pole listy wyświetli dane w dwóch kolumnach, jedna kolumna zawiera imię i druga kolumna zawiera wiek.
Naciśnięcie przycisku „OK” po wybraniu pozycji w polu Lista spowoduje wyświetlenie komunikatu informacyjnego o wybranej pozycji.
Proszę postępować zgodnie z poniższym kodem!
Option Explicit Sub running() UserForm1.Show End Sub Sub ADODBrunning() UFADODB.Show End Sub 'Dodaj poniższy kod w formularzu użytkownika UFADODB Option Explicit Private Sub CommandButton1_Click() Dim name1 As String Dim age1 As Integer Dim i As Integer 'Przypisz wybrany wartość w polu listy do zmiennej name1 i age1 For i = 0 Do ListBox1.ListCount - 1 If ListBox1.Selected(i) Then name1 = ListBox1.Value age1 = ListBox1.List(ListBox1.ListIndex, 1) Exit For End If Next ' Wyładuj formularz użytkownika Wyładuj mnie 'Wyświetlanie wyjścia MsgBox "Wybrałeś " & imię1 & ". Jego wiek to " & wiek1 & " lat." End Sub Private Sub UserForm_Initialize() 'Wypełnianie ListBox1 danymi z zamkniętego skoroszytu Dim tArray As Variant 'Wywołanie funkcji ReadDataFromWorkbook w celu pobrania danych z określonego zakresu do tablicy 'Zmień ścieżkę zgodnie z wymaganiami, "Sample_data" nazywa się zdefiniowany zakres tArray = ReadDataFromWorkbook ("D:\Excelforum\ExcelForum office\excel tip old code\Shared Macro\23\23SampleData.xls", "Sample_Data") 'Wywołanie funkcji FillListBox do dodawania elementów w polu listy 'Przypisz obiekt pola listy i tarray jako parametr FillListBox Me .ListBox1, tArray 'Zwalnianie zmiennych tablicowych i zwalnianie pamięci używanej dla ich elementów. Erase tArray End Sub Private Sub FillListBox(lb As MSForms.ListBox, RecordSetArray As Variant) 'Wypełnianie pola listy lb danymi z RecordSetArray Dim r As Long, c As Long With lb .Clear 'Przypisywanie wartości do pola listy 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 'Nie zaznaczanie żadnej pozycji w polu listy domyślnie .ListIndex = -1 End With End Sub Private Function ReadDataFromWorkbook(SourceFile As String, _ SourceRange As String) As Variant ' wymaga odwołania do biblioteki Microsoft ActiveX Data Objects ' (menu Narzędzia > Odwołania w VBE ) Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset Dim dbConnectionString As String 'Zadeklarowanie ciągu połączenia i sterownika wymaga nawiązania połączenia dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};ReadOnly=1;DBQ =" & SourceFile 'Tworzenie nowego połączenia ADODB Ustaw dbConnection = Nowy ADODB.Connection On Error GoTo InvalidInput 'Otwórz połączenie z bazą danych dbConnection.Open dbConnectionString 'Pobieranie zestawu rekordów ze zdefiniowanego nazwanego zakresu Set rs = dbConnection.Execute("[" & SourceRange & "]") On Error GoTo 0 'Zwraca dwa tablica wymiarowa ze wszystkimi rekordami w rs ReadDataFromWorkbook = rs.GetRows 'Zamknij zestaw rekordów i połączenie z bazą danych rs.Close dbConnection.Close Set rs = Nic nie ustawiono dbConnection = Brak funkcji wyjścia 'Kod obsługi błędu InvalidInput: MsgBox "Plik źródłowy lub zakres źródłowy jest nieprawidłowy!", _ vbExclamation, "Pobierz dane z zamkniętego skoroszytu" End Function 'Dodaj poniższy kod w UserForm1 Option Explicit Private Sub CommandButton1_Click() Dim name1 As String Dim i As Integer 'Przypisz wybraną wartość do zmiennej name1 For i = 0 Do ListBox1.ListCount - 1 If ListBox1.Selected(i) Następnie name1 = ListBox1.Value Zakończ do końca Jeśli dalej 'Wyładuj formularz użytkownika Wyładuj mnie 'Wyświetl wybraną nazwę MsgBox "Wybrałeś " & name1 & "." End Sub Private Sub UserForm_Initialize() Dim ListItems As Variant, i As Integer Dim SourceWB As Workbook 'Wyłączanie aktualizacji ekranu Application.ScreenUpdating = False With Me.ListBox1 'Usuń istniejące wpisy z listy .Clear 'Otwórz skoroszyt źródłowy jako ReadOnly Set SourceWB = Workbooks.Open("D:\Excelforum\ExcelForum office\excel tip old code\Shared Macro\23\23SampleData.xls", _ False, True) 'Pobierz żądany zakres wartości ListItems = SourceWB.Worksheets(1 ).Range("A2:A10").Value 'Zamknij skoroszyt źródłowy bez zapisywania zmian SourceWB.Close False Set SourceWB = Nic Application.ScreenUpdating = True 'Konwertuj wartości na tablicę pionową ListItems = Application.WorksheetFunction.Transpose(ListItems) For i = 1 To UBound(ListItems) 'Wypełnij pole listy .AddItem ListItems(i) Next i 'Domyślnie nie wybierając żadnych elementów, ustaw 0, aby wybrać pierwszy element .ListIndex = -1 End With End Sub
Jeśli podobał Ci się ten blog, podziel się nim ze znajomymi na Facebooku. Możesz również śledzić nas na Twitterze i Facebooku.
Chcielibyśmy usłyszeć od Ciebie, daj nam znać, jak możemy poprawić naszą pracę i uczynić ją lepszą dla Ciebie. Napisz do nas na stronie e-mail