Tietojen siirtäminen ADO-tietuejoukosta Exceliin automaation avulla

Yhteenveto

Voit siirtää ADO-tietuejoukon sisällön Microsoft Excel -laskentataulukkoon automatisoimalla Excelin. Käytettävissä oleva lähestymistapa määräytyy automatisoitavan Excel-version mukaan. Excel 97:ssä, Excel 2000:ssa ja Excel 2002:ssa on CopyFromRecordset-menetelmä, jonka avulla voit siirtää tietuejoukon alueelle. CopyFromRecordset Excel 2000:ssa ja 2002:ssa voidaan kopioida joko DAO- tai ADO-tietuejoukko. Excel 97:n CopyFromRecordset tukee kuitenkin vain DAO-tietuejoukkoja. Jos haluat siirtää ADO-tietuejoukon Excel 97:ään, voit luoda matriisin tietuejoukosta ja täyttää sitten alueen kyseisen matriisin sisällöllä.

Tässä artikkelissa käsitellään molempia lähestymistapoja. Esitelty mallikoodi havainnollistaa, miten voit siirtää ADO-tietuejoukon Excel 97:ään, Excel 2000:een, Excel 2002:een, Excel 2003:een tai Excel 2007:ään.

Lisätietoja

Alla olevassa koodimallissa näytetään, miten voit kopioida ADO-tietuejoukon Microsoft Excel -laskentataulukkoon Microsoft Visual Basicin automaation avulla. Koodi tarkistaa ensin Excelin version. Jos Excel 2000 tai 2002 havaitaan, CopyFromRecordset-menetelmää käytetään, koska se on tehokas ja vaatii vähemmän koodia. Jos kuitenkin havaitaan Excel 97 tai aiempi, tietuejoukko kopioidaan ensin matriisiin käyttämällä ADO-tietuejoukko-objektin GetRows-menetelmää. Matriisi transponoituu siten, että tietueet ovat ensimmäisessä ulottuvuudessa (riveillä) ja kentät toisessa ulottuvuudessa (sarakkeissa). Tämän jälkeen matriisi kopioidaan Excel-laskentataulukkoon määrittämällä matriisi solualueelle. (Matriisi kopioidaan yhdessä vaiheessa sen sijaan, että se kulkisi laskentataulukon kunkin solun läpi.)

Koodiesimerkki käyttää Microsoft Officeen sisältyvää Northwind-mallitietokantaa. Jos valitsit oletuskansion, kun asensit Microsoft Officen, tietokanta sijaitsee:

\Program Files\Microsoft Office\Office\Samples\Northwind.mdb

Jos Northwind-tietokanta sijaitsee tietokoneen eri kansiossa, sinun on muokattava tietokannan polkua alla olevassa koodissa.

Jos järjestelmään ei ole asennettu Northwind-tietokantaa, voit asentaa mallitietokannat Microsoft Officen asennusohjelman Lisää tai poista -toiminnolla.

Huomautus Northwind-tietokantaa ei asenneta, kun asennat 2007 Microsoft Officen. Hanki Northwind 2007 käymällä seuraavassa Microsoftin verkkosivustossa:

Office-mallien teemat &

Mallin luontivaiheet

1. Käynnistä Visual Basic ja luo uusi Standard EXE -projekti. Form1 luodaan oletusarvoisesti.

2. Lisää Komentopainike Form1:een.

3. Valitse Projekti-valikosta Viittaukset. Lisää viittaus Microsoft ActiveX Data Objects 2.1 -kirjastoon.

4. Liitä seuraava koodi Form1:n koodiosioon:

   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
   
   

5. Suorita projekti painamalla F5-näppäintä. Form1 tulee näkyviin.

6. Napsauta Form1:n CommandButton-painiketta ja huomaa, että Orders-taulukon sisältö näkyy uudessa työkirjassa Excelissä.

KopioiTietuejoukosta -toiminnon käyttäminen

Tehokkuuden ja suorituskyvyn vuoksi ensisijainen menetelmä on CopyFromRecordset. Koska Excel 97 tukee vain DAO-tietuejoukkoja CopyFromRecordset-toiminnolla ja jos yrität välittää ADO-tietuejoukon CopyFromRecordset-objektiin Excel 97:ssä, näyttöön tulee seuraava virhe:

Suorituksenaikainen virhe 430: Luokka ei tue automaatiota tai odotettua liittymää. Voit välttää tämän virheen koodiesimerkissä tarkistamalla Excelin version siten, että et käytä 97-versiossa KopioTietuejoukko-toimintoa.

Huomautus Kun käytät CopyFromRecordset-funktiota, ota huomioon, että käyttämäsi ADO- tai DAO-tietuejoukko ei voi sisältää OLE-objektikenttiä tai matriisitietoja, kuten hierarkkisia tietuejoukkoja. Jos sisällytät tietuejoukkoon jommankumman tyypin kenttiä, CopyFromRecordset-menetelmä epäonnistuu. Virhe:

Suorituksenaikainen virhe -2147467259: Objektialueen CopyFromRecordset-menetelmä epäonnistui.

GetRowsin käyttäminen

Jos Excel 97 havaitaan, kopioi tietuejoukko matriisiin käyttämällä ADO-tietuejoukon GetRows-menetelmää. Jos määrität GetRowsin palauttaman matriisin laskentataulukon solualueelle, tiedot ulottuvat sarakkeiden yli rivien sijaan. Jos tietuejoukossa on esimerkiksi kaksi kenttää ja 10 riviä, matriisi näkyy kahtena rivinä ja 10 sarakkeena. Siksi sinun on transponoitava matriisi TransposeDim()-funktiolla, ennen kuin määrität matriisin solualueelle. Määritettäessä matriisia solualueelle on joitakin rajoituksia, jotka on syytä ottaa huomioon:

Seuraavat rajoitukset koskevat matriisin määrittämistä Excel Range -objektille:

• Matriisi ei voi sisältää OLE-objektikenttiä tai matriisitietoja, kuten hierarkkisia tietuejoukkoja. Huomaa, että koodimalli tarkistaa tämän ehdon ja näyttää "Matriisikentän", jotta käyttäjälle ilmoitetaan, että kenttää ei voi näyttää Excelissä.

• Matriisi ei voi sisältää Päivämäärä-kenttiä, joiden päivämäärä on ennen vuotta 1900. (Katso Microsoft Knowledge Base -artikkelilinkin "Viittaukset"-osiosta.) Huomaa, että koodiesimerkki muotoilee Päivämäärä-kentät varianttimerkkijonoksi tämän mahdollisen ongelman välttämiseksi.

Huomaa, että TransposeDim()-funktiota käytetään matriisin transponoimiseen, ennen kuin matriisi kopioidaan Excel-laskentataulukkoon. Sen sijaan, että loisit oman funktion matriisin transponointiin, voit käyttää Excelin Transponointi-funktiota muokkaamalla mallikoodia ja määrittämällä matriisin soluihin alla kuvatulla tavalla:

   xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
      xlApp.WorksheetFunction.Transpose(recArray)

Jos päätät transponoida matriisin TransposeDim()-funktion sijaan Excelin Transponoi-menetelmällä, ota huomioon seuraavat Transpose-menetelmän rajoitukset:

• Matriisi ei voi sisältää elementtiä, jonka enimmäispituutta on 255 merkkiä.
• Matriisi ei voi sisältää tyhjäarvoja.
• Matriisin elementtien määrä voi olla enintään 5 461.

Jos edellä olevia rajoituksia ei oteta huomioon kopioitaessa matriisia Excel-laskentataulukkoon, jokin seuraavista suoritusaikavirheistä voi ilmetä:

Suorituksenaikainen virhe 13: Tyyppiristiriita

Suorituksenaikainen virhe 5: Virheellinen toimintosarja

kutsu tai argumentti Suorituksenaikainen virhe 1004: Sovelluksen määrittämä tai objektin määrittämä virhe

Lisätietoja

Saat lisätietoja rajoituksista, jotka koskevat matriisien välittämistä Excelin eri versioihin, napsauttamalla seuraavaa artikkelin numeroa, jotta voit tarkastella artikkelia Microsoft Knowledge Base -joukossa:

177991 XL: Matriisien exceliin siirtämisen rajoitukset automaation avulla

247412 TIEDOT: Menetelmät tietojen siirtämiseksi Exceliin Visual Basicista