I den här artikeln kommer vi att hämta data från stängd arbetsbok till listruta i användarform med VBA.
Rådata för detta exempel ligger i intervall A2: B10 på arbetsboken “23SampleData.xls”, som placeras i filvägen “D: \ Excelforum \ ExcelForum office \ excel tip old code \ Shared Macro \ 23 \”.
Vi har skapat två kommandoknappar på huvudarket för att köra två olika användarformulär. Varje kommandoknapp är länkad till olika användarformer.
Logisk förklaring
I det här exemplet används två olika sätt för att hämta data från den stängda arbetsboken. Dessa är:-
-
Öppna den stängda arbetsboken och hämta data
-
Använda ADODB -anslutning
Öppna den stängda arbetsboken och hämta data
Det är möjligt att ställa in egenskapen RowSource för en ListBox -kontroll för att hämta data från en annan arbetsbok genom att tilldela RowSource -egenskapen värde enligt följande:
‘[Filnamn.xls] Blad1?! $ B $ 1: $ B $ 15
ListBox Control visar endast värden om den andra arbetsboken är öppen.
Så för att hämta data från stängd arbetsbok skapar vi ett makro för att öppna den andra arbetsboken utan att användaren märker det och hämtar data från arbetsboken för att lägga till objekt i listrutan och stänga arbetsboken.
Genom att klicka på knappen "Välj" aktiveras användarformen "UserForm1". Initiera händelse för användarformen används för att lägga till objekt i listrutan. Denna händelse öppnar först den stängda arbetsboken och tilldelar sedan värdet i intervallet till "ListItems" -varianten. Efter tilldelning av värdet stängs arbetsboken och objekt läggs till i listrutan.
Listruta används för att välja namn från de befintliga listvärdena. Genom att trycka på "OK" -knappen visas det valda namnet.
Använda ADODB -anslutning
ActiveX Data Objects (ADO) är ett lättanvänt gränssnitt på hög nivå för OLE DB-anslutning. Det är ett programmeringsgränssnitt för att komma åt och manipulera data i en databas.
För att skapa ADODB -anslutning måste vi lägga till ADO -biblioteket i projektet.
För att lägga till referens, välj från menyn Verktyg> Referens.
Genom att klicka på knappen "ADODB Connection" i kalkylbladet aktiveras användarformen "UFADODB". I initialiseringshändelsen för denna användarform har vi använt ADODB -anslutning för att hämta data från den stängda arbetsboken. Vi har skapat en anpassad användardefinierad funktion (UDF) "ReadDataFromWorkbook" för att upprätta anslutningen och hämta data från den stängda arbetsboken till matrisen.
Vi har använt en annan UDF "FillListBox" för att lägga till objekt i listrutan under initialiseringen av användarformen. Listbox visar data i två kolumner, en kolumn innehåller namnet och andra kolumnen innehåller åldern.
Genom att trycka på "OK" -knappen efter att du valt objektet i listrutan visas informationsmeddelandet om det valda objektet.
Följ koden nedan
Alternativ Explicit Sub running () UserForm1.Show End Sub Sub ADODBrunning () UFADODB.Show End Sub 'Lägg till nedan kod i UFADODB användarform Alternativ Option Explicit Private Sub CommandButton1_Click () Dim name1 As String Dim age1 As Integer Dim i As Integer' Tilldela det valda värde i listrutan till variabelnamn1 och ålder1 För i = 0 Till ListBox1.ListCount - 1 Om ListBox1.Selected (i) Sedan namn1 = ListBox1.Value age1 = ListBox1.List (ListBox1.ListIndex, 1) Avsluta för slutet om nästa ' Ladda ur användarformuläret Ladda ur mig Visar utdata MsgBox "Du har valt" & namn1 & ". Hans ålder är" & age1 & "yrs." End Sub Private Sub UserForm_Initialize () 'Fyllning ListBox1 med data från en stängd arbetsbok Dim tArray As Variant' Samtalsfunktion ReadDataFromWorkbook för att få data från specificerat intervall till array 'Ändra sökväg enligt dina krav, "Sample_data" heter definierat intervall tArray = ReadDataFromWorkbook ("D: \ Excelforum \ ExcelForum office \ excel tip old code \ Shared Macro \ 23 \ 23SampleData.xls", "Sample_Data") 'Samtalsfunktion FillListBox för att lägga till objekt i Listbox' Tilldela Listbox -objekt och tarray som parameter FillListBox Me .ListBox1, tArray 'Släpp matrisvariabler och fördela minnet som används för deras element. Radera tArray End Sub Private Sub FillListBox (lb As MSForms.ListBox, RecordSetArray As Variant) 'Filling List box lb with data from RecordSetArray Dim r As Long, c As Long With lb .Clear' Tilldela värde till listbox För r = LBound (RecordSetArray , 2) Till UBound (RecordSetArray, 2) .AddItem För c = LBound (RecordSetArray, 1) Till UBound (RecordSetArray, 1) .Lista (r, c) = RecordSetArray (c, r) Nästa c Nästa r 'Välj inget objekt i listrutan som standard .ListIndex = -1 Avsluta med slut Sub Privat funktion ReadDataFromWorkbook (SourceFile As String, _ SourceRange As String) As Variant 'kräver en referens till biblioteket Microsoft ActiveX Data Objects' (menyverktyg> Referenser i VBE ) Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset Dim dbConnectionString As String 'Deklarerar en anslutningssträng och drivrutinen kräver för att upprätta anslutning dbConnectionString = "DRIVER = {Microsoft Excel Driver (*.xls)}; ReadOnly = 1; DBQ = "& SourceFile 'Skapa en ny ADODB -anslutning Ange dbConnection = Ny ADODB.Connection On Error GoTo InvalidInput 'Öppna databasanslutningen dbConnection.Open dbConnectionString' Hämtar rekordset från definierat namngivet område Set rs = dbConnection.Execute ("[" & SourceRange & "]") On Error GoTo 0 'Returnerar två dimensionell array med alla poster i rs ReadDataFromWorkbook = rs.GetRows 'Stäng postuppsättningen och databasanslutningen rs.Close dbConnection.Close Set rs = Nothing Set dbConnection = Nothing Exit Function' Kod för hanteringsfel InvalidInput: MsgBox "Källfilen eller källområdet är ogiltig! ", _ vbExclamation," Hämta data från stängd arbetsbok "Slutfunktion 'Lägg till nedan kod i UserForm1 Alternativ Explicit Private Sub CommandButton1_Click () Dim name1 As String Dim i As Integer' Tilldela det valda värdet till variabelnamn1 För i = 0 To ListBox1.ListCount - 1 If ListBox1.Selected (i) Then name1 = ListBox1.Value Exit For End If Next 'Unload the userform Unload Me' Visa det valda namnet MsgBox "Du har valt" & namn1 & "." End Sub Private Sub UserForm_Initialize () Dim ListItems As Variant, i As Integer Dim SourceWB As Workbook 'Stäng av skärmuppdateringar från Application.ScreenUpdating = False With Me.ListBox1' Ta bort befintliga poster från listboxen. Rensa 'Öppna källarbetsboken som ReadOnly Set SourceWB = Workbooks.Open ("D: \ Excelforum \ ExcelForum office \ excel tip old code \ Shared Macro \ 23 \ 23SampleData.xls", _ False, True) 'Hämta det värdeintervall du vill ha ListItems = SourceWB.Worksheets (1 ) .Range ("A2: A10"). Värde 'Stäng källarbetsboken utan att spara ändringar SourceWB.Close False Set SourceWB = Ingenting Application.ScreenUpdating = True' Konvertera värden till en vertikal array ListItems = Application.WorksheetFunction.Transpose (ListItems) För i = 1 Till UBound (ListItems) 'Populera listboxen .AddItem ListItems (i) Nästa i' Välj inga objekt som standard, ställ in på 0 för att välja det första objektet. ListIndex = -1 Avsluta med slutet Sub
Om du gillade den här bloggen, dela den med dina vänner på Facebook. Du kan också följa oss på Twitter och Facebook.
Vi vill gärna höra från dig, låt oss veta hur vi kan förbättra vårt arbete och göra det bättre för dig. Skriv till oss på e -postwebbplatsen