Connettersi ad un database (mdb) ed estrarre dati importandoli su un foglio di Excel. - pagina vista: volte

Difficoltà : medio -alta

Sul sito ennius.interfree.it ho già pubblicato una serie di articoli che trattano l'argomento "Connettersi ad un database", e li trovate riuniti nell'articolo "Database esterni (mdb)". L'utilità di questi esercizi è evidente: connettersi ad un database per "importare" (dati presenti sul database) in fogli di lavoro di Excel.

Comunque prima di iniziare, è opportuno ricordare che per poter lavorare con Connessioni DAO, è necessario, dal menù "Strumenti/Riferimenti/Riferimenti disponibili" (dal Visual Basic Editor) mettere un segno di spunta alla voce "Microsoft DAO 3.6 Object Library" (o altra versione disponibile sul proprio computer), altrimenti non si attiverà la connessione al database.

L'importazione di dati è uno "sport" che svolgono in molti, e in questo articolo non ripeterò cose che "pellegrini sportivi" già conoscono, o sono contenute in spiegazioni contenute nell'articolo succitato, ma vorrei mostrare come rendere "variabile" l'importazione di dati: credo che le procedure e le spiegazioni che presento, potranno aiutare a diversificare una "connessione" rendendo veramente versatile l'estrazione e l'utilizzo di dati contenuti in un database.

Al di là delle procedure da seguire e delle istruzioni da compilare per ottenere una connessione, l'importazione dei dati è poi  affidata ad una Query di selezione, che altro non è che un'istruzione scritta in linguaggio SQL, con la quale decidiamo "cosa importare" e da quale "tabella" contenuta nel database al quale ci siamo connessi.

Sappiamo che in un database di tipo Access (.mdb), i dati sono contenuti in una o più tabelle (e/o Query residenti), e che questi dati all'interno di ogni tabella, sono organizzati in "campi" (colonne) e "records" (righe): una "Query di selezione" in genere viene inserita direttamente nelle istruzioni per l'importazione di dati, rendendo di fatto ripetitiva la Query, la quale eventualmente importerà nuovi dati inseriti nel database di origine, ma sempre quello; ciò che faremo sarà invece:

• usare istruzioni per "leggere" tutte le tabelle presenti in un determinato database.

• selezionare (ogn)una di queste tabelle, e "leggere" tutti i campi contenuti nella tabella.

Sarà quindi possibile decidere da quale tabella importare i dati, e quali campi di questa tabella scegliere per importare i dati, questo ci consentirà di modificare la Query di selezione, importando di volta in volta dati diversi, provenienti da tabelle e campi diversi.

Per questo esercizio ho usato il database northwind.mdb, in genere installato, come esempio di database, sui computer al momento dell'installazione di Access, ma potrete tranquillamente usare un qualsiasi vostro database mdb; sarà poi necessario indicare nelle istruzioni di connessione, il percorso completo che mira al file; io per questa occasione l'ho posto in : C:\Temp\northwind.mdb, ed ho poi usato una cella del foglio di lavoro dove ho scritto il percorso completo. Useremo questa cella come riferimento al database nelle istruzioni di connessione.

Ho predisposto 3 ListBox su una UserForm che chiameremo da un pulsante posto sul foglio; all'apertura della UserForm (evento Activate), facciamo caricare nella ListBox 1 col metodo AddItem, tutti i nomi delle tabelle contenute nel database; sfruttando poi l'evento ListBox1_Click, faremo caricare nella ListBox 2 tutti i nomi dei campi contenuti nella tabella che avremo selezionato nella ListBox1. Avremo a questo punto sott'occhio lo schema della tabella scelta, e a questo punto potremo scegliere due strade:

• inviare tutti i campi contenuti nella tabella (e ora visti nella ListBox2) ad altrettante celle del foglio, allineando i campi su una stessa riga, in modo da formare le intestazioni di colonna della nuova tabella che formeremo sul foglio tramite la Query. Per questo useremo il pulsante "Predisponi tutti i campi ^", questo nel caso che si voglia importare tutti i dati contenuti in tutti i campi della tabella scelta.

• oppure, selezionando i campi che ci interessano nella ListBox2, inviarli ad ogni click nella ListBox3. Questo ci consente di definire solo i campi che ci interessa estrarre e di cui dovremo tener conto per comporre la Query; dovremo comunque trasferire sul foglio i campi scelti per creare le intestazioni di colonna, e per questo useremo il pulsante "Predisponi campi sul foglio".

Vediamo un'immagine della UserForm, con i tre passaggi: abbiamo scelto la tabella Clienti, nella ListBox2 vediamo i campi relativi alla tabella selezionata, ed abbiamo infine scelto solo i campi che vediamo nella ListBox3, selezionandoli nella ListBox2. Ora dovremo premere il pulsante "Predisponi Campi sul Foglio" prima di usare il pulante "Componi la Query":

Passiamo ora ad esaminare la prima parte delle routines, quelle relative al caricamento delle ListBox. Parleremo poi di come comporre le Query, argomento che riveste particolare attenzione nel contesto di questo esercizio.

Istruzioni per caricare le tabelle del database all'apertura della UserForm:

• Private Sub UserForm_Activate()
  Sheets(1).Range("A7:Z7").ClearContents  'faccio pulire la riga 7, da A7 a Z7 che corrisponde all'intervallo sul foglio 'di lavoro che conterrà le intestazioni di colonna (uso un range sufficiente a pulire fino a 26 campi).
  Dim Db As Database   'dichiarazioni di variabili
  Dim Td As TableDef
  Dim NomeDB As String
  NomeDB = Sheets(1).Range("B2") 'NomeDB variabile con la quale prendiamo il percorso che mira al database, 'contenuto nella cella B2.
  
  Set Db = DBEngine.OpenDatabase(NomeDB) 'con Db impostiamo l'apertura in DAO della connessione (tramite 'OpenDatabase del DBEngine) al database ora rappresentato da NomeDB.
  
  For Each Td In Db.TableDefs  'iniziamo il ciclo che scorre ogni Tabella (Td) contenuta nell'insieme Tabelle '(TableDefs) del database (Db).
  'sotto: impostiamo un controllo If che ha il compito di caricare solo le Tabelle contenenti dati e non le tabelle che ogni 'database mdb contiene e che normalmente noi non vediamo, contenenti attributi o i dati di sistema del database.
  If (Td.Attributes And dbSystemObject) = 0 Then 
  ListBox1.AddItem Td.Name  'aggiungiamo quindi il nome della tabella nella ListBox1
  End If
  Next Td
  
  Db.Close 'poi chiudiamo la connessione al database
  Set Td = Nothing   'ed svuotiamo la memoria da queste variabili
  Set Db = Nothing
  End Sub

Tanto per capire cosa si intende per Td.Attributes e dbSystemObject, se si eliminasse la condizione impostata, ci troveremmo anche con tutte le tabelle bordate in rosso nell'immagine sottostante, tabelle che potrebbero generare confusione e che all'utente sicuramente non servono, contenendo solo dati di gestione del database da parte di Access:

continuiamo con le routine prima parte:

Istruzioni per visualizzare nella ListBox2 i "Campi" contenuti nella tabella che selezioneremo nella ListBox1:

• Private Sub ListBox1_Click()
  m = ListBox2.ListCount - 1    'dobbiamo, ad ogni selezione nella ListBox1, pulire la lista della ListBox2
  If m >= 1 Then
  For Z = m To 0 Step -1
  ListBox2.RemoveItem Z  
  Next
  End If    '---fine istruzioni per la pulizia della ListBox2
  Dim NomeTb As String     'dichiarazione della variabile NomeTb (nome tabella) come stringa
  NomeTb = ListBox1.Text  'con la variabile NomeTb prendiamo il nome tabella in questo momento cliccata '(selezionata).
  'sotto: ripetiamo le istruzioni per la connessione in DAO al database
  Dim Db As Database     'variabile per definire Db come database
  Dim Td As TableDef      'variabile per definire Td come tabella (TableDef)
  Dim NomeDB As String    'variabile per definire NomeDB come nome del database
  NomeDB = Sheets(1).Range("B2")  'prendiamo con NomeDB il testo contenuto nella cella B2, Foglio1
  
  Set Db = DBEngine.OpenDatabase(NomeDB) 'ci riconnettiamo al database e...
  
  X = Db.TableDefs(NomeTb).Fields.Count 'con la variabile X contiamo quanti campi (Fields) sono presenti nella
  'tabella "NomeTb" ora selezionata, poi iniziamo un ciclo For Next che leggendo i nomi dei campi, li aggiunga alla 'ListBox2. Ricordo che gli indici in una ListBox iniziano da zero, e scorrendo gli indici per leggere il nome, dobbiamo 'fermarci al valore X meno 1
  For n = 0 To X - 1
  ListBox2.AddItem Db.TableDefs(NomeTb).Fields(n).Name 'aggiungiamo il campo alla listbox2
  Next
  Db.Close  'quindi chiudiamo il database aperto e scarichiamo la memoria con Nothing (sotto)
  Set Td = Nothing
  Set Db = Nothing
  
  End Sub

Una volta che avremo il nome dei campi nella ListBox2, decideremo cosa vogliamo "estrarre", se tutti i campi, o solo alcuni; in questa seconda ipotesi useremo ancora l'evento Click ma della ListBox2, per trasferire il nome dei campi scelti nella ListBox3, vediamo quindi le istruzioni che sono molto semplici:

Istruzioni per visualizzare nella ListBox3 i "Campi" che selezioneremo nella ListBox2:

• Private Sub ListBox2_Click()
  ListBox3.AddItem ListBox2.Text 'aggiungiamo alla ListBox3 il nome su cui clicchiamo nella ListBox2
  End Sub

Queste tre routines hanno il preciso scopo di permetterci di trasferire su una riga che avremo scelto, il nome dei campi da cui vogliamo importare i dati, (io ho usato la riga 7 a partire dalla A7 e fino alla Z7) attraverso uno dei due pulsanti predisposti sulla UserForm. Vediamo le routines inserite nell'evento Click dei CommandButton:

Pulsante "Predisponi tutti i campi^". - questo pulsante va usato se vorremo estrarre tutti i campi, e dovremo aver selezionato prima una tabella nella ListBox1:

• Private Sub CommandButton3_Click()
  m = ListBox2.ListCount  'con la variabile "m" otteniamo il numero di quanti campi sono nella ListBox2
  If m = 0 Then  'controllo che impedisce di proseguire se la ListBox2 è vuota
  MsgBox "Prima seleziona la Tabella nella ListBox a sinistra"
  Exit Sub
  End If
  If m >= 1 Then   'se la variabile "m" è uguale o maggiore a 1, trasferiamo il nome dei campi a partire dalla A7
  For W = 1 To m
  Sheets(1).Cells(7, W) = ListBox2.List(W - 1)
  Next
  End If
  End Sub

Pulsante "Predisponi Campi sul Foglio".  - pulsante usato per trasferire i nomi dei campi presenti nella ListBox3, ottenuti per selezione da tutti i campi presenti nella ListBox2:

• Private Sub CommandButton2_Click()
  m = ListBox3.ListCount  'con la variabile "m" otteniamo il numero di quanti campi sono nella ListBox3
  If m = 0 Then  'controllo che impedisce di proseguire se la ListBox3 è vuota
  MsgBox "Prima seleziona il/i campo/i nella ListBox centrale"
  Exit Sub
  End If
  If m >= 1 Then  'se la variabile "m" è uguale o maggiore a 1, trasferiamo il nome dei campi a partire dalla A7
  For W = 1 To m
  Sheets(1).Cells(7, W) = ListBox3.List(W - 1)
  Next
  End If
  End Sub

Pulsante "Componi la Query"  -  pulsante per chiamare la InputBox dove comporre la Query. Eseguo un controllo per assicurarci di non aver dimenticato di premere uno o l'altro dei pulsanti visti sopra:

• Private Sub CommandButton1_Click()
  If Sheets(1).[A7] = "" Then  'controlliamo che almeno la cella A7 contenga il nome di un campo
  MsgBox "Seleziona prima i campi da estrarre"
  Exit Sub
  End If
  Miaquery = InputBox("Scrivi la Query da eseguire, ricorda di scrivere il nome dei campi se fai una Query parziale")
  If Miaquery = "" Then Exit Sub  'se non scriveremo niente si esce dalla routine
  MsgBox Miaquery   'questo messaggio solo se volete vedere il risultato di quanto scritto nella InputBox(ev.togliere)
  Unload UserForm1    'quindi chiudiamo anche la UserForm1
  End Sub

L'intervallo A7:Z7, è l'intervallo che serve alle istruzioni per l'estrazione dati, dove verranno letti il nome dei campi da cui estrarre i dati, in funzione della Query di selezione che predisporremo, e che dovrà agire sullo stesso numero di campi che scriveremo nella Query.

In genere, nelle istruzioni di connessione in DAO, per importare i dati sul foglio di lavoro, si usa un ciclo che scorra tutti i campi della tabella del database, e per ogni campo letto, dobbiamo indicare da quale cella del foglio di lavoro iniziare ad importare i dati, scalando di colonna, per tanti campi quanti ne stiamo importando; è in queste istruzioni che dobbiamo indicare che il riferimento alla cella(x, y) sarà uguale al (campo)Fields(numeroindice) della tabella, e ovviamente, se dovessimo importare 5 campi, dovremmo indicare 5 celle(x, y) indicando per ognuna il Fields(numeroindice) relativo. Useremo invece, per la connessione di importazione il metodo basato su ADO dove, anzichè indicare il numero indice del Field da importare, il suo nome: cioè il nome del campo.

In questo esercizio, dove i campi diventano variabili di volta in volta, non possiamo usare istruzioni preimpostate nel codice, infatti non sapremo a priori da quale tabella e quali campi vogliamo importare, quindi abbiamo bisogno di far leggere da un secondo ciclo, il nome del campo (che verrà letto nell'intervallo A7:Z7, e che otterremo da ciò che faremo con le ListBox nella UserForm)), e usare il nome del campo per identificare lo stesso campo nella tabella del database ed importarne i dati. Sembra complicato ma in realtà è semplice.

Le Query.

Il compito di far estrarre i dati da un database, è affidato ad una Query di selezione; possiamo dividere le Query di selezione in due gruppi:

• Query che importano tutti i dati di una tabella del database (quindi tutti i campi presenti) ad esempio:

          "Select * From Clienti"  -  Seleziona tutto (*) (quindi tutti i campi) dalla (From) tabella (sottintesa) Clienti

• Query che che definiscono i campi da importare e/o importano tutti i dati ma con restrizioni su uno o più campi ; queste sono le Query più versatili e ci consentono una gestione completa di ciò che vogliamo estrarre, esempi:

         "Select * From Clienti Where Città between 'C' And 'D' "  - Seleziona tutto (*) (tutti i campi) dalla tabella
         Clienti, ma solo i dati dove (Where) l'iniziale del nome della Città è compreso (between) tra la lettera C e la lettera D
         (quindi tutta la C).

         "Select IDCliente, NomeSocietà, Indirizzo, Città, CAP, Paese From Clienti"  -   Seleziona solo i campi
         indicati (IDCliente, NomeSocietà, Indirizzo, Città, CAP, Paese) dalla (From) tabella Clienti. (Quando si indica un
         solo campo o più campi, ma non tutti, icampi devono essere separati da una virgola).

         "Select IDCliente, NomeSocietà, Indirizzo, Città, CAP, Paese From Clienti where Paese = 'Italia' "
         Seleziona solo i campi indicati (IDCliente, NomeSocietà, Indirizzo, Città, CAP, Paese) dalla (From) tabella Clienti,
         dove (where) il campo Paese corrisponde al nome Italia.

Esistono molti modi di comporre le Query, compreso le Query di giunzione (Join) in cui è possibile estrarre dati da più tabelle contemporaneamente, ed altre, ma non è questa la sede per tenere un corso specifico sulle Query; ciò che credo vada invece puntualizzato (almeno nelle parole più frequenti) è la sintassi da usare nel comporre una Query, seguiamo uno specchietto:

due accorgimenti ancora; in queste Query, in particolare scrivendo la query in una InputBox come nell'esempio che faremo, la sintassi prevede che:

• i nomi o comunque il testo (stringhe) usati come termini di confronto, siano posti tra apici semplici, es:   = 'Ancona'

• i numeri usati come termini di confronto siano senza nessun tipo di apice, es:   > 10 (maggiore di 10)

• le date usate come termini di confronto siano poste tra cancelletti (#), es:  > #01/01/99# e la data andrà scritta rispettando la sintassi inglese: mese/giorno/anno anzichè giorno/mese/anno come in italiano.

Continuando invece le spiegazioni, vediamo come fare per ottenere la nostra Query. Esistono diversi modi, ad esempio scrivere la Query in una cella del foglio di lavoro, ed assegnare il riferimento alla cella ad una variabile (Miaquery) che useremo nelle istruzioni di collegamento; oppure predisporre in una zona del Foglio (o altro Foglio) una serie di query preimpostate da noi, il cui intervallo venga poi assegnato al RowSource di una ComboBox che avremo inserito sulla userform, dove, una volta scelta la tabella e i campi da estrarre, ci consenta di selezionare nella ComboBox stessa, la Query più appropriata.

Io nell'esercizio, tramite il pulsante "Componi la Query", richiamo una InputBox nella quale scriveremo la nostra Query. Saremo avvantaggiati dalla presenza dei campi scelti e visibili nella ListBox3, che ci ricorderanno i campi da scrivere nella query per comporla. Nella InputBox non avremo bisogno di iniziare e chiudere la Query con doppi apici in quanto assegneremo ciò che scriveremo ad una variabile stringa (vista quindi dal codice già tra doppi apici). Useremo dimensionare come Public questa variabile, in modo che sia vista in tutto il progetto; posizioneremo quindi questa variabile nella zona "Dichiarazioni  -  Generale" del modulo che useremo per scrivere le istruzioni di connessione; questo farà si che sia visibile in tutto il progetto (se avessimo usato la sezione Dichiarazioni - Generale della UserForm, la visibilità della variabile sarebbe stata limitata alla sola UserForm). Scriveremo quindi: Public Miaquery As String

Dopo che avremo scritto la Query e premuto il pulsante OK della InputBox, ci troveremo sul foglio pronti a premere il pulsante "Estrai Dati dal Dbase". Ho diviso in due azioni questo esercizio, anche se è possibile creare un'unica sequenza di istruzioni, azionabile con un solo pulsante; ritengo che così sia meglio "digerire" i diversi passaggi, tra quelli visti fin'ora, e quelli che seguono.

Per questa connessione al database, anzichè sfruttare l'apertura in DAO, basato sull' OpenDatabase del DBEngine, useremo l'apertura in ADO, basato sull'oggetto Connection, da cui dipende l'oggetto Recordset, che a sua volta è composto di Fields, in cui stanno l'insieme di oggetti Field. Queste poche premesse solo per evidenziare la diversità di connessione al database usate con le precedenti routine, e quella che vedremo ora. Potrete scaricare un file di esempio, ma suggerirei di stamparvi questa pagina, per "ricucire" con calma, le spiegazioni fornite.

Questa sarà la pagina del Foglio1, con i pulsanti per chiamare la UserForm e poi per connettersi ed importare dati. La riga 7 (verde) è la riga dove verranno copiati i nomi dei campi scelti:

e queste le istruzioni da inserire in un modulo standard:

nella sezione "Generale  - Dichiarazioni" scriveremo:

• Public Miaquery As String

e questa la routine di connessione:

• Sub Estrai()
  If Miaquery = "" Then  'se non avremo ancora composto la query, quindi sarà vuota, si avvisa e si esce
  MsgBox "Prima componi la Query usando ""Scegli Tabella"""
  Exit Sub
  End If
  Dim NomeDB As String  'dichiarazione variabili: con NomeDB prendiamo il percorso del database
  Dim Quante As Integer  'con Quante conteremo quante colonne sono occupate dal nome dei campi
  Dim Q As Integer  'Q sarà il contatore del ciclo interno For..Next
  Dim I As Integer   'I sarà il contatore dell'indice di riga
  
  NomeDB = Sheets(1).Range("B2")  'NomeDb è uguale a ciò che abbiamo scritto nella cella B2
  
  Dim Connessione 'con Connessione impostiamo la stringa che ci consente di connetterci tramite ADO
  Connessione = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & NomeDB & ";'"
  Dim Oggetto As Object, Recordset As Object
  Set Oggetto = CreateObject("ADODB.Connection")
  Oggetto.Open Connessione
  Set Recordset = CreateObject("ADODB.Recordset")
  
  Set Recordset = Oggetto.Execute(Miaquery) 'ora eseguiamo la Query che abbiamo scritto nella InputBox
  
  Range(Cells(8, 1), Cells(65536, 26)).ClearContents  'prima di importare i nuovi dati, puliamo tutto l'intervallo
  
  If Sheets(1).Cells(7, 2) <> "" Then 'ora controlliamo se la cella B7 contiene il nome di un campo, se lo contiene
  Quante = Sheets(1).Cells(7, 1).End(xlToRight).Column 'contiamo quante colonne sono occupate a partire da A7 e 'assegniamo il numero delle colonne alla variabile Quante, che ci serve poi come limite superiore nel ciclo For Next
  Else  'altrimenti (se la cella B7 è vuota, e avremo un solo campo scritto in A7)
  Quante = 1  'Quante sarà uguale a 1
  End If
  
  Do While Not Recordset.EOF  'ora iniziamo un ciclo While che leggendo i record contenuti nella tabella, partirà dal 'primo e continuerà fino alla fine dei record (EOF = End Of File)
  
  I = I + 1 'I e un contatore che si incrementerà di un valore ad ogni ciclo While, quindi all'inizio I (0) sarà uguale a '0+1=1
  
  For Q = 1 To Quante  'iniziamo il secondo ciclo For che iniziando da 1, girerà fino al numero di colonne occupate contate nella riga 7
  'sotto: creiamo l'abbinamento: la cella( riga I+7 (cioè 1+7) colonna Q (1), quindi cella A8) sarà uguale al dato 'contenuto nel campo il cui nome è ora nella cella riga 7, colonna Q (la 1, ad inizio ciclo, cioè cella A7)
  Cells(I + 7, Q) = Recordset(Sheets(1).Cells(7, Q).Value) 'scorriamo quindi le colonne e contemporaneamente il 'nome dei campi, con incremento di 1 fino alla fine di questo ciclo
  Next Q
  
  Recordset.MoveNext 'quindi passiamo al record successivo, e ripetiamo le istruzioni a partire da I = I +1
  Loop
  
  ' ora si chiudono gli oggetti e si scaricano dalla memoria
  Recordset.Close
  Set Recordset = Nothing
  Oggetto.Close
  Set Oggetto = Nothing
  End Sub

Ricordarsi di scrivere l'esatto percorso che mira al vostro database, comprensivo di path; io l'ho scritto nella cella B2, ma sarà possibile munire il progetto di routine per la ricerca dei file, con assegnazione alla variabile NomeDB del nome del database (da rendere in questo caso come variabile Pubblica), in modo da diversificare anche il database sul quale operare; ma per questo esercizio esistono diversi articoli sui due siti.

Buon lavoro.

File consultabile e scaricabile:

prelevato sul sito www.ennius.altervista.org