Come trasferire dati da un recordset ADO a Excel con l'automazione

Articolo
06/20/2023
Si applica a:

Excel 2007, Excel 2003, Excel 2002, Excel 2000, Excel 97

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:

Temi dei modelli di & Office

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