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
Tag:             

Excel e ADO: scrittura/lettura dati da tabelle Excel

Login Registrati
Stai vedendo 12 articoli - dal 1 a 12 (di 12 totali)
  • Autore
    Articoli
  • #12488 Score: 0 | Risposta

    vecchio frac
    Senior Moderator
      238 pts

      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...

      [Leggi tutto al seguente link: https://www.excelvba.it/forumexcel/excel-e-ado-scrittura-lettura-dati-da-tabelle-excel/]

      #12503 Score: 0 | Risposta

      albatros54
      Moderatore
        81 pts

         

        Qual è il punto di avere gusti diversi, se non mostrare che i cervelli lavorano diversamente, che pensiamo diversamente? ( Alan Turing)
        Sempre il mare, uomo libero, amerai!
        ( Charles Baudelaire )
        #14538 Score: 0 | Risposta

        PLB83
        Partecipante

          Grande articolo! 🙂

          #25079 Score: 0 | Risposta

          Ornella

            Si può strutturare la query richiamando anche un foglio dati esterno alla cartella aperta ?  Se si qual'è la sintassi corretta per indicare la cartella ? Ad esempio se sono nella cartella Cartel1.xlsm ed il Foglio1 è nella Cartel2.xlsx come dovrei modificare la sintassi "SELECT * FROM [Foglio1$A5:D50]" ?  

            #25090 Score: 0 | Risposta

            albatros54
            Moderatore
              81 pts

              non hai letto bene il post sul blog, perche risponde anche alla tua domanda

              cit."'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""

               


              Qual è il punto di avere gusti diversi, se non mostrare che i cervelli lavorano diversamente, che pensiamo diversamente? ( Alan Turing)
              Sempre il mare, uomo libero, amerai!
              ( Charles Baudelaire )
              #25276 Score: 0 | Risposta

              Ornella

                chiedo scusa sono inesperta ho provato e non riesco a metterlo in pratica. Ho due cartelle excel aperte "Cartel1.xlsm" e "Cartel2.xlsx" vorrei fare una query per estrarre dei campi mettendo in relazione due fogli di due cartelle diverse come ad esempio "Select a.codice, b.cognome, b.nome from Cartel1.xlsm![sintesi$a:b] as a, Cartel2.xlsx![anagrafica$a:c] as b where a.codice = b.codice" ma non riesco a capire quale sintassi dovrei utilizzare e, se si può fare, perchè finora ho solo trovato esempi che puntavano a fogli diversi ma della stessa cartella excel.

                #25281 Score: 0 | Risposta

                albatros54
                Moderatore
                  81 pts

                  non avendo i file su cui poter lavorare e non sapere quello che tu vuoi ottenete, mi sembra un'operazione assai ardua  

                  comunque guarda questo post   https://www.excelvba.it/forumexcel/forums/discussione/prelevare-dati-da-celle-in-cartella-chiusa-in-base-a-valore-da-foglio-aperto/

                   

                   


                  Qual è il punto di avere gusti diversi, se non mostrare che i cervelli lavorano diversamente, che pensiamo diversamente? ( Alan Turing)
                  Sempre il mare, uomo libero, amerai!
                  ( Charles Baudelaire )
                  #40819 Score: 0 | Risposta

                  Ignazio Maria Mancini

                    Non riesco a far funzionare la macro, il sistema non ha la possibilità di collegarsi a server esterni. Uno dei file, quello nella rete aziendale è utilizzato dal personale per ordinazioni mensa, con più fogli di lavoro.  L'altro, un semplice foglio  uguale, che lavora su pc personale collegato alla stessa rete interna, impostato come il primo,  tramite macro, deve richiamare tutte le prenotazioni da passare in cucina. Spero di essere stato chiaro. 

                    Grazie ancora

                    #40823 Score: 0 | Risposta

                    albatros54
                    Moderatore
                      81 pts

                      Non conviene mai accodarsi ad un post  di parecchi anni fa, consigliamo sempre di aprire un nuovo post, magari facendo riferimento a post precedente.

                      Comunque non sei stato molto chiaro su quello che vuoi fare e quello che vuoi ottenere.

                       

                      Qual è il punto di avere gusti diversi, se non mostrare che i cervelli lavorano diversamente, che pensiamo diversamente? ( Alan Turing)
                      Sempre il mare, uomo libero, amerai!
                      ( Charles Baudelaire )
                      #41833 Score: 0 | Risposta

                      Marino
                      Partecipante

                        Dovrei estrarre il nome dei fogli presenti in un file excel da mettere poi un una combobox per la gestione delle annualità.

                        Questo per gestione di evitare poi di eseguire comandi che creerebbero errore.

                        Grazie mille

                        #41834 Score: 0 | Risposta

                        vecchio frac
                        Senior Moderator
                          238 pts

                          Marino ha scritto:

                          Questo per gestione di evitare poi di eseguire comandi che creerebbero errore.

                          Non (mi) è chiaro quello che intendi dire.

                          albatros54 ha scritto:

                          consigliamo sempre di aprire un nuovo post

                          Segui il consiglio di Albatros. Per favore apri una nuova discussione esponendo bene il problema, l'obiettivo e magari allegando qualche file di esempio.

                          Garzie

                          #41836 Score: 0 | Risposta

                          LucaSR
                          Partecipante
                            6 pts

                            Ciao fai come ti dicono, così ti do la mia soluzione "scritta", poi quando avrai scritto il tuo codice lo rivedremo insieme   

                          Login Registrati
                          Stai vedendo 12 articoli - dal 1 a 12 (di 12 totali)
                          Rispondi a: Excel e ADO: scrittura/lettura dati da tabelle Excel
                          Gli allegati sono permessi solo ad utenti REGISTRATI
                          Le tue informazioni: