In questo articolo, recupereremo i dati dalla cartella di lavoro chiusa alla casella di riepilogo nel form 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 l'esecuzione di 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 recuperare 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 di inizializzazione del form utente viene utilizzato per aggiungere elementi nella casella di riepilogo. Questo evento apre prima 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.
Utilizzo 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, dovremo 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'altra UDF "FillListBox" per aggiungere elementi nella casella di riepilogo durante l'inizializzazione del form 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 di riepilogo verrà visualizzato il messaggio informativo sull'elemento selezionato.
Si prega di seguire sotto per il codice
Option Explicit Sub running() UserForm1.Show End Sub ADODBrunning() UFADODB.Show End Sub 'Aggiungi sotto il codice nel form utente UFADODB Option Explicit Private Sub CommandButton1_Click() Dim name1 As String Dim age1 As Integer Dim i As Integer 'Assegna il selezionato valore nella casella di riepilogo alla variabile name1 e 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 ' Scarica il modulo utente Scaricami 'Visualizza l'output MsgBox "Hai selezionato " & nome1 & ". La sua età è " & età1 & " anni." End Sub Private Sub UserForm_Initialize() 'Riempimento ListBox1 con dati da una cartella di lavoro chiusa Dim tArray As Variant 'Richiamo della funzione ReadDataFromWorkbook per ottenere dati dall'intervallo specificato all'array 'Cambia percorso in base alle tue esigenze, "Sample_data" è denominato intervallo definito tArray = ReadDataFromWorkbook ("D:\Excelforum\ExcelForum office\excel tip old code\Shared Macro\23\23SampleData.xls", "Sample_Data") 'Richiamo della funzione FillListBox per l'aggiunta di elementi nella casella di riepilogo 'Assegna oggetto casella di riepilogo e tarray come parametro FillListBox Me .ListBox1, tArray 'Rilascia variabili array e dealloca la memoria usata per i loro elementi. Cancella 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 'Assegnazione valore alla 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 'Selezione di nessun elemento nella casella di riepilogo per impostazione predefinita .ListIndex = -1 End With End Sub Private Function ReadDataFromWorkbook(SourceFile As String, _ SourceRange As String) As Variant ' richiede un riferimento alla libreria Microsoft ActiveX Data Objects' (menu Strumenti > Riferimenti nel VBE ) Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset Dim dbConnectionString As String 'Dichiarare una stringa di connessione e il driver richiedono per stabilire la connessione dbConnectionString = "DRIVER={Driver Microsoft Excel (*.xls)};ReadOnly=1;DBQ =" & SourceFile 'Creazione di una nuova connessione ADODB Imposta dbConnection = New ADODB.Connection On Error GoTo InvalidInput 'Apri la connessione al database dbConnection.Open dbConnectionString 'Ottenendo il recordset dall'intervallo denominato definito Set rs = dbConnection.Execute("[" & SourceRange & "]") On Error GoTo 0 'Restituisce due array dimensionale con tutti i record in rs ReadDataFromWorkbook = rs.GetRows 'Chiudi il recordset e la connessione al database rs.Close dbConnection.Close Set rs = Nothing Set dbConnection = Nothing Exit Function 'Codice per la gestione dell'errore InvalidInput: MsgBox "Il file di origine o l'intervallo di origine non è valido!", _ vbExclamation, "Ottieni dati dalla cartella di lavoro chiusa" End Function 'Aggiungi sotto il codice in UserForm1 Option Explicit Private Sub CommandButton1_Click() Dim name1 As String Dim i As Integer 'Assegna il valore selezionato alla variabile name1 For i = 0 A ListBox1.ListCount - 1 If ListBox1.Selected(i) Then name1 = ListBox1.Value Exit For End If Next 'Scarica il form utente Unload Me' Visualizza il nome selezionato MsgBox "Hai selezionato " & name1 & "." End Sub Private Sub UserForm_Initialize() Dim ListItems As Variant, i As Integer Dim SourceWB As Workbook 'Disattiva gli aggiornamenti dello schermo Application.ScreenUpdating = False With Me.ListBox1 'Rimuovi le voci esistenti dalla casella di riepilogo. Clear 'Apri la cartella di lavoro di origine come ReadOnly Set SourceWB = Workbooks.Open("D:\Excelforum\ExcelForum office\excel tip old code\Shared Macro\23\23SampleData.xls", _ False, True) 'Ottieni l'intervallo di valori che desideri ListItems = SourceWB.Worksheets(1 ).Range("A2:A10").Value 'Chiudi la cartella di lavoro di origine senza salvare le modifiche SourceWB.Close False Set SourceWB = Nothing Application.ScreenUpdating = True 'Converti i valori in un array verticale ListItems = Application.WorksheetFunction.Transpose(ListItems) For i = 1 To UBound(ListItems) 'Popola la casella di riepilogo .AddItem ListItems(i) Next i 'Selezionando nessun elemento per impostazione predefinita, imposta a 0 per selezionare il primo elemento. ListIndex = -1 End With End Sub
Se ti è piaciuto questo blog, condividilo con i tuoi amici su Facebook. Inoltre, puoi seguirci su Twitter e Facebook.
Ci piacerebbe avere tue notizie, facci sapere come possiamo migliorare il nostro lavoro e renderlo migliore per te. Scrivici al sito di posta elettronica