L'approccio più utilizzato per il trasferimento di dati in
una cartella di lavoro di Excel è basato sull'automazione. L'automazione
consente di specificare con la massima flessibilità la posizione dei dati nella
cartella di lavoro, nonché di formattare la cartella di lavoro e definire
diverse impostazioni in fase di esecuzione. Con l'automazione è possibile
utilizzare diversi approcci per il trasferimento dei dati:
- Trasferimento dei dati in base alla cella
- Trasferimento dei dati di una matrice in un intervallo di
celle
- Trasferimento dei dati di un recordset ADO in un intervallo
di celle mediante il metodo CopyFromRecordset
- Creazione di un oggetto QueryTable in un foglio di lavoro di Excel che contiene i risultati di una
query eseguita su un'origine dati ODBC o OLEDB
- Trasferimento dei dati negli Appunti e inserimento del
contenuto degli Appunti in un foglio di lavoro di Excel
Per il trasferimento di dati in Excel sono inoltre disponibili
metodi che non richiedono necessariamente l'automazione. L'esecuzione di
un'applicazione lato server può ad esempio costituire un approccio valido per
evitare che i client debbano elaborare elevate quantità di dati. Di seguito
sono elencati i metodi utilizzabili per trasferire i dati senza l'automazione:
- Trasferimento dei dati in un file di testo delimitato da
tabulazioni o da virgole che possa essere analizzato nelle celle di un foglio
di lavoro di Excel
- Trasferimento dei dati in un foglio di lavoro tramite
ADO
- Trasferimento dei dati in Excel tramite DDE (Dynamic Data
Exchange)
Nelle sezioni seguenti vengono fornite ulteriori informazioni
su ciascuna soluzione.
Nota In Microsoft Office Excel 2007 è possibile utilizzare il nuovo
formato di file Cartella di lavoro di Excel 2007 (*.xlsx) per il salvataggio
delle cartelle di lavoro. A tale scopo, individuare la riga di codice seguente
negli esempi riportati di seguito:
oBook.SaveAs "C:\Book1.xls"
Sostituire il codice con la riga di codice seguente:
oBook.SaveAs "C:\Book1.xlsx"
Per impostazione predefinita, inoltre, il database Northwind non è
incluso in Office 2007. È tuttavia possibile scaricare il database Northwind da
Microsoft Office Online.
Utilizzo dell'automazione per il trasferimento di dati cella per cella
Con l'automazione, è possibile trasferire dati in un foglio di
calcolo una cella alla volta:
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
'Start a new workbook in Excel
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
'Add data to cells of the first worksheet in the new workbook
Set oSheet = oBook.Worksheets(1)
oSheet.Range("A1").Value = "Last Name"
oSheet.Range("B1").Value = "First Name"
oSheet.Range("A1:B1").Font.Bold = True
oSheet.Range("A2").Value = "Doe"
oSheet.Range("B2").Value = "John"
'Save the Workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit Il trasferimento di dati cella per cella è un metodo accettabile per
piccole quantità di dati. È infatti possibile inserire i dati in qualsiasi
punto della cartella di lavoro e formattare le celle in base alle condizioni in
fase di esecuzione. Questo approccio
non è tuttavia consigliato se si dispone di una quantità elevata di
dati da trasferire in una cartella di lavoro di Excel. Ciascun oggetto
Range acquisito in fase di esecuzione causa una richiesta di
interfaccia pertanto il trasferimento dei dati con questo approccio può
risultare rallentato. In Microsoft Windows 95 e Windows 98 è inoltre presente
una limitazione di 64 KB per le richieste di interfaccia. Se si raggiunge o si
supera questo limite nelle richieste di interfaccia, è possibile che il server
di automazione Excel si blocchi o che vengano visualizzati messaggi di errore
in cui si informa che la memoria disponibile non è sufficiente. Questa
limitazione relativa a Windows 95 e Windows 98 viene illustrata nel seguente
articolo della Microsoft Knowledge Base:
216400
(http://support.microsoft.com/kb/216400/
)
Blocco dell'applicazione client con Windows 95/98 a causa dell'automazione COM tra processi
Anche in questo caso il trasferimento di dati cella
per cella è accettabile solo per quantità ridotte di dati. Se è necessario
trasferire in Excel quantità elevate di dati, valutare la possibilità di
adottare una delle soluzioni descritte di seguito.
Per ulteriore
codice di esempio relativo all'automazione di Excel, fare clic sul numero
dell'articolo della Microsoft Knowledge Base riportato di seguito:
219151
(http://support.microsoft.com/kb/219151/
)
Automazione di Microsoft Excel da Visual Basic
Utilizzo dell'automazione per trasferire matrici di dati in un intervallo di un foglio di lavoro
È possibile trasferire una matrice di dati in un intervallo di
celle contemporaneamente:
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
'Start a new workbook in Excel
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
'Create an array with 3 columns and 100 rows
Dim DataArray(1 To 100, 1 To 3) As Variant
Dim r As Integer
For r = 1 To 100
DataArray(r, 1) = "ORD" & Format(r, "0000")
DataArray(r, 2) = Rnd() * 1000
DataArray(r, 3) = DataArray(r, 2) * 0.7
Next
'Add headers to the worksheet on row 1
Set oSheet = oBook.Worksheets(1)
oSheet.Range("A1:C1").Value = Array("Order ID", "Amount", "Tax")
'Transfer the array to the worksheet starting at cell A2
oSheet.Range("A2").Resize(100, 3).Value = DataArray
'Save the Workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit Se si trasferiscono i dati utilizzando una matrice anziché cella per
cella, è possibile migliorare notevolmente le prestazioni quando è necessario
gestire elevate quantità di dati. Considerare questa riga del codice sopra
riportato che consente di trasferire dati in 300 celle del foglio di lavoro:
oSheet.Range("A2").Resize(100, 3).Value = DataArray Questa riga rappresenta due richieste di interfaccia, una per l'oggetto
Range restituito dal metodo
Range e l'altra per l'oggetto
Range restituito dal metodo
Resize. Viceversa, per il trasferimento dei dati cella per cella è
necessario inoltrare a oggetti
Range richieste relative a 300 interfacce. Quando possibile, è
opportuno sfruttare il trasferimento in massa dei dati e ridurre il numero di
richieste di interfaccia effettuate.
Utilizzo dell'automazione per trasferire un recordset ADO in un intervallo di un foglio di lavoro
In Excel 2000 è stato introdotto il metodo
CopyFromRecordset che consente di trasferire un recordset ADO (o DAO) in un
intervallo di un foglio di lavoro. Nel codice riportato di seguito viene
illustrato come automatizzare Excel 2000, Excel 2002 o Office Excel 2003 per il
trasferimento del contenuto della tabella Orders nel database di esempio
Northwind mediante il metodo
CopyFromRecordset.
'Create a Recordset from all the records in the Orders table
Dim sNWind As String
Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset
sNWind = _
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sNWind & ";"
conn.CursorLocation = adUseClient
Set rs = conn.Execute("Orders", , adCmdTable)
'Create a new workbook in Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
'Transfer the data to Excel
oSheet.Range("A1").CopyFromRecordset rs
'Save the Workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit
'Close the connection
rs.Close
conn.Close Se si utilizza la versione del database Northwind disponibile in
Office 2007, è necessario sostituire la riga di codice seguente nell'esempio:
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ sNWind & ";"
Sostituire questa riga di codice con la seguente:
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _ sNWind & ";"
Anche in Excel 97 è disponibile un metodo
CopyFromRecordset che può tuttavia essere utilizzato solo con un recordset DAO. ADO
non è supportato con il metodo
CopyFromRecordset ed Excel 97.
Per ulteriori informazioni sull'utilizzo di
ADO e del metodo
CopyFromRecordset, vedere il seguente articolo della Microsoft Knowledge
Base:
246335
(http://support.microsoft.com/kb/246335/
)
Trasferimento di dati da un recordset ADO in Excel tramite l'automazione
Utilizzo dell'automazione per creare un oggetto QueryTable in un foglio di lavoro
Un oggetto
QueryTable rappresenta una tabella creata in base ai dati restituiti da
un'origine dati esterna. Quando si automatizza Microsoft Excel, è possibile
creare un oggetto
QueryTable fornendo una stringa di connessione a un'origine dati OLEDB o
ODBC unitamente a una stringa SQL. Il recordset viene generato in Excel e
inserito nel foglio di lavoro nella posizione specificata. L'utilizzo di
oggetti
QueryTable presenta diversi vantaggi rispetto al metodo
CopyFromRecordset:
- La creazione del recordset e il relativo inserimento nel
foglio di lavoro sono gestiti da Excel.
- È possibile salvare la query con l'oggetto QueryTable e aggiornarla successivamente per ottenere un recordset
aggiornato.
- Quando si aggiunge un nuovo oggetto QueryTable al foglio di lavoro, è possibile specificare che i dati già
esistenti nelle celle del foglio di lavoro vengano spostati in modo da
permettere l'inserimento dei nuovi dati. Per ulteriori informazioni, vedere la
proprietà RefreshStyle.
Nel codice riportato di seguito viene illustrato come
automatizzare Excel 2000, Excel 2002 o Office Excel 2003 per creare un nuovo
oggetto
QueryTable in un foglio di lavoro di Excel utilizzando i dati del database
di esempio Northwind:
'Create a new workbook in Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
'Create the QueryTable
Dim sNWind As String
sNWind = _
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
Dim oQryTable As Object
Set oQryTable = oSheet.QueryTables.Add( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sNWind & ";", oSheet.Range("A1"), "Select * from Orders")
oQryTable.RefreshStyle = xlInsertEntireRows
oQryTable.Refresh False
'Save the Workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.QuitUtilizzo degli Appunti
È possibile utilizzare gli Appunti di Windows per trasferire dati
in un foglio di lavoro. Per incollare i dati in più celle di un foglio di
lavoro, è possibile copiare una stringa in cui le colonne sono delimitate da
tabulazioni e righe delimitate da ritorni a capo. Nel codice riportato di
seguito viene illustrato l'utilizzo degli Appunti di Windows in Visual Basic
per trasferire dati in Excel:
'Copy a string to the clipboard
Dim sData As String
sData = "FirstName" & vbTab & "LastName" & vbTab & "Birthdate" & vbCr _
& "Bill" & vbTab & "Brown" & vbTab & "2/5/85" & vbCr _
& "Joe" & vbTab & "Thomas" & vbTab & "1/1/91"
Clipboard.Clear
Clipboard.SetText sData
'Create a new workbook in Excel
Dim oExcel As Object
Dim oBook As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
'Paste the data
oBook.Worksheets(1).Range("A1").Select
oBook.Worksheets(1).Paste
'Save the Workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.QuitCreazione di un file di testo delimitato analizzabile in Excel in righe e colonne
In Excel è possibile aprire file delimitati da tabulazioni o da
virgole e analizzare correttamente i dati nelle celle. Questa funzione può
essere utilizzata per trasferire elevate quantità di dati in un foglio di
lavoro servendosi dell'automazione in modo limitato. Questo approccio può
rivelarsi adeguato per un'applicazione client-server perché è possibile
generare il file di testo sul lato client. È quindi possibile aprire il file di
testo dal client, utilizzando l'automazione quando
necessario.
Nell'esempio di codice riportato di seguito viene
illustrato come creare un file di testo delimitato da virgole da un recordset
ADO:
'Create a Recordset from all the records in the Orders table
Dim sNWind As String
Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim sData As String
sNWind = _
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sNWind & ";"
conn.CursorLocation = adUseClient
Set rs = conn.Execute("Orders", , adCmdTable)
'Save the recordset as a tab-delimited file
sData = rs.GetString(adClipString, , vbTab, vbCr, vbNullString)
Open "C:\Test.txt" For Output As #1
Print #1, sData
Close #1
'Close the connection
rs.Close
conn.Close
'Open the new text file in Excel
Shell "C:\Program Files\Microsoft Office\Office\Excel.exe " & _
Chr(34) & "C:\Test.txt" & Chr(34), vbMaximizedFocus Se si utilizza la versione del database Northwind disponibile in
Office 2007, è necessario sostituire la riga di codice seguente nell'esempio:
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sNWind & ";"Sostituire questa riga di codice con la seguente:
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
sNWind & ";" Se il file di testo è caratterizzato dall'estensione CSV, verrà aperto
senza che venga visualizzata l'Importazione guidata testo e si presupporrà che
sia un file di testo delimitato da virgole. Analogamente, se è caratterizzato
dall'estensione txt, il file verrà automaticamente analizzato utilizzando le
tabulazioni come delimitatori di testo.
Nell'esempio di codice
precedente Excel è stato avviato tramite l'istruzione
Shell e come argomento della riga di comando è stato utilizzato il nome
del file. Non è quindi stata utilizzata l'automazione. Tuttavia, se si
desidera, è possibile utilizzare in modo limitato l'automazione per aprire il
file di testo e salvarlo nel formato della cartella di lavoro di Excel:
'Create a new instance of Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
'Open the text file
Set oBook = oExcel.Workbooks.Open("C:\Test.txt")
'Save as Excel workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls", xlWorkbookNormal
oExcel.Quit Per ulteriori informazioni sull'utilizzo di operazioni di I/O sui file
dall'applicazione Visual Basic, vedere il seguente articolo della Microsoft
Knowledge Base:
172267
(http://support.microsoft.com/kb/172267/
)
Utilizzo di RECEDIT.VBP per illustrare operazioni di I/O sui file in Visual Basic
Trasferimento dei dati in un foglio di lavoro tramite ADO
È possibile utilizzare il provider Microsoft Jet OLE DB per
aggiungere record a una tabella in una cartella di lavoro di Excel esistente.
Una "tabella" in Excel corrisponde in pratica a un intervallo di celle che può
essere caratterizzato da un nome definito. La prima riga dell'intervallo deve
contenere le intestazioni (o nomi di campo) e tutte le altre righe
dell'intervallo contengono i record. Nella procedura seguente viene illustrato
come creare una cartella di lavoro con una tabella vuota denominata
MyTable.
Excel 97, Excel 2000 ed Excel 2003
- Aprire una nuova cartella di lavoro in Excel.
- Aggiungere le seguenti intestazioni alle celle A1:B1 di
Foglio1:
R1: Nome B1: Cognome - Formattare la cella B1 applicando l'allineamento a
destra.
- Selezionare l'intervallo di celle A1:B1.
- Scegliere Nome dal menu Inserisci, quindi fare clic su Definisci. Immettere il nome MyTable e scegliere OK.
- Salvare la nuova cartella di lavoro con il nome
C:\Book1.xls e chiudere Excel.
Per aggiungere record a
MyTable tramite
ADO, è possibile utilizzare codice analogo al seguente:
'Create a new connection object for Book1.xls
Dim conn As New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Book1.xls;Extended Properties=Excel 8.0;"
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
" values ('Bill', 'Brown')"
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
" values ('Joe', 'Thomas')"
conn.CloseExcel 2007
- In Excel 2007 avviare una nuova cartella di
lavoro.
- Aggiungere le seguenti intestazioni alle celle A1:B1 di
Foglio1:
A1: Nome B1: Cognome - Formattare la cella B1 applicando l'allineamento a
destra.
- Selezionare l'intervallo di celle A1:B1.
- Sulla barra multifunzione fare clic sulla scheda
Formule, quindi scegliere Definisci nome.
Digitare il nome MyTable e scegliere
OK.
- Salvare la nuova cartella di lavoro con il nome
C:\Book1.xls e chiudere Excel.
Per aggiungere record alla tabella MyTable tramite ADO,
utilizzare codice analogo a quello dell'esempio seguente.
'Create a new connection object for Book1.xls
Dim conn As New ADODB.Connection
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\Book1.xlsx;Extended Properties=Excel 12.0;"
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
" values ('Scott', 'Brown')"
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
" values ('Jane', 'Dow')"
conn.Close Quando i record vengono aggiunti alla tabella in questo modo, la
formattazione della cartella di lavoro viene mantenuta. Nell'esempio precedente
i nuovi campi aggiunti alla colonna B vengono formattati con allineamento a
destra. Ogni record aggiunto a una riga acquisisce il formato della riga
precedente.
Si noti che quando si aggiunge un record a una cella o a
celle del foglio di lavoro, gli eventuali dati presenti in tali celle vengono
sovrascritti. Ciò significa che le righe del foglio di lavoro non vengono
spostate verso il basso quando si aggiungono nuovi record. È opportuno tenere
presente questo comportamento quando si progetta il layout dei dati dei fogli
di lavoro.
Nota Il metodo per aggiornare i dati in un foglio di lavoro di Excel
tramite ADO o DAO non può essere utilizzato in ambiente Visual Basic,
Application Edition in Access in seguito all'installazione di Office 2003
Service Pack 2 (SP2) o dell'aggiornamento per Access 2002 incluso nell'articolo
904018 della Microsoft Knowledge Base. È invece utilizzabile in ambiente Visual
Basic, Application Edition da altre applicazioni Office, ad esempio Word, Excel
e Outlook.
Per
ulteriori informazioni, fare clic sui numeri degli articoli della Microsoft
Knowledge Base riportati di seguito:
904953
(http://support.microsoft.com/kb/904953/
)
Impossibile modificare, aggiungere o eliminare dati in tabelle collegate a una cartella di lavoro di Excel in Office Access 2007, in Office Access 2003 o in Access 2002
904018
(http://support.microsoft.com/kb/904018/
)
Descrizione dell'aggiornamento per Access 2002 del 18 ottobre 2005
Per ulteriori informazioni sull'utilizzo di ADO
per accedere a una cartella di lavoro di Excel, vedere i seguenti articoli
della Microsoft Knowledge Base:
195951
(http://support.microsoft.com/kb/195951/
)
Interrogazione e aggiornamento dei dati di Excel mediante la tecnologia ADO da una pagina ASP
Utilizzo di DDE per il trasferimento di dati in Excel
DDE rappresenta uno strumento alternativo all'automazione per
comunicare con Excel e trasferire dati. Con l'avvento dell'automazione e del
componente COM, DDE non rappresenta più il metodo preferito per comunicare con
altre applicazioni e deve essere utilizzato solo se non sono disponibili altre
soluzioni.
Per trasferire dati in Excel tramite DDE, è possibile:
- Utilizzare il metodo LinkPoke per scrivere in memoria dati in un intervallo specifico di
celle
Oppure - Utilizzare il metodo LinkExecute per inviare comandi che verranno eseguiti in Excel.
Nell'esempio di codice riportato di seguito viene illustrato
come stabilire una conversazione DDE con Excel per scrivere in memoria dati in
celle di un foglio di lavoro ed eseguire comandi. Se si utilizza questo
esempio, per stabilire correttamente una conversazione DDE con
LinkTopic Excel|MyBook.xls, è necessario aver già aperto una cartella di
lavoro denominata
MyBook.xls in un'istanza in esecuzione
di Excel.
Nota In Excel 2007 è possibile utilizzare il nuovo formato di file
xlsx per salvare le cartelle di lavoro. Assicurarsi di aggiornare il nome file
nell'esempio di codice seguente.
Nota In questo esempio
Text1 rappresenta un controllo
Text Box in un form Visual Basic:
'Initiate a DDE communication with Excel
Text1.LinkMode = 0
Text1.LinkTopic = "Excel|MyBook.xls"
Text1.LinkItem = "R1C1:R2C3"
Text1.LinkMode = 1
'Poke the text in Text1 to the R1C1:R2C3 in MyBook.xls
Text1.Text = "one" & vbTab & "two" & vbTab & "three" & vbCr & _
"four" & vbTab & "five" & vbTab & "six"
Text1.LinkPoke
'Execute commands to select cell A1 (same as R1C1) and change the font
'format
Text1.LinkExecute "[SELECT(""R1C1"")]"
Text1.LinkExecute "[FONT.PROPERTIES(""Times New Roman"",""Bold"",10)]"
'Terminate the DDE communication
Text1.LinkMode = 0 Quando si esegue
LinkPoke con Excel, per la specifica dell'intervallo per
LinkItem viene utilizzata la notazione riga-colonna (R1C1). Se si intende
scrivere in memoria dati in più celle, è possibile utilizzare una stringa in
cui le colonne sono delimitate da tabulazioni e le righe da ritorni a
capo.
Quando si utilizza
LinkExecute per chiedere l'esecuzione di un comando in Excel, per la
specifica del comando è necessario utilizzare la sintassi XLM (Excel Macro
Language). La documentazione relativa a XLM non è inclusa con Excel 97 e
versioni successive. Per ulteriori informazioni su come ottenere la
documentazione relativa a XLM, vedere il seguente articolo della Microsoft
Knowledge Base:
143466
(http://support.microsoft.com/kb/143466/
)
File Macro97.exe disponibile nei servizi in linea
DDE non costituisce la soluzione consigliata per
comunicare con Excel. L'automazione assicura massima flessibilità e consente di
accedere più agevolmente alle nuove funzionalità disponibili in
Excel.
Torna all'inizio
