Obiettivo: creare una classe per poter interrogare tramite query SQL un foglio excel, leggendo e scrivendo i dati come se lavorassimo con tabelle SQL
Questo esercizio riprende il punto direttamente da questo precedente: Interrogare foglio di Excel con SQL in VBA
Dopo aver visto come poter interrogare con query SQL un foglio excel, vediamo come creare una classe ad hoc che ci permetta di leggere e scrivere i dati mediante query SQL. Per farlo anzitutto aggiungiamo una nuova classe:
Premendo ALT+F11 apriamo l’editor Visual Basic, ci posizioniamo su un qualunque punto del progetto a sinistra e clicchiamo col destro (per esempio su Form), poi scegliamo Inserisci ⇒ Modulo classe. A questo punto verrà creata una nuova classe. Per rinominarla è sufficiente selezionarla e poi spostarsi nel riquadro delle proprietà.
A questo punto incolliamo dentro la classe appena creata (tenendo presente poi il nome scelto) il seguente codice:
|
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 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 |
' funzione in input per il database, restituisce un vettore variant di dimensione n x m, dove ' n è il numero di righe lette e m il numero di colonne per riga Public Function dbout(ByVal query As String) As Variant Dim conn As ADODB.Connection Dim rs As ADODB.Recordset Dim strFileName As String Dim ConnectionString As String Dim pos As Integer ' creiamo il vettore con il risultato Dim result() As Variant 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 rs.Open query, conn ' adesso dobbiamo anzitutto contare la dimensione del risultato righe = 0 colonne = 0 If Not rs.EOF Then Do righe = righe + 1 colonne = rs.Fields.Count rs.MoveNext Loop Until rs.EOF End If ' riposizioniamo il recordset alla partenza rs.MoveFirst ' ridimensioniamo il vettore risultato sulle righe e colonne appena contate ReDim result(righe, colonne) ' leggiamo tutti i dati ed inseriamoli nel vettore righe = 0 If Not rs.EOF Then Do For j = 0 To rs.Fields.Count - 1 result(righe, j) = rs(j) Next j righe = righe + 1 rs.MoveNext Loop Until rs.EOF End If rs.Close conn.Close ' restituiamo il vettore dbout = result End Function ' questo metodo accetta in input la query e la esegue sul foglio prescelto Public Function dbin(ByVal query 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;"";" Set conn = CreateObject("ADODB.Connection") conn.Open ConnectionString ' eseguiamo la query conn.Execute query conn.Close End Function |
Faccio notare come nel metodo dbout dobbiamo fare un’operazione un po’ superflua in apparenza, ovvero contare prima le righe e le colonne, per poi ridimensionare il vettore result. Questa operazione è necessaria perché ReDim, in VBA, non permette di ridimensionare l’intera matrice (vettore di vettori), ma solo l’ultima dimensione. Quindi, per esempio, result(4,10) potrebbe diventare result(4,20), ma non result(5,10) oppure result(5,20). Un’alternativa sarebbe quella di creare una funzione che trasponga il vettore e lo modifichi, ritrasponendolo di nuovo, ma dal punto di vista del calcolo credo sarebbe molto più oneroso che fare così.
Fatto questo potremo utilizzare la nostra nuova classe nella maniera seguente.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
' inserimento Dim query As String query = "INSERT INTO [DATI$] (ID,Nome,Città) Values (1,'Mario','Palermo')" Dim db As New DbSQL db.dbin query ' lettura query = "SELECT * FROM [DATI$A:C]" Dim db As New DbSQL Dim righe As Variant righe = db.dbout(query) For i = 0 To UBound(righe, 1) - 1 Debuig.Print righe(i, 1) ' 1 = prima colonna, 2 = seconda colonna, ecc. Next i |
Una volta creata la classe può essere esportata, oppure importata nel modo seguente:
Per chi volesse è possibile scaricare la classe già pronta cliccando qui: DbSQL.zip
Hash MD5: A2A18A42BB11AEA86DF1AB4F547B4414
La classe è stata testata su Excel 2016.