Ci sono situazioni reali in cui Excel custodisce dati che dobbiamo in qualche modo estrarre. Utilizzare Excel come fonte di dati può non essere il massimo della vita innanzitutto perché non ammette accessi contemporanei. Excel è un programma che gestisce un foglio elettronico, lo fa anche molto bene, ma non nasce certo come database.
In ogni caso può capitare di doversi servire di Excel per gestire dati strutturati (del resto la tabella è un insieme di campi organizzati in righe ed è quindi la fonte dati strutturata per definizione). In questo articolo vedremo come fare.
Da diversi anni è possibile utilizzare ADO (ActiveX Data Objects) con il provider Microsoft Jet OLE DB 4.0 per leggere e scrivere dati nelle cartelle di lavoro di Microsoft Excel.
Microsoft consiglia l’utilizzo di ADO per trasferire o recuperare dati da una cartella di lavoro di Excel in quanto offre agli sviluppatori diversi vantaggi in termini di automazione in Excel: in termini di prestazioni e di scalabilità. Microsoft Excel è un server ActiveX out-of-process mentre ADO viene eseguito in-process e consente di ridurre il sovraccarico dovuto alle chiamate fuori processo. Per applicazioni Web, non è sempre desiderabile automatizzare Microsoft Excel là dove ADO offre una soluzione più scalabile per la gestione dei dati di una cartella di lavoro (la scalabilità è la capacità di un software/hardvware di adattarsi ad un aumento di domanda o di lavoro).
ADO può essere utilizzato per trasferire dati non elaborati in una cartella di lavoro.
Non è possibile utilizzare ADO per applicare formati o formule alle celle, tuttavia, è possibile trasferire dati in una cartella di lavoro preformattata il cui formato viene mantenuto. Qualora fosse necessaria una formattazione, anche”condizionale”, una volta inseriti i dati è possibile ottenere tale formattazione grazie all’automazione o con una macro nella cartella di lavoro.
Relativamente alle specifiche del provider Jet OLE DB per cartelle di lavoro di Excel, il modulo di gestione di database Microsoft Jet può essere utilizzato per accedere a dati in altri formati file di database, ad esempio appunto cartelle di lavoro di Excel, attraverso driver ISAM (Indexed Sequential Access Method). Al fine di aprire i formati esterni supportati dal provider Microsoft Jet 4.0 OLE DB, è possibile specificare il tipo di database nelle proprietà estese per la connessione. Il provider Jet OLE DB supporta praticamente tutti i tipi di database per le cartelle di lavoro Microsoft Excel, da Excel 3.0 in poi.
Per esempio, occorre utilizzare il tipo di database di origine “Excel 5.0” per cartelle di lavoro Microsoft Excel 5.0 e 7.0 (95) e utilizzare il tipo di database di origine “Excel 8.0” per cartelle di lavoro Microsoft Excel 8.0 (con estensione xls, fino a Excel 2007). Per le cartelle più recenti “xlsx” e “xlsm” l’origine si riferisce a “Excel 12.0 XML” o “Excel 12.0 Macro”.
Per scoprire quale sia la stringa di connessione più adatta, si può consultare il sito www.connectionstrings.com.
Questo sito è molto completo e contiene praticamente tutte le stringhe di connessione ai diversi provider e anche in diversi formati.
Cos’è la stringa di connessione? ADO utilizza un fornitore dati (provider) per connettersi a una base dati esterna di tipo “foglio Excel”: la stringa di connessione contiene le informazioni che il provider deve conoscere per essere in grado di stabilire una connessione al database (o file di dati, in generale).
Una stringa di connessione è costituito da una serie di coppie parola chiave – valore separate da punto e virgola (;) Il segno di uguale (=) connette ogni parola chiave e il relativo valore.
La stringa di connessione viene passata all’oggetto connection attraverso la sua funzione Open o attraverso l’impostazione della sua proprietà ConnectionString.
Riporto le stringhe di connessione più utilizzate quando si tratta con Excel; attenzione alle virgolette perché fanno parte della stringa di connessione e vanno eventualmente raddoppiate se costruite al volo:
Excel 97-2007 (xls)
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=”Excel 8.0;HDR=Yes;IMEX=1″;
oppure
OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=”Excel 8.0;HDR=Yes;IMEX=1″;
Excel 2007-2013 senza macro (xlsx):
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties=”Excel 12.0 Xml;HDR=YES”;
Excel 2007-2013 con macro (xlsm):
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsm;Extended Properties=”Excel 12.0 Macro;HDR=YES”;
File di testo
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\txtFilesFolder\;Extended Properties=”text;HDR=Yes;FMT=Delimited”;
In generale, nelle stringhe di connessione citate:
“HDR=Yes;” indica che la prima riga contiene le intestazioni della tabella, non dati: “HDR=No;” indica il contrario.
“IMEX=1;” dice al driver di leggere sempre dati mescolati (numeri, stirnghe, date) come testo. Questa opzione però può influenzare negativamente il modo come Excel tratta i dati.
“FMT=Delimited” indica che i dati sono stati delimitati nel file di testo. “FMT=Fixed” indica che i dati hanno una lunghezza fissa nel file.
Ottenuta la stringa di connessione giusta per il nostro file di dati, lo si apre, lo si interroga con una query e si assegna il set di righe a una variabile di tipo (ADO) recordset. La query è scritta in SQL, secondo le normali regole di SQL Jet (quello di Access).
La sintassi della stringa SQL è tipicamente “SELECT [colonna 1], [colonna 2] FROM [Foglio1$]”. Notare che i nomi delle colonne vanno tra parentesi quadre se contengono spazi e che i nomi dei fogli sono seguiti dal segno del dollaro e devono essere racchiusi da parentesi quadre.
Altro esempio: “SELECT * FROM [Foglio1$A5:D50]”, recupera i dati dal foglio1, da A5 a D50.
Bisogna fare attenzione che per recuperare una cella singola si deve specificare espressamente: “SELECT * FROM [Foglio1$A5:A5]”
Lo scheletro delle operazioni si riassume così:
– creare gli oggetti necessari (connection e recordset)
– aprire una connessione al file di dati
– aprire il recordset con l’istruzione SQL
– copiare sul foglio il risultato del recordset
Lo scenario può complicarsi molto in funzione dell’esigenza, e questo influisce sulla complessità della query che si intende realizzare.
Il bello di questa tecnica è che è molto veloce, trasparente all’utente, non è necessari aprire un file per leggervi dei dati (il file Excel con i dati può restare chiuso), e la stessa tecnica, con la query opportuna, può essere utilizzata anche per scrivere dei dati dentro un file di Excel.
Per spiegare concretamente, fornisco un piccolo esempio di lettura dati con una tabella fittizia (dati di vendita inventati per un’impresa di articoli di cancelleria, dove ogni riga rappresenta un ordine).
Il codice presente nel modulo, ampiamente commentato, è il seguente:
Option Explicit
Sub excel_ADO()
Dim cn As Object 'dichiara l'oggetto che sarà la connessione alla fonte dati
Dim rs As Object 'dichiara l'oggetto che sarà il recordset
Dim s As String
Const adOpenStatic = 3 'proprietà CursorType. Usa un cursore statico
Const adLockOptimistic = 3 'proprietà LockType. Usa un blocco dal record corrente
Const adCmdText = &H1 'proprietà CommandType. Si aspetta un comando di testo (non una tabella, p.es.)
'se la fonte dati è in questo stesso file ma non è ancora stato salvato,
'occorre farlo. Quindi salva una copia di se stesso con nome temporaneo
'per accedervi successivamente; alla fine cancella il file temporaneo
's = ThisWorkbook.Path & "\temporary.xlsm"
'ThisWorkbook.SaveCopyAs s
'se la fonte dati è in un file Excel in un'altra cartella,
'basta farvi riferimento precisando percorso e nome completo
's = "C:\TEST\esempio file dati.xlsm"
'se la fonte dati è in questo stesso file che è già stato salvato,
'basta recuperarne il nome completo
s = ThisWorkbook.FullName
'crea gli oggetti ADO connection e recordset
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
'apre una connessione al file sopra individuato (il cui percorso è nella variabile "s");
'attenzione alle doppie virgolette (fanno parte della stringa di connessione
'e quindi vanno raddoppiate in fase di costruzione della stringa)
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & s & ";" & _
"Extended Properties=""Excel 12.0 Macro;HDR=Yes;"";"
'apre il recordset impostando la stringa query necessaria
'qui selezionaiamo tre campi e un campo calcolato al volo
'dal foglio1, colonne A:G, raggruppando i dati sui primi tre campi
'al metodo Open dobbiamo passare il riferimento all'oggetto connection
'e i tre parametri opzionali per impostare il tipo di cursore.
rs.Open "SELECT Region, Rep, Item, Sum(Units * UnitCost) As Total " & _
"FROM [Foglio1$A:G] " & _
"GROUP BY Region, Rep, Item; ", _
cn, adOpenStatic, adLockOptimistic, adCmdText
'cancelliamo il contenuto delle colonne A:K
Range("K:N").ClearContents
'predisponiamo l'intestazione del range che accoglierà i dati
Range("K1:M1").Value = Range("B1:D1").Value
Range("N1") = "Sum Total"
'riversiamo i dati del recordset con il metodo appropriato fornito da Excel
'nota che si poteva usare un Do .. Loop per scorrere il recordset riga per riga
'e recuperare ogni dato singolarmente
Range("K2").CopyFromRecordset rs
'chiudiamo il recordset, la connessione e annientiamo gli oggetti
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub
Riportate eventuali commenti se qualcosa è inesatto o poco chiaro!
Scaricate il file di esempio da qui: READWRITE EXCEL VIA ADO
› Excel e ADO: scrittura/lettura dati da tabelle Excel