Obiettivo: interrogare e filtrare il contenuto di un foglio excel come se fosse una tabella di un database SQL
Prepariamo anzitutto un file Excel creando un foglio con dei dati come quelli di seguito:
Questo foglio lo chiamiamo DATI. Faccio notare che nella prima riga ho inserito le intestazioni della nostra tabella.
In un secondo foglio andiamo a creare un pulsante che richiamerà un form. L’idea sarebbe quella di creare un metodo di inserimento che guidi l’utente e gli permetta di filtrare una vasta base dati, eseguendo anche dei comandi di ricerca. Il risultato sarà il seguente:
Se proviamo a cercare qualcosa, per esempio “Mar” l’elenco dovrà essere filtrato e otterremo il risultato seguente:
Fatte queste premesse vediamo come realizzare l’interrogazione del foglio mediante VBA (sorvolo sull’elementare creazione del form).
Anzitutto creiamo una Sub che caricherà i valori nella combobox (cmbSeleziona) e nella listbox (lbSeleziona).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
Private Sub LoadSelect(Optional ByVal nome As String = "") Dim conn As ADODB.Connection Dim rs As ADODB.Recordset Dim strFileName As String Dim ConnectionString As String strFileName = ThisWorkbook.FullName ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _ & strFileName & ";Extended Properties=""Excel 12.0 xml;HDR=YES;IMEX=1;Readonly=True"";" Set conn = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.Recordset") conn.Open ConnectionString Dim query As String If nome <> "" Then query = "SELECT * FROM [DATI$A:C] WHERE Nome LIKE '%" & nome & "%'" Else query = "SELECT * FROM [DATI$A:C]" End If rs.Open query, conn cmbSeleziona.Clear lbSelezione.Clear pos = 0 If Not rs.EOF Then Do cmbSeleziona.AddItem rs(1) & " [" & rs(2) & "]", pos cmbSeleziona.List(pos, 1) = rs(0) lbSelezione.AddItem rs(1) & " [" & rs(2) & "]", pos lbSelezione.List(pos, 1) = rs(0) pos = pos + 1 rs.MoveNext Loop Until rs.EOF End If rs.Close conn.Close End Sub |
Faccio notare che in questo caso la query filtra solamente i valori in base al Nome, inoltre il parametro nome può essere passato in via opzionale, permettendo quindi anche di visualizzare tutti i campi se non c’è nessun nome da filtrare.
Ricordiamoci che per poter eseguire la connessione ADO abbiamo bisogno di importare l’opportuna libreria dai riferimenti.
A questo punto completiamo il nostro codice aggiungendo:
|
1 2 3 4 5 6 7 8 9 10 11 |
Private Sub txtCerca_Change() LoadSelect txtCerca.Text End Sub Private Sub UserForm_Initialize() LoadSelect End Sub |
In questo modo ad ogni modifica (change) di txtCerca, che è la textbox di ricerca in cima al form, verrà interrogato il nostro foglio per produrre un risultato.
Il medesimo metodo LoadSelect, questa volta senza argomenti, lo aggiungiamo anche all’avvio del form, per caricare il contenuto completo senza filtri.
Qui è possibile scaricare il file creato con Excel 2016.
è possibile sostituire su (query = “SELECT * FROM [DATI$A:C] WHERE Nome LIKE ‘%” & nome & “%'”) la variabile “Nome” scegliendo da una list di una ComboBox?
Ciao Maury,
Ammettiamo che tu abbia una variabile tipo
Dim colonna As Stringcolonna = "Nome"
Allora la query potrebbe essere:
query = "SELECT * FROM [DATI$A:C] WHERE " & colonna & " LIKE '%" & nome & "%'"Come vedi ho concatenato nella stringa la variabile contenente il titolo del campo.