Riempire un controllo ListBox con i valori da lavoro chiuso utilizzando VBA in Microsoft Excel
In questo articolo, recupereremo i dati dalla cartella di lavoro chiusa alla casella di riepilogo nel modulo utente utilizzando VBA.
I dati grezzi per questo esempio sono nell’intervallo A2: B10 sulla cartella di lavoro “23SampleData.xls”, che si trova nel percorso file “D: \ Excelforum \ ExcelForum office \ excel tip old code \ Shared Macro \ 23 \”.
Abbiamo creato due pulsanti di comando nel foglio di lavoro principale per eseguire due diversi moduli utente. Ogni pulsante di comando è collegato a diversi moduli utente.
Spiegazione logica
In questo esempio, vengono utilizzati due modi diversi per recuperare i dati dalla cartella di lavoro chiusa. Questi sono:- . Apri la cartella di lavoro chiusa e ottieni i dati. Utilizzo della connessione ADODB
Apri la cartella di lavoro chiusa e ottieni i dati
È possibile impostare la proprietà RowSource di un controllo ListBox per ottenere dati da un’altra cartella di lavoro assegnando un valore alla proprietà RowSource come segue:
“[Nomefile.xls] Foglio1?! $ B $ 1: $ B $ 15 Il controllo ListBox visualizzerà i valori solo se l’altra cartella di lavoro è aperta.
Quindi, per recuperare i dati dalla cartella di lavoro chiusa, creeremo una macro per aprire l’altra cartella di lavoro senza che l’utente se ne accorga e recuperi i dati dalla cartella di lavoro per aggiungere elementi nella casella di riepilogo e chiudere la cartella di lavoro.
Facendo clic sul pulsante “Seleziona” si attiverà il modulo utente “UserForm1”.
L’evento Initialize del modulo utente viene utilizzato per aggiungere elementi nella casella di riepilogo. Questo evento apre innanzitutto la cartella di lavoro chiusa e quindi assegna il valore nell’intervallo alla variante “ListItems”. Dopo aver assegnato il valore, la cartella di lavoro viene chiusa e gli elementi vengono aggiunti alla casella di riepilogo.
La casella di riepilogo viene utilizzata per selezionare il nome dai valori di elenco esistenti. Premendo il pulsante “OK” verrà visualizzato il nome selezionato.
Uso della connessione ADODB
ActiveX Data Objects (ADO) è un’interfaccia di alto livello e facile da usare per la connessione OLE DB. È un’interfaccia di programmazione per accedere e manipolare i dati in un database.
Per creare una connessione ADODB, sarà necessario aggiungere la libreria ADO al progetto.
Per aggiungere un riferimento, scegli dal menu Strumenti> Riferimento.
Facendo clic sul pulsante “Connessione ADODB” sul foglio di lavoro si attiverà il modulo utente “UFADODB”. Nell’evento di inizializzazione di questo modulo utente, abbiamo utilizzato la connessione ADODB per recuperare i dati dalla cartella di lavoro chiusa. Abbiamo creato una funzione definita dall’utente (UDF) personalizzata “ReadDataFromWorkbook” per stabilire la connessione e recuperare i dati dalla cartella di lavoro chiusa all’array.
Abbiamo utilizzato un altro UDF “FillListBox” per aggiungere elementi nella casella di riepilogo durante l’inizializzazione del modulo utente. La casella di riepilogo visualizzerà i dati in due colonne, una colonna contiene il nome e la seconda colonna contiene l’età.
Premendo il pulsante “OK” dopo aver selezionato l’elemento nella casella Elenco, verrà visualizzato il messaggio di informazioni sull’elemento selezionato.
Segui sotto per il codice
Option Explicit
Sub running()
UserForm1.Show
End Sub
Sub ADODBrunning()
UFADODB.Show
End Sub
'Add below code in UFADODB userform
Option Explicit
Private Sub CommandButton1_Click()
Dim name1 As String
Dim age1 As Integer
Dim i As Integer
'Assign the selected value in list box to variable name1 and age1
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
'Unload the userform
Unload Me
'Displaying output
MsgBox "You have selected " & name1 & ". His age is " & age1 & " yrs."
End Sub
Private Sub UserForm_Initialize()
'Filling ListBox1 with data from a closed workbook
Dim tArray As Variant
'Calling function ReadDataFromWorkbook for getting data from specified range to array
'Change path according to your requirement, "Sample_data" is named defined range
tArray = ReadDataFromWorkbook("D:\Excelforum\ExcelForum office\excel tip old code\Shared Macro\23\23SampleData.xls", "Sample_Data")
'Calling function FillListBox for adding items in List Box
'Assign List box object and tarray as parameter
FillListBox Me.ListBox1, tArray
'Releasing array variables and deallocate the memory used for their elements.
Erase 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
'Assigning value to listbox
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
'Selecting no item in the List box by default
.ListIndex = -1
End With
End Sub
Private Function ReadDataFromWorkbook(SourceFile As String, _
SourceRange As String) As Variant
' requires a reference to the Microsoft ActiveX Data Objects library
' (menu Tools > References in the VBE)
Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset
Dim dbConnectionString As String
'Declaring a connection string and the driver require for establishing connection
dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};ReadOnly=1;DBQ=" & SourceFile
'Creating a new ADODB connection
Set dbConnection = New ADODB.Connection
On Error GoTo InvalidInput
'Open the database connection
dbConnection.Open dbConnectionString
'Getting the recordset from defined named range
Set rs = dbConnection.Execute("[" & SourceRange & "]")
On Error GoTo 0
'Returns a two dimensional array with all records in rs
ReadDataFromWorkbook = rs.GetRows
'Close the recordset and database connection
rs.Close
dbConnection.Close
Set rs = Nothing
Set dbConnection = Nothing
Exit Function
'Code for handling error
InvalidInput:
MsgBox "The source file or source range is invalid!", _
vbExclamation, "Get data from closed workbook"
End Function
'Add below code in UserForm1
Option Explicit
Private Sub CommandButton1_Click()
Dim name1 As String
Dim i As Integer
'Assign the selected value to variable name1
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
name1 = ListBox1.Value
Exit For
End If
Next
'Unload the userform
Unload Me
'Display the selected name
MsgBox "You have selected " & name1 & "."
End Sub
Private Sub UserForm_Initialize()
Dim ListItems As Variant, i As Integer
Dim SourceWB As Workbook
'Turning screen updates off
Application.ScreenUpdating = False
With Me.ListBox1
'Remove existing entries from the listbox
.Clear
'Open the source workbook as ReadOnly
Set SourceWB = Workbooks.Open("D:\Excelforum\ExcelForum office\excel tip old code\Shared Macro\23\23SampleData.xls", _
False, True)
'Get the range of values you want
ListItems = SourceWB.Worksheets(1).Range("A2:A10").Value
'Close the source workbook without saving changes
SourceWB.Close False
Set SourceWB = Nothing
Application.ScreenUpdating = True
'Convert values to a vertical array
ListItems = Application.WorksheetFunction.Transpose(ListItems)
For i = 1 To UBound(ListItems)
'Populate the listbox
.AddItem ListItems(i)
Next i
'Selecting no items by default, set to 0 to select the first item
.ListIndex = -1
End With
End Sub
Se ti è piaciuto questo blog, condividilo con i tuoi amici su Facebook e Facebook.
Ci piacerebbe sentire la tua opinione, facci sapere come possiamo migliorare il nostro lavoro e renderlo migliore per te. Scrivici a [email protected]