Database in Excel - Gestione dati - Una Videoteca. - dal 04/09/04 pagina vista: volte

Ancora un'altro esempio di un progetto di inserimento, cancellazione, consultazione, ricerca dati in un database. Questo esercizio vuole focalizzare l'attenzione sull'utilizzo degli indici riga (o colonna) per la gestione dei dati in una tabella database.

Qualsiasi operazione, tra quelle sopra, necessarie alla gestione di insiemi di dati, cioè tabelle dati, altrimenti detti database, ha la necessità di lavorare con indici riga o indici colonna.

E infatti attraverso il reperimento di questi indici che possiamo identificare righe o colonne contenenti dati esistenti in una tabella, oppure inserirne di nuovi, cercando l'indice corrispondente ad una riga vuota.

Ovviamente un database che si rispetti sarà formato da tante colonne quanti sono i campi necessari al trattamento dei nostri dati, e che attraverso le intestazioni di campo (o colonna) formeranno lo schema di contenimento dati a noi necessario.

Diamo quindi per scontato che conosceremo il numero di colonne (o campi) che formeranno la tabella, come pure sapremo la riga iniziale della tabella ( e quindi il suo indice riga) , mentre non possiamo sapere quanti dati, e quindi quante righe saranno occupate.

Esistono diversi modi per reperire un indice riga ma fondamentalmente si basano su due necessità:

  • trovare l'indice riga della prima riga vuota dove inserire nuovi dati.

  • trovare l'indice riga corrispondente ad un criterio che avremo cercato, per modificare e/o cancellare il dato trovato o dati correlati al criterio.

Trovare l'indice riga della prima riga vuota - tra i vari modi utilizzabili ne suggerisco tre:

  • con la Funzione End - consente, data una cella iniziale, di trovare l'ultima cella occupata da dati, nella stessa colonna. Ricordo che per ottenere l'ultima cella occupata, le celle dell'intervallo contenente dati non dovranno avere celle vuote tra una cella occupata e la successiva occupata, riempirle magari con un trattino ( - ) oppure un punto ( . ), altrimenti End identificherebbe la prima cella vuota fermandosi a quella, anche se sotto esistono ancora dati. Per ottenere l'indice riga useremo la proprietà Row che restituisce il numero di riga corrispondente alla cella, assegnandolo alla variabile Ri; poichè vogliamo il numero della riga vuota che sarà sotto l'ultima cella occupata, aggiungeremo 1. Esempio :

  • Ri = ActiveSheet.Cells(1, 1).End(xlDown).Row + 1

  • con la proprietà UsedRange - che restituisce tutta l'area contenente dati, e trattandosi di un foglio destinato alla tabella, sicuramente comprenderà tutte le righe attualmente occupate. Conteremo le righe dell'area usando la proprietà Rows e la sua proprietà Count, aggiungendo 1 per avere la prima riga libera. Esempio:

  • Ri = ActiveSheet.UsedRange.Rows.Count + 1

  • con un ciclo While...Wend - che ricerca la prima riga libera in una colonna, fornendo la riga iniziale da cui iniziare il ciclo, e della cella così trovata, ne leggiamo il numero con la proprietà Row.  Esempio :

  • riga = 1
    While Cells(riga, 1) <> ""
    riga = riga + 1
    Wend
    Ri = Cells(riga, 1).Row

In tutti e tre i casi la variabile Ri corrisponderà alla prima riga libera. Se rendiamo Pubblica questa variabile, la potremo sfruttare in istruzioni successive per indicare su quale riga intervenire. Dovremo usare la zona del modulo standard (che conterrà la/le nostre macro) Generale - Dichiarazioni, definendo anche il "Tipo di variabile", così:

  • Public Ri As Integer

Nel caso si abbia a che fare con database estesi, che superino le 32.767 righe, sarà opportuno dichiarare la variabile di "tipo" Long .

Trovare l'indice riga corrispondente ad un criterio - questa operazione è normalmente affidata a cicli For Next o For Each Next che si occupino di rintracciare il valore corrispondente ad un criterio di ricerca in una determinata area o intervallo di celle; una volta trovata la cella che risponde al requisito, sarà sufficiente usare la proprietà Row per reperire il suo indice di riga, assegnando ad una variabile (Ri ad esempio) il numero reperito con Row. Useremo poi la sintassi Cells(Ri, numero colonna) per puntare ad una cella correlata al criterio trovato.

Ma lo stesso concetto del reperimento indice riga ci può servire per definire gli estremi di un'intervallo, anzi per meglio dire, il numero che serve a definire il numero di riga del riferimento che forma la fine intervallo, come ad esempio l'assegnazione di un'intervallo ad un RowSource di una ComboBox o ListBox, dove la sintassi prevede di dichiarare (in vba) l'intervallo come Stringa, messo tra doppi apici, dove 100 rappresenta la centesima riga della colonna A in A1:A100:

  • ComboBox1.RowSource = "A1:A100"

bene, potremo rendere variabile il numero 100 (dell'ultima riga intervallo) sostituendo l'istruzione sopra con:

  • Ri = Sheets(1).[A1].End(xlDown).Row
    ComboBox1.RowSource = "A1:A" & Ri & ""

Prima di illustrare brevemente il progetto, desidero sottolineare anche il sistema usato per riempire le TextBox sulla UserForm con i dati provenienti dalle celle sul foglio: un ciclo For Next che lavorando sugli indici degli insiemi, provvede velocemente a caricare le TextBox; infatti abbiamo 8 campi (colonne) e 8 Textbox; sappiamo che le textbox vengono identificate dal loro numero indice (Textbox1, Textbox2, ecc.) e le celle del foglio dal loro indiceriga e indice colonna: rendendo variabile l'indice ( I ) con il ciclo, otteniamo che a textbox( I ) corrisponderà cella riga2, colonna( I ):

  • For I = 1 To 8
    UserForm1.Controls("TextBox" & I).Text = Sheets(1).Cells(2, I).Value
    Next

sono comunque concetti già espressi in altri articoli sul sito.

Ho detto "illustrare brevemente" in quanto non commento su questa pagina le routine usate (è possibile scaricare il file e leggere il codice), ma mi limito a descrivere le funzionalità. Sotto un immagine della UserForm1 che serve come maschera di Consultazione e di Ricerca:

La UserForm all'avvio carica il titolo del Film e dei dati correlati posti ad inizio tabella, dopo le intestazioni di campo (riga 2); nella parte alta (azzurra) sono presenti 4 OptionButton per l'ordinamento dati: selezionando un criterio di ordinamento potremo scorrere tutti i record agendo sullo ScrollBar (rosso); sempre in questa zona è presente la TextBox9 (quindi successiva alle 8 previste dal ciclo sopra esposto) dove digitando un titolo, anche parziale, tramite il pulsante "Cerca" rintraccerà tutte le celle del campo "Titolo" che contengono la parola digitata.

E' presente un pulsante (rosso) per l'eliminazione di una riga (quindi di un Film) corrispondente all'indice riga del film al momento presente nelle TextBox, ed un pulsante "Modifica Record" nel caso si sia variato un dato tra quelli presenti.

Il pulsante "Inserisci Nuovo" chiude la UserForm1 ed attiva la UserForm2 che è la maschera preposta per nuovi inserimenti (la vediamo poi sotto).

Un buon progetto deve prevedere più possibilità di ricerca. In questo progetto la cosa più importante è rappresentata dal reperimento di tutti i titoli appartenenti ad uno specifico criterio, ad esempio tutti i titoli relativi ad un certo attore, o ad una certa attrice, o regista o genere, oltre alla posizione che il film avrà sullo scaffale per rintracciarlo rapidamente. Per questa necessità ho previsto, nella zona bassa (arancio/giallo) quattro Combobox ognuna collegata tramite il proprio RowSource, ai relativi intervalli dei rispettivi campi. Selezionando uno dei criteri scelti, facciamo caricare nella ListBox (gialla) tutti i dati correlati al criterio scelto; questo offre una panoramica completa di tutto ciò che il database contiene relativo al criterio selezionato; esempio sotto: selezionando il nome di un attore (Al Pacino)

otterremo (con l'evento Click della ComboBox) il caricamento dei film in cui è presente, nel campo "Attore" il nome selezionato:

E' evidente che in funzione del criterio scelto (genere, attore, attrice, regista) otterremo i relativi dati. Da notare che in funzione del criterio scelto, i dati riportati nella ListBox1 escludono il campo del criterio scelto, ma solamente i campi correlati (come è giusto che sia) . Le Label descrittive di campo sopra la ListBox1 sono quindi aggiornate in funzione del criterio scelto. Trovate tutte le istruzioni di modifica Caption nell'evento Click di ogni ComboBox.

Eventuali errori di dati nei campi sono da imputarsi solo ad errato inserimento da parte dell'utente (ad esempio nome di attore nel campo attrice o viceversa). Unico difetto riscontrabile sarà che se cerchiamo i film di un attore il cui nome è finito nel campo "Attrice", quei titoli non figureranno nella ListBox, visto che la ricerca viene eseguita solo sul campo del criterio scelto. Il progetto vba è stato realizzato su una tabella con dati già inseriti; Con l'utilizzo della seconda maschera per l'inserimento di nuovi dati, non ci saranno errori, se non voluti.

Per questo ho previsto il pulsantino giallo "Inv" (inverti) che ha il compito di invertire tra loro i dati nelle TextBox "Attore/Attrice", salvo poi confermare premendo il pulsante "Modifica Record".

Il pulsante "Cancella Record" serve ad eliminare l'intera riga selezionata al momento.


 

La cartella si compone di due fogli : il foglio 1 dove risiede il database dei titoli, e il foglio2 dove risiede il database dei campi Genere, Attore, Attrice, Regista. Questa è l'area che serve a rifornire i RowSource delle quattro ComboBox sulla seconda UserForm, quella per l'inserimento di nuovi dati. Queste ComboBox servono solo per la ricerca ed eventuale inserimento nelle textbox di pertinenza, dei nomi già presenti nei quattro campi citati prima: ci evita di scrivere o riscrivere un nome già presente; questa l'immagine della UserForm2:

Le ComboBox sono posizionate sopra le rispettive TextBox, e selezionando un nome, vedremo questo:

Nella TextBox apparirà il nome selezionato nella soprastante ComboBox. Chiaramente se sarà un nuovo nome lo scriveremo manualmente nella TextBox di pertinenza.

Tutte le istruzioni sono state inserite nell'evento Click del pulsante "Registra", e sono presenti queste istruzioni:

  • Reperimento del valore MAX nella colonna A del Foglio1 - questo ci consente di  avere il nuovo ID che verrà inserito nella TextBox1, tramite la funzione WorksheetFunction.Max

  • m = Sheets(1).Range(Cells(2, 1), Cells(2, 1).End(xlDown))
    TextBox1 = WorksheetFunction.Max(m) + 1

  • Un controllo che almeno il titolo sia stato scritto. (TextBox2).

  • Un ciclo While...Wend per reperire la prima riga libera dove copiare i dati che avremo inserito nelle TextBox.

  • Richiamo a quattro macro ognuna delle quali ha il compito di cercare sul Foglio2, nel campo di pertinenza, se il nome che stiamo inserendo è già presente; se sarà assente, aggiunge il nuovo nome in fondo all'elenco del rispettivo campo. Questo ci consente di aggiornare automaticamente il database nomi (genere, attore, attrice, regista) sul Foglio2 .

  • Richiamo della macro Ordinamento, che dopo l'eventuale aggiunta dei nomi nel database sul foglio 2, ne mette in ordine alfabetico i singoli campi: un ordinamento indipendente per ogni campo.

  • Infine un doppio ciclo For Next che lavorando sugli insiemi provvede a pulire i nomi selezionati nelle ComboBox e nelle Textbox per un eventuale nuovo inserimento.

Entrambe le UserForm hanno la proprietà ShowModal = False per poter consentire spostamenti sul o sui fogli. Per chi possiede la versione 97 di Excel tenga presente che questa impostazione non consente l'utilizzo del programma e genera un errore.

Chi vorrà usare il programma, dovrà personalizzarlo sui titoli in suo possesso e potrà farlo seguendo due procedure: modificando i dati attraverso il pulsante "Modifica Record" sulla UserForm1 (metodo consigliato), oppure cancellando i dati esistenti tranne le prime due righe (che andranno modificate col pulsante Modifica Record) per evitare che routine che usano End si precipitino a fine foglio non trovando le prime due righe iniziali dell'intervallo vuote.

Chi ha seguito gli articoli non troverà problemi nel capire le istruzioni impiegate, e non commentate, presenti nel codice, e comunque questo lavoro può essere un esempio interessante per capire ed impostare nuovi lavori.

File scaricabile e consultabile :   Video2000.zip   90  Kb

  

Buon lavoro.

prelevato sul sito www.ennius.altervista.org