Metodi per il trasferimento di dati in Excel da Visual Basic
Riepilogo
Questo articolo illustra numerosi metodi per il trasferimento dei dati in Microsoft Excel dall'applicazione Microsoft Visual Basic. Questo articolo presenta anche i vantaggi e gli svantaggi per ogni metodo, in modo da poter scegliere la soluzione più adatta alle esigenze.
Ulteriori informazioni
L'approccio più comunemente usato per trasferire i dati in una cartella di lavoro di Excel è Automazione. L'automazione offre la massima flessibilità per specificare la posizione dei dati nella cartella di lavoro, nonché la possibilità di formattare la cartella di lavoro e di impostare diverse impostazioni in fase di esecuzione. Con Automazione è possibile usare diversi approcci per trasferire i dati:
- Trasferire una cella di dati per cella
- Trasferire dati in una matrice in un intervallo di celle
- Trasferire i dati in un recordset ADO in un intervallo di celle tramite il metodo CopyFromRecordset
- Creare una tabella QueryTable in un foglio di lavoro di Excel contenente il risultato di una query in un'origine dati ODBC o OLEDB
- Trasferire i dati negli Appunti e quindi incollare il contenuto degli Appunti in un foglio di lavoro di Excel
Esistono anche metodi che è possibile usare per trasferire i dati in Excel che non richiedono necessariamente l'automazione. Se si esegue un server applicazioni, questo può essere un buon approccio per eliminare la maggior parte dell'elaborazione dei dati dai client. I metodi seguenti possono essere usati per trasferire i dati senza automazione:
- Trasferire i dati in un file di testo delimitato da tabulazioni o virgole che Excel può analizzare in seguito nelle celle di un foglio di lavoro
- Trasferire i dati in un foglio di lavoro usando ADO
- Trasferire dati in Excel usando DDE (Dynamic Data Exchange)
Le sezioni seguenti forniscono maggiori dettagli su ognuna di queste soluzioni.
Nota Quando si usa Microsoft Office Excel 2007, è possibile usare il nuovo formato di file Cartella di lavoro di Excel 2007 (*.xlsx) quando si salvano le cartelle di lavoro. A tale scopo, individuare la riga di codice seguente negli esempi di codice seguenti:
oBook.SaveAs "C:\Book1.xls"
Sostituire questo codice con con la riga di codice seguente:
oBook.SaveAs "C:\Book1.xlsx"
Inoltre, il database Northwind non è incluso in Office 2007 per impostazione predefinita. È tuttavia possibile scaricare il database Northwind da Microsoft Office Online.
Usare Automazione per trasferire le celle di dati in base alla cella
Con Automazione è possibile trasferire i dati in un foglio di lavoro 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 celle di dati per cella può essere un approccio perfettamente accettabile se la quantità di dati è piccola. È possibile inserire i dati in qualsiasi punto della cartella di lavoro e formattare le celle in modo condizionale in fase di esecuzione. Tuttavia, questo approccio non è consigliato se si dispone di una grande quantità di dati da trasferire in una cartella di lavoro di Excel. Ogni oggetto Range acquisito in fase di esecuzione genera una richiesta di interfaccia in modo che il trasferimento dei dati in questo modo possa essere lento. Inoltre, Microsoft Windows 95 e Windows 98 hanno una limitazione di 64K sulle richieste di interfaccia. Se si raggiunge o si supera questo limite di 64k per le richieste di interfaccia, il server di automazione (Excel) potrebbe smettere di rispondere o si potrebbero ricevere errori che indicano memoria insufficiente.
Ancora una volta, il trasferimento di cella dati per cella è accettabile solo per piccole quantità di dati. Se è necessario trasferire set di dati di grandi dimensioni in Excel, è consigliabile prendere in considerazione una delle soluzioni presentate in un secondo momento.
Per altri esempi di codice per l'automazione di Excel, vedere Come automatizzare Microsoft Excel da Visual Basic.
Usare l'automazione per trasferire una matrice di dati in un intervallo in un foglio di lavoro
Una matrice di dati può essere trasferita a un intervallo di più 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 usando una matrice anziché una cella per cella, è possibile ottenere un enorme miglioramento delle prestazioni con una grande quantità di dati. Si consideri questa riga del codice precedente che trasferisce i dati a 300 celle nel 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 un'altra per l'oggetto Range restituito dal metodo Resize. D'altra parte, il trasferimento della cella dati per cella richiederebbe richieste per 300 interfacce agli oggetti Range. Quando possibile, è possibile trarre vantaggio dal trasferimento in blocco dei dati e dalla riduzione del numero di richieste di interfaccia effettuate.
Usare l'automazione per trasferire un recordset ADO in un intervallo di fogli 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. Il codice seguente illustra come automatizzare Excel 2000, Excel 2002 o Office Excel 2003 e trasferire il contenuto della tabella Orders nel database di esempio Northwind usando 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
Nota Se si usa la versione di Office 2007 del database Northwind, è necessario sostituire la riga di codice seguente nell'esempio di codice:
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ sNWind & ";"
Sostituire questa riga di codice con la riga di codice seguente:
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _ sNWind & ";"
Excel 97 fornisce anche un metodo CopyFromRecordset, ma è possibile usarlo solo con un recordset DAO. CopyFromRecordset con Excel 97 non supporta ADO.
Per altre informazioni sull'uso di ADO e del metodo CopyFromRecordset, vedere Come trasferire dati da un recordset ADO a Excel con l'automazione.
Usare l'automazione per creare una tabella QueryTable in un foglio di lavoro
Un oggetto QueryTable rappresenta una tabella compilata da dati restituiti da un'origine dati esterna. Durante l'automazione di Microsoft Excel, è possibile creare una tabella QueryTable semplicemente fornendo un stringa di connessione a un'origine dati OLEDB o ODBC insieme a una stringa SQL. Excel si assume la responsabilità di generare il recordset e inserirlo nel foglio di lavoro nella posizione specificata. L'uso di QueryTables offre diversi vantaggi rispetto al metodo CopyFromRecordset:
- Excel gestisce la creazione del recordset e il relativo posizionamento nel foglio di lavoro.
- La query può essere salvata con queryTable in modo che possa essere aggiornata in un secondo momento per ottenere un recordset aggiornato.
- Quando si aggiunge una nuova tabella QueryTable al foglio di lavoro, è possibile specificare che i dati già esistenti nelle celle del foglio di lavoro vengano spostati in base ai nuovi dati.Per informazioni dettagliate, vedere la proprietà RefreshStyle.
Il codice seguente illustra come automatizzare Excel 2000, Excel 2002 o Office Excel 2003 per creare una nuova tabella query in un foglio di lavoro di Excel usando 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.Quit
Usare gli Appunti
Gli Appunti di Windows possono essere usati anche come meccanismo per il trasferimento dei 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 caratteri di tabulazione e le righe sono delimitate da ritorni a capo. Il codice seguente illustra come Visual Basic può usare l'oggetto Clipboard per trasferire i 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.Quit
Creare un file di testo delimitato che Excel può analizzare in righe e colonne
Excel può aprire file delimitati da tabulazioni o virgole e analizzare correttamente i dati in celle. È possibile sfruttare questa funzionalità quando si vuole trasferire una grande quantità di dati in un foglio di lavoro usando un numero minimo, se presente, di automazione. Questo potrebbe essere un buon approccio per un'applicazione client-server perché il file di testo può essere generato sul lato server. È quindi possibile aprire il file di testo nel client usando Automazione, dove appropriato.
Il codice seguente illustra 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
Nota Se si usa la versione di Office 2007 del database Northwind, è necessario sostituire la riga di codice seguente nell'esempio di codice:
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sNWind & ";"
Sostituire questa riga di codice con la riga di codice seguente:
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
sNWind & ";"
Se il file di testo ha un'estensione .CSV, excel apre il file senza visualizzare l'Importazione guidata testo e presuppone automaticamente che il file sia delimitato da virgole. Analogamente, se il file ha un'estensione .TXT, Excel analizza automaticamente il file usando i delimitatori di tabulazione.
Nell'esempio di codice precedente Excel è stato avviato usando l'istruzione Shell e il nome del file è stato usato come argomento della riga di comando. Nell'esempio precedente non è stata usata alcuna automazione. Tuttavia, se lo si desidera, è possibile usare una quantità minima di automazione per aprire il file di testo e salvarlo nel formato 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
Trasferire dati in un foglio di lavoro usando ADO
Usando il provider OLE DB di Microsoft Jet, è possibile aggiungere record a una tabella in una cartella di lavoro di Excel esistente. Una "tabella" in Excel è semplicemente un intervallo con un nome definito. La prima riga dell'intervallo deve contenere le intestazioni (o i nomi dei campi) e tutte le righe successive contengono i record. La procedura seguente illustra come creare una cartella di lavoro con una tabella vuota denominata MyTable.
Excel 97, Excel 2000 ed Excel 2003
Avviare una nuova cartella di lavoro in Excel.
Aggiungere le intestazioni seguenti alle celle A1:B1 di Sheet1:
A1: FirstName B1: LastName
Formattare la cella B1 come allineata a destra.
Selezionare A1:B1.
Scegliere Nomi dal menu Inserisci e quindi selezionare Definisci. Immettere il nome MyTable e fare clic su OK.
Salvare la nuova cartella di lavoro come C:\Book1.xls e chiudere Excel.
Per aggiungere record a MyTable usando ADO, è possibile usare codice simile 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.Close
Excel 2007
In Excel 2007 avviare una nuova cartella di lavoro.
Aggiungere le intestazioni seguenti alle celle A1:B1 di Sheet1:
A1: FirstName B1: LastName
Formattare la cella B1 come allineata a destra.
Selezionare A1:B1.
Sulla barra multifunzione fare clic sulla scheda Formule e quindi su Definisci nome. Digitare il nome MyTable e quindi fare clic su OK.
Salvare la nuova cartella di lavoro come C:\Book1.xlsx e quindi chiudere Excel.
Per aggiungere record alla tabella MyTable usando ADO, usare codice simile all'esempio di codice 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 si aggiungono record alla tabella in questo modo, la formattazione nella cartella di lavoro viene mantenuta. Nell'esempio precedente i nuovi campi aggiunti alla colonna B vengono formattati con l'allineamento a destra. Ogni record aggiunto a una riga prende in prestito il formato dalla riga sopra di essa.
Si noti che quando un record viene aggiunto a una cella o a celle del foglio di lavoro, sovrascrive tutti i dati presenti in precedenza in tali celle; In altre parole, le righe del foglio di lavoro non vengono "spostate verso il basso" quando vengono aggiunti nuovi record. È consigliabile tenere presente questo aspetto quando si progetta il layout dei dati nei fogli di lavoro.
Nota
Il metodo per aggiornare i dati in un foglio di lavoro di Excel usando ADO o DAO non funziona nell'ambiente Visual Basic, Application in Access dopo l'installazione di Office 2003 Service Pack 2 (SP2) o dopo l'installazione dell'aggiornamento per Access 2002 incluso nell'articolo della Microsoft Knowledge Base 904018. Il metodo funziona correttamente nell'ambiente Visual Basic, Application di altre applicazioni di Office, ad esempio Word, Excel e Outlook.
Per maggiori informazioni, consultare l'articolo seguente:
Per altre informazioni sull'uso di ADO per accedere a una cartella di lavoro di Excel, vedere Come eseguire query e aggiornare i dati di Excel usando ADO da ASP.
Usare DDE per trasferire dati in Excel
DDE è un'alternativa all'automazione come mezzo per comunicare con Excel e trasferire dati; tuttavia, con l'avvento di Automazione e COM, DDE non è più il metodo preferito per comunicare con altre applicazioni e deve essere usato solo quando non sono disponibili altre soluzioni.
Per trasferire i dati in Excel tramite DDE, è possibile utilizzare il metodo LinkPoke per inserire dati in un intervallo specifico di celle oppure usare il metodo LinkExecute per inviare i comandi che verranno eseguiti da Excel.
Nell'esempio di codice seguente viene illustrato come stabilire una conversazione DDE con Excel in modo da poter inserire dati nelle celle di un foglio di lavoro ed eseguire comandi. Usando questo esempio, per stabilire correttamente una conversazione DDE in LinkTopic Excel|MyBook.xls, una cartella di lavoro con il nome MyBook.xls deve essere già aperta in un'istanza in esecuzione di Excel.
Nota
Quando si usa Excel 2007, è possibile usare il nuovo formato di file .xlsx per salvare le cartelle di lavoro. Assicurarsi di aggiornare il nome del file nell'esempio di codice seguente. In questo esempio, Text1 rappresenta un controllo Casella di testo in una maschera di 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 usa LinkPoke con Excel, si specifica l'intervallo nella notazione R1C1 (row-column) per LinkItem. Se si selezionano dati in più celle, è possibile usare una stringa in cui le colonne sono delimitate da tabulazioni e le righe sono delimitate da ritorni a capo.
Quando si usa LinkExecute per chiedere a Excel di eseguire un comando, è necessario assegnare a Excel il comando nella sintassi del linguaggio di macro di Excel (XLM). La documentazione di XLM non è inclusa nelle versioni di Excel 97 e successive.
DDE non è una soluzione consigliata per la comunicazione con Excel. Automazione offre la massima flessibilità e offre più accesso alle nuove funzionalità che Excel ha da offrire.
Commenti e suggerimenti
Presto disponibile: Nel corso del 2024 verranno gradualmente disattivati i problemi di GitHub come meccanismo di feedback per il contenuto e ciò verrà sostituito con un nuovo sistema di feedback. Per altre informazioni, vedere https://aka.ms/ContentUserFeedback.
Invia e visualizza il feedback per