TheTruster's Box

Nel post precedente ho affrontato il problema dell'importazione di dati da un foglio di lavoro di Excel ad un Documento Word.
La stessa cosa avrebbe potuto essere realizzata utilizzando come destinazione, in luogo del documento Word, un altro Foglio di Lavoro Excel, un Database Access oppure un programma in Visual Basic 6.
Il sistema dell'automazione, però, ci pone nella situazione di dover avere installato sulla macchina l'applicazione del pacchetto Office che intendiamo utilizzare. Nel caso di Excel ad esempio, non potremmo fare ricorso all'apposita DLL se Excel stesso non fosse installato nel sistema.

Una soluzione alternativa, anche se non si tratta propriamente di automazione, è quella di trattare Excel come fosse un Database , nel qual caso avremmo bisogno solo del modello ad oggetti ADO e, se il sistema operativo è Windows XP, questo è installato insieme al sistema, per cui sicuramente disponibile.

Per il nostro esempio di importazione prenderemo a base un Database Access anche perfettamente vuoto senza alcuna tabella, visto che ci occuperemo di crearla dinamicamente al momento dell'importazione.
Avremo anche bisogno di un file di Excel per la nostra prova per cui, se non ne abbiamo uno già disponibile con dei dati intabellati in maniera coerente, creiamone uno, con questa impostazione:

Come si può notare, la cosa importante è utilizzare la prima riga come intestazione dei campi.

Passiamo alla creazione del nostro Database - o creiamone uno nuovo - e aggiungiamo un nuovo Modulo, premendo Moduli nella finestra del Database e Nuovo sulla barra superiore della stessa finestra.
Si aprirà la finestra dell'Editor di Visual Basic e la prima cosa che faremo, è referenziare la libreria che ci interessa, relativa ad ADO.
Lo possiamo fare da Strumenti -> Riferimenti.... Molto probabilmente, dipendentemente dalla versione di Access, si troverà già referenziata la

Microsoft ActiveX Data Object 2.1 Library

nel qual caso possiamo de-referenziarla andandone a scegliere una analoga, ma più aggiornata, ovvero la

Microsoft ActiveX Data Object 2.8 Library

oppure la più recente installata nel sistema, se questa non è presente.

Il modello ad oggetti ADO, benchè disponga di un numero relativamente basso di oggetti, risulta piuttosto articolato e sarebbe impossibile discuterne le potenzialità e le caratteristiche in poche righe.
In generale, comunque, anche ADO come gli oggetti della libreria di Excel si possono riassumere in una certa gerarchia, a capo della quale troviamo l'oggetto Connection.
Questo è l'oggetto principale poichè permette di "aprire una porta" sul Database permettendoci l'accesso ai dati.
Attraverso la Connection possiamo eseguire delle operazioni direttamente sul DB inviandogli delle frasi SQL, per cui ci consente di inserire dati e riceverne indietro, creare tabelle o cancellarne o ancora modificarne la struttura.
Se creare una tabella piuttosto che inserire dati o modificarli non presuppone per forza un ritorno degli stessi verso la nostra applicazione, la semplice lettura dei dati contenuti in una tabella per la loro visualizzazione, ci pone nella situazione di doverli "immagazzinare" da qualche parte. Ci serve un contenitore, insomma. Questo contenitore si chiama Recordset.
Come suggerisce la parola questo oggetto è un set di record ovvero una porzione dei dati contenuti genericamente nel nostro DB anche se distribuiti su più tabelle, selezionati secondo dei criteri.
Per selezionare dei record si usa una Query ovvero una frase SQL che il motore del DB interpreta per restituirci dei dati coerenti con le nostre condizioni.

Mi scuso per questa digressione, forse per qualcuno superflua, ma probabilmente necessaria per permettere a chi si avvicina per la prima volta a questa tecnologia di capire almeno il senso delle righe di codice che andrò a riportare in seguito.
In ogni caso consiglio, per una concezione più dettagliata dell'argomento, di dare una scorsa a questo sito (in inglese) dove, oltre alle spiegazioni riferite ai vari oggetti di ADO, sono presenti anche alcuni esempi di utilizzo.

Ritornando alla nostra importazione, prepariamo l'oggetto principale di cui ci serviremo per aprire il DB Excel ovvero una Connection.

Dim oConn As ADODB.Connection

Di solito, personalmente, preferisco avere un unico oggetto Connection, attraverso il quale aprire più Recordset. L'oggetto Connection viene dichiarato pubblico, aperto all'inizio dell'applicazione e chiuso alla chiusura della stessa oppure quando non risulta più necessario, mentre gli oggetti Recordset vengono aperti e chiusi, spesso nella stessa routine, quando esauriscono la loro utilità, sia essa di inserimento/modifica che di lettura.

In questo caso, possiamo accontentarci un solo oggetto Recordset che dichiareremo all'interno della routine di importazione.
A proposito, cominciamo a costruirla. Dal punto di vista logico la nostra routine dovrà:

1. Aprire la Connessione verso Excel
2. Aprire il Recordset
3. Immagazzinare i dati da importare nel Recordset
4. Creare la Tabella che conterrà i dati nel nostro DB
5. Copiare i dati sulla Tabella
6. Chiudere il Recordset
7. Chiudere la Connessione con Excel

Per aprire la Connessione verso una qualsiasi DB è necessaria la cosiddetta ConnectionString ovvero stringa di connessione. Essa ci permette di scegliere la modalità di apertura del DB, specificando il motore e il file del DB stesso, e passando al Database Username e Password nel caso in cui fossero necessari per accedervi.
Per Excel è necessario una particolare ConnectionString e un'ottima fonte per reperirne una adeguata alle nostre necessità è il sito ConnectionStrings.com.
Guardando tra quelle disponibili per aprire Excel come DB troveremo anche questa

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

Per semplicità possiamo anche realizzare una Routine riutilizzabile, appositamente creata per aprire la connessione, liberandoci dalla necessità dover inserire il codice necessario tutte le volte in diverse routines:

Sub ApriConnessione(NomeDB As String)
 
On Error GoTo Err_Handle
 
If oConn Is Nothing Then
    Set oConn = New ADODB.Connection
ElseIf oConn.State = adStateOpen Then
    oConn.Close
End If
 
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & NomeDB & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
 
Exit Sub
 
Err_Handle:
    MsgBox Err.Number & ", " & Err.Description
 
End Sub

Come si può notare la connessione verso il nostro file Excel si ridurrà ad una semplice riga del tipo:

ApriConnessione "C:\Cartella\FileExcel.xls"

Analogamente possiamo creare una seconda routine per chiudere la connessione eventualmente aperta:

Sub ChiudiConnessione()
 
If Not oConn Is Nothing Then
    If oConn.State = adStateOpen Then
        oConn.Close
    End If
End If
Set oConn = Nothing
 
End Sub

Abbiamo praticamente realizzato i punti 1 e 7 della lista precedente, quindi passiamo, finalmente, alla creazione della routine di importazione. Nella sua costruzione utilizzeremo, oltre alla Connessione verso Excel e al Recordset, la connessione che Access, ovvero la sua Applicazione, apre verso le sue tabelle. Questa Connessione è accessibile attraverso l'oggetto CurrentProject.
Sfrutteremo questa Connessione per inviare al DB delle frasi SQL idonee a creare la tabella che ospiterà i dati e le frasi di UPDATE (sempre SQL) che ci permetteranno di inserirvi i dati letti da Excel.

Sub ImportaDatiExcel()
 
' Dichiaro il Recordset e la variabile per la Query
Dim oRSet As ADODB.Recordset
Dim SQL As String
 
' Apro la Connessione
ApriConnessione "E:\VisualBasic\My Tutorials\Interventi Blog\Automazione Office\ExcelProva.xls"
 
' Assegno un nuovo Recordset
Set oRSet = New ADODB.Recordset
 
' Creo la query per reperire i dati sul foglio Excel
SQL = "SELECT Cognome, Nome, Telefono FROM [Foglio1$]"
oRSet.Open SQL, oConn
 
' Attraverso un'apposita routine, elimino la
' tabella di destinazione, se esistente.
EliminaTabella ("DatiExcel")
 
' Creo la Tabella di destinazione dei dati, definendone i campi con nome, tipo e lunghezza
SQL = "CREATE TABLE DatiExcel (Cognome TEXT(50), Nome TEXT(50), Telefono TEXT(20))"
CurrentProject.Connection.Execute SQL
 
' Mediante un ciclo, scorro tutto il Recordset
' compiendo una serie di INSERT sulla tabella
' di destinazione appena creata
With oRSet
    While Not .EOF
        SQL = "INSERT INTO DatiExcel " & _
              "(Cognome, Nome, Telefono) VALUES (" & _
              "'" & .Fields("Cognome").Value & "', " & _
              "'" & .Fields("Nome").Value & "', " & _
              "'" & .Fields("Telefono").Value & "')"
 
        CurrentProject.Connection.Execute SQL
        oRSet.MoveNext
    Wend
End With
 
' Chiudo il Recordset e annullo l'oggetto
oRSet.Close
Set oRSet = Nothing
 
' Chiudo la connessione
ChiudiConnessione
 
End Sub

Nel codice appena riportato ho richiamato anche la Routine EliminaTabella, appositamente creata per eliminare la tabella, nel caso fosse presente, prima di crearne una nuova. E' ovvio che, se non si cancellasse la tabella preesistente si avrebbe un errore. Utilizzando una routine separata, l'errore viene trascurato, se rilasciato dall'applicazione, evitandoci di inserire ulteriori righe di codice alla routine di importazione mantenendola, tutto sommato, compatta.
Ecco la dichiarazione della routine:

Sub EliminaTabella(NomeTabella As String)
 
On Error Resume Next
CurrentProject.Connection.Execute "DROP TABLE " & NomeTabella
 
End Sub

Abbiamo praticamente terminato e l'unica cosa che rimane da fare è provare il tutto. Se tutto andrà a buon fine, riusciremo ad importare i dati contenuti in un File Excel aperto come fosse un DB in un Database Access, dentro una tabella creata dinamicamente. La creazione della tabella, in questo caso, è stata fatta in maniera un po' "rigida" nel senso che abbiamo già impostato da codice i campi con loro tipo e lunghezza, ma volendo rendere il tutto ancora più flessibile, prima della creazione della tabella, potremmo scorrere con un ciclo i campi del Recordset importato per valutarne numero, nome e tipo di dati inseriti, creando una tabella esattamente corrispondente ai dati da importare.

Nel caso vogliate segnalarmi inesattezze o richiedere ulteriori chiarimenti contattatemi pure senza remore