Come trasferire dati da un recordset ADO a Excel con l'automazione
Riepilogo
È possibile trasferire il contenuto di un recordset ADO in un foglio di lavoro di Microsoft Excel automatizzando Excel. L'approccio che è possibile usare dipende dalla versione di Excel che si sta automatizzando. Excel 97, Excel 2000 ed Excel 2002 dispongono di un metodo CopyFromRecordset che è possibile utilizzare per trasferire un recordset in un intervallo. È possibile utilizzare CopyFromRecordset in Excel 2000 e 2002 per copiare un oggetto DAO o un recordset ADO. Tuttavia, CopyFromRecordset in Excel 97 supporta solo recordset DAO. Per trasferire un recordset ADO in Excel 97, è possibile creare una matrice dal recordset e quindi popolare un intervallo con il contenuto della matrice.
Questo articolo illustra entrambi gli approcci. Il codice di esempio presentato illustra come trasferire un recordset ADO in Excel 97, Excel 2000, Excel 2002, Excel 2003 o Excel 2007.
Ulteriori informazioni
L'esempio di codice riportato di seguito illustra come copiare un recordset ADO in un foglio di lavoro di Microsoft Excel usando l'automazione da Microsoft Visual Basic. Il codice controlla innanzitutto la versione di Excel. Se viene rilevato Excel 2000 o 2002, viene utilizzato il metodo CopyFromRecordset perché è efficiente e richiede meno codice. Tuttavia, se viene rilevato Excel 97 o versioni precedenti, il recordset viene prima copiato in una matrice utilizzando il metodo GetRows dell'oggetto recordset ADO. La matrice viene quindi trasposta in modo che i record si trovino nella prima dimensione (in righe) e i campi si trovino nella seconda dimensione (in colonne). La matrice viene quindi copiata in un foglio di lavoro di Excel assegnando la matrice a un intervallo di celle. La matrice viene copiata in un unico passaggio anziché scorrere ogni cella del foglio di lavoro.
L'esempio di codice usa il database di esempio Northwind incluso in Microsoft Office. Se è stata selezionata la cartella predefinita durante l'installazione di Microsoft Office, il database si trova in:
\Programmi\Microsoft Office\Office\Samples\Northwind.mdb
Se il database Northwind si trova in una cartella diversa nel computer, è necessario modificare il percorso del database nel codice fornito di seguito.
Se il database Northwind non è installato nel sistema, è possibile usare l'opzione Aggiungi/Rimuovi per l'installazione di Microsoft Office per installare i database di esempio.
Nota Il database Northwind non viene installato quando si installa Microsoft Office 2007. Per ottenere Northwind 2007, visitare il seguente sito Web Microsoft:
Passaggi per creare l'esempio
Avviare Visual Basic e creare un nuovo progetto EXE Standard. Form1 viene creato per impostazione predefinita.
Aggiungere un controllo CommandButton a Form1.
Fare clic su Riferimenti dal menu Progetto. Aggiungere un riferimento alla libreria Microsoft ActiveX Data Objects 2.1.
Incollare il codice seguente nella sezione di codice di Form1:
Private Sub Command1_Click() Dim cnt As New ADODB.Connection Dim rst As New ADODB.Recordset Dim xlApp As Object Dim xlWb As Object Dim xlWs As Object Dim recArray As Variant Dim strDB As String Dim fldCount As Integer Dim recCount As Long Dim iCol As Integer Dim iRow As Integer ' Set the string to the path of your Northwind database strDB ="c:\program files\Microsoft office\office11\samples\Northwind.mdb" ' Open connection to the database cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & strDB & ";" ''When using the Access 2007 Northwind database ''comment the previous code and uncomment the following code. 'cnt.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _ ' "Data Source=" & strDB & ";" ' Open recordset based on Orders table rst.Open "Select * From Orders", cnt ' Create an instance of Excel and add a workbook Set xlApp = CreateObject("Excel.Application") Set xlWb = xlApp.Workbooks.Add Set xlWs = xlWb.Worksheets("Sheet1") ' Display Excel and give user control of Excel's lifetime xlApp.Visible = True xlApp.UserControl = True ' Copy field names to the first row of the worksheet fldCount = rst.Fields.Count For iCol = 1 To fldCount xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name Next ' Check version of Excel If Val(Mid(xlApp.Version, 1, InStr(1, xlApp.Version, ".") - 1)) > 8 Then 'EXCEL 2000,2002,2003, or 2007: Use CopyFromRecordset ' Copy the recordset to the worksheet, starting in cell A2 xlWs.Cells(2, 1).CopyFromRecordset rst 'Note: CopyFromRecordset will fail if the recordset 'contains an OLE object field or array data such 'as hierarchical recordsets Else 'EXCEL 97 or earlier: Use GetRows then copy array to Excel ' Copy recordset to an array recArray = rst.GetRows 'Note: GetRows returns a 0-based array where the first 'dimension contains fields and the second dimension 'contains records. We will transpose this array so that 'the first dimension contains records, allowing the 'data to appears properly when copied to Excel ' Determine number of records recCount = UBound(recArray, 2) + 1 '+ 1 since 0-based array ' Check the array for contents that are not valid when ' copying the array to an Excel worksheet For iCol = 0 To fldCount - 1 For iRow = 0 To recCount - 1 ' Take care of Date fields If IsDate(recArray(iCol, iRow)) Then recArray(iCol, iRow) = Format(recArray(iCol, iRow)) ' Take care of OLE object fields or array fields ElseIf IsArray(recArray(iCol, iRow)) Then recArray(iCol, iRow) = "Array Field" End If Next iRow 'next record Next iCol 'next field ' Transpose and Copy the array to the worksheet, ' starting in cell A2 xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _ TransposeDim(recArray) End If ' Auto-fit the column widths and row heights xlApp.Selection.CurrentRegion.Columns.AutoFit xlApp.Selection.CurrentRegion.Rows.AutoFit ' Close ADO objects rst.Close cnt.Close Set rst = Nothing Set cnt = Nothing ' Release Excel references Set xlWs = Nothing Set xlWb = Nothing Set xlApp = Nothing End Sub Function TransposeDim(v As Variant) As Variant ' Custom Function to Transpose a 0-based array (v) Dim X As Long, Y As Long, Xupper As Long, Yupper As Long Dim tempArray As Variant Xupper = UBound(v, 2) Yupper = UBound(v, 1) ReDim tempArray(Xupper, Yupper) For X = 0 To Xupper For Y = 0 To Yupper tempArray(X, Y) = v(Y, X) Next Y Next X TransposeDim = tempArray End Function
Premere F5 per eseguire il progetto. Viene visualizzato Il modulo 1.
Fare clic sul controllo CommandButton in Form1 e notare che il contenuto della tabella Orders viene visualizzato in una nuova cartella di lavoro in Excel.
Uso di CopyFromRecordset
Per efficienza e prestazioni, CopyFromRecordset è il metodo preferito. Poiché Excel 97 supporta solo recordset DAO con CopyFromRecordset, se si tenta di passare un recordset ADO a CopyFromRecordset con Excel 97, viene visualizzato l'errore seguente:
Errore di runtime 430: la classe non supporta l'automazione o non supporta l'interfaccia prevista. Nell'esempio di codice è possibile evitare questo errore controllando la versione di Excel in modo da non usare CopyFromRecordset per la versione 97.
Nota Quando si usa CopyFromRecordset, è necessario tenere presente che il recordset ADO o DAO utilizzato non può contenere campi oggetto OLE o dati di matrice, ad esempio recordset gerarchici. Se si includono campi di entrambi i tipi in un recordset, il metodo CopyFromRecordset avrà esito negativo con l'errore seguente:
Errore di run-time -2147467259: metodo CopyFromRecordset dell'oggetto Range non riuscito.
Uso di GetRows
Se viene rilevato Excel 97, utilizzare il metodo GetRows del recordset ADO per copiare il recordset in una matrice. Se si assegna la matrice restituita da GetRows a un intervallo di celle nel foglio di lavoro, i dati passano attraverso le colonne anziché verso il basso le righe. Ad esempio, se il recordset include due campi e 10 righe, la matrice viene visualizzata come due righe e 10 colonne. È quindi necessario trasporre la matrice usando la funzione TransposeDim() prima di assegnare la matrice all'intervallo di celle. Quando si assegna una matrice a un intervallo di celle, esistono alcune limitazioni di cui tenere conto:
Quando si assegna una matrice a un oggetto Range di Excel, si applicano le limitazioni seguenti:
La matrice non può contenere campi oggetto OLE o dati di matrice, ad esempio recordset gerarchici. Si noti che l'esempio di codice controlla questa condizione e visualizza "Campo matrice" in modo che l'utente venga informato che il campo non può essere visualizzato in Excel.
La matrice non può contenere campi Date con una data precedente all'anno 1900. Per un collegamento all'articolo della Microsoft Knowledge Base, vedere la sezione "Riferimenti". Si noti che l'esempio di codice formatta i campi Date come stringhe varianti per evitare questo potenziale problema.
Si noti l'uso della funzione TransposeDim() per trasporre la matrice prima che la matrice venga copiata nel foglio di lavoro di Excel. Anziché creare una funzione personalizzata per trasporre la matrice, è possibile usare la funzione Transpose di Excel modificando il codice di esempio per assegnare la matrice alle celle, come illustrato di seguito:
xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
xlApp.WorksheetFunction.Transpose(recArray)
Se si decide di utilizzare il metodo Transpose di Excel anziché la funzione TransposeDim() per trasporre la matrice, è necessario conoscere le limitazioni seguenti con il metodo Transpose:
- La matrice non può contenere un elemento maggiore di 255 caratteri.
- La matrice non può contenere valori Null.
- Il numero di elementi nella matrice non può superare 5461.
Se le limitazioni precedenti non vengono prese in considerazione quando si copia una matrice in un foglio di lavoro di Excel, è possibile che si verifichi uno degli errori di runtime seguenti:
Errore di run-time 13: Tipo non corrispondente
Errore di run-time 5: procedura non valida
call o argument Errore di runtime 1004: errore definito dall'applicazione o definito dall'oggetto
Riferimenti
Per altre informazioni sulle limitazioni relative al passaggio di matrici a varie versioni di Excel, fare clic sul numero dell'articolo seguente per visualizzare l'articolo della Microsoft Knowledge Base:
177991 XL: Limitazioni del passaggio di matrici a Excel tramite automazione
247412 INFO: Metodi per il trasferimento di dati in Excel da Visual Basic