Tietojen siirtäminen ADO-tietuejoukosta Exceliin automaation avulla

Artikkeleiden käännökset Artikkeleiden käännökset
Artikkelin tunnus: 246335 - Näytä tuotteet, joita tämä artikkeli koskee.
Laajenna kaikki | Kutista kaikki

Tällä sivulla

Yhteenveto

ADO-tietuejoukon sisältö voidaan siirtää Microsoft Excel -laskentataulukkoon automatisoimalla Excel. Käytettävä lähestymistapa määrittyy automatisoitavan Excel-version mukaan. Excel 97, Excel 2000 ja Excel 2002 sisältävät CopyFromRecordset-menetelmän, jonka avulla voidaan siirtää tietuejoukko alueeseen. Excel 2000:n ja Excel 2002:n CopyFromRecordset-menetelmän avulla voidaan kopioida DAO- tai ADO-tietuejoukko. Excel 97:n CopyFromRecordset-menetelmä sen sijaan tukee vain DAO-tietuejoukkoja. Jos haluat siirtää ADO-tietuejoukon Excel 97:ään, voit luoda tietuejoukosta matriisin ja lisätä sitten matriisin sisällön alueeseen.

Tässä artikkelissa käsitellään molempia lähestymistapoja. Annetussa mallikoodissa esitellään, miten ADO-tietuejoukko voidaan siirtää Excel 97:ään, Excel 2000:een, Excel 2002:een, Excel 2003:een tai Excel 2007:ään.

Enemmän tietoa

Jäljempänä annetussa mallikoodissa esitellään, miten ADO-tietuejoukko kopioidaan Microsoft Excel -laskentataulukkoon Microsoft Visual Basicin automatisoinnin avulla. Koodi tarkistaa ensin Excelin version. Jos se havaitsee Excel 2000:n tai Excel 2002:n, käytetään CopyFromRecordset-menetelmää, koska se on tehokas ja vaatii muita tapoja vähemmän koodia. Jos havaitaan Excel 97 tai vanhempi versio, tietuejoukko kopioidaan ensin matriisiin ADO-tietuejoukko-objektin GetRows-menetelmän avulla. Matriisi transponoidaan sitten niin, 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ä se solualueelle. (Matriisi kopioidaan yhdessä vaiheessa sen sijaan, että laskentataulukon kukin solu käytäisiin läpi silmukan avulla.)

Mallikoodissa käytetään Microsoft Officeen sisältyvää Northwind-mallitietokantaa. Jos valitsit oletuskansion asentaessasi Microsoft Officen, tietokannan sijainti on seuraava:

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

Jos Northwind-tietokanta sijaitsee tietokoneen eri kansiossa, sinun on muokattava sen polkua seuraavassa annetussa koodissa.

Jos järjestelmässä ei ole asennettuna Northwind-tietokantaa, voit asentaa mallitietokannat Microsoft Officen asennusohjelman Lisää tai poista -toiminnon avulla.

Huomautus Northwind-tietokantaa ei asenneta, kun asennat 2007 Microsoft Officen. Voit hankkia Northwind 2007:n seuraavasta Microsoftin verkkosivustosta:
http://office.microsoft.com/en-us/templates/TC012289971033.aspx

Mallin luomisen vaiheet

  1. Käynnistä Visual Basic ja luo uusi normaali EXE-projekti. Lomake1 luodaan oletusarvon mukaan.
  2. Lisää Lomake1-lomakkeeseen Komentopainike-ohjausobjekti.
  3. Valitse Projekti-valikosta Viittaukset. Lisää viittaus Microsoft ActiveX Data Objects 2.1 Library -kirjastoon.
  4. Liitä seuraava koodi Lomake1-lomakkeen koodiosaan:
    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
        
        ' Määritä merkkijonoksi Northwind-tietokannan polku
        strDB ="c:\program files\Microsoft office\office11\samples\Northwind.mdb"
      
        ' Avaa yhteys tietokantaan
        cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & strDB & ";"
        
        ' Kun käytössä on Access 2007 Northwind -tietokanta,
        ' merkitse edellinen koodi kommentiksi ja poista seuraavan koodin kommenttimerkintä.
        'cnt.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        '    "Data Source=" & strDB & ";"
            
        ' Avaa Orders-taulukkoon perustuva tietuejoukko
        rst.Open "Select * From Orders", cnt
        
        ' Luo Excel-esiintymä ja lisää työkirja
        Set xlApp = CreateObject("Excel.Application")
        Set xlWb = xlApp.Workbooks.Add
        Set xlWs = xlWb.Worksheets("Sheet1")
      
        ' Näytä Excel ja anna käyttäjälle Excelin toiminta-ajan hallinta
        xlApp.Visible = True
        xlApp.UserControl = True
        
        ' Kopioi kenttien nimet laskentataulukon ensimmäiselle riville
        fldCount = rst.Fields.Count
        For iCol = 1 To fldCount
            xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
        Next
            
        ' Tarkista Excelin versio
        If Val(Mid(xlApp.Version, 1, InStr(1, xlApp.Version, ".") - 1)) > 8 Then
            ' Excel 2000, 2002, 2003 tai 2007: Use CopyFromRecordset
             
            ' Kopioi tietuejoukko laskentataulukkoon alkaen solusta A2
            xlWs.Cells(2, 1).CopyFromRecordset rst
            ' Huomautus: CopyFromRecordset epäonnistuu, jos tietuejoukko
            ' sisältää OLE-objektikentän tai matriisitietoja, kuten
            ' hierarkkisia tietuejoukkoja
            
        Else
            ' Excel 97 ja aiemmat: Käytä GetRows-funktiota ja kopioi matriisi sitten Exceliin
        
            ' Kopioi tietuejoukko matriisiin
            recArray = rst.GetRows
            ' Huomautus: GetRows palauttaa 0-pohjaisen matriisin, jossa
            ' ensimmäinen ulottuvuus sisältää kentät ja toinen
            ' tietueet. Tämä matriisi transponoidaan niin, että ensimmäinen
            ' ulottuvuus sisältää tietueet, jolloin tiedot näkyvät
            ' oikein Exceliin kopioituna
            
            ' Selvitä tietueiden määrä
    
            recCount = UBound(recArray, 2) + 1 '+ 1, koska kyseessä on 0-pohjainen matriisi
            
    
            ' Tarkista, sisältääkö matriisi epäkelpoa sisältöä, kun
            ' matriisi kopioidaan Excel-laskentataulukkoon
            For iCol = 0 To fldCount - 1
                For iRow = 0 To recCount - 1
                    ' Käsittele Date-kentät
                    If IsDate(recArray(iCol, iRow)) Then
                        recArray(iCol, iRow) = Format(recArray(iCol, iRow))
                    ' Käsittele OLE-objektikentät tai matriisikentät
                    ElseIf IsArray(recArray(iCol, iRow)) Then
                        recArray(iCol, iRow) = "Array Field"
                    End If
                Next iRow 'seuraava tietue
            Next iCol 'seuraava kenttä
                
            ' Transponoi ja kopioi matriisi laskentataulukkoon
            ' aloittaen solusta A2
            xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
                TransposeDim(recArray)
        End If
    
        ' Sovita sarakkeiden leveydet ja rivien korkeudet automaattisesti
        xlApp.Selection.CurrentRegion.Columns.AutoFit
        xlApp.Selection.CurrentRegion.Rows.AutoFit
    
        ' Sulje ADO-objektit
        rst.Close
        cnt.Close
        Set rst = Nothing
        Set cnt = Nothing
        
        ' Vapauta Excel-viittaukset
        Set xlWs = Nothing
        Set xlWb = Nothing
    
        Set xlApp = Nothing
    
    End Sub
    
    
    Function TransposeDim(v As Variant) As Variant
    ' Mukautettu funktio, jolla 0-pohjainen matriisi transponoidaan (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ä. Lomake1 tulee näyttöön.
  6. Napsauta Lomake1-lomakkeen Komentopainiketta ja huomaa, että Orders-taulukon sisältö näkyy Excelissä uudessa työkirjassa.
CopyFromRecordset-menetelmän käyttäminen

CopyFromRecordset on suositeltu menetelmä sen tehokkuuden ja suorituskyvyn vuoksi. Koska Excel 97 tukee vain DAO-tietuejoukkoja CopyFromRecordset-menetelmän yhteydessä, jos yrität välittää ADO-tietuejoukon CopyFromRecordset-menetelmälle Excel 97:ssä, näyttöön tulee seuraavankaltainen virhe:
Suorituksenaikainen virhe 430:
Luokka ei tue automaatiota tai odotettua liittymää.
Voit välttää tämän virheen mallikoodissa tarkistamalla Excelin version, jotta Excel 97 -version CopyFromRecordset-menetelmää ei käytetä.

Huomautus Kun käytät CopyFromRecordset-menetelmää, huomaa, että käyttämäsi ADO- tai DAO-tietuejoukko ei voi sisältää OLE-objektikenttiä tai matriisitietoja, kuten hierarkkisia tietuejoukkoja. Jos näitä kenttätyyppejä on tietuejoukossa, CopyFromRecordset-menetelmä epäonnistuu ja tuo näyttöön seuraavankaltaisen virheen:
Suorituksenaikainen virhe -2147467259:
Objektin Range menetelmä CopyFromRecordset epäonnistui.
GetRows-menetelmän käyttäminen

Jos Excel 97 havaitaan, käytä ADO-tietuejoukon GetRows-menetelmää tietuejoukon kopioimiseen matriisiin. Jos määrität GetRows-menetelmän palauttaman matriisin laskentataulukon soluihin, tiedot lisätään sarakkeisiin rivien sijaan. Jos tietuejoukossa on esimerkiksi kaksi kenttää ja kymmenen riviä, matriisi näkyy kahtena rivinä ja kymmenenä sarakkeena. Tämän vuoksi matriisi on transponoitava TransposeDim()-funktion avulla ennen sen määrittämistä solualueeseen. Kun matriisi määritetään solualueeseen, on pidettävä mielessä joitakin rajoituksia.

Seuraavat rajoitukset koskevat matriisin määrittämistä Excelin Alue-objektiin:
  • Matriisi ei saa sisältää OLE-objektikenttiä tai matriisitietoja, kuten hierarkkisia tietuejoukkoja. Huomaa, että mallikoodi tarkistaa tämän ja tuo näyttöön Array Field -tekstin, jotta käyttäjä tietää, ettei kenttää voi näyttää Excelissä.

  • Matriisi ei saa sisältää Päivämäärä-kenttiä, joiden päivämäärä on vuotta 1900 aiempi. (Katso Microsoft Knowledge Base -tietokannan artikkelin linkki lisätietolähteiden osasta.) Huomaa, että mallikoodi muotoilee Päivämäärä-kentät muuttujamerkkijonoiksi, jotta tämä mahdollinen ongelma välitetään.
Huomaa, että TransposeDim()-funktiota käytetään matriisin transponoimiseen, ennen kuin matriisi kopioidaan Excel-laskentataulukkoon. Sen sijaan, että loisit oman funktion matriisin transponointia varten, voit käyttää Excelin Transponoi-funktiota muokkaamalla mallikoodin määrittämään matriisin soluihin seuraavasti:
   xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
      xlApp.WorksheetFunction.Transpose(recArray)
				
Jos päätät käyttää Excelin Transponoi-menetelmää matriisin transponoimiseen TransposeDim()-funktion sijaan, pidä mielessä seuraavat Transponoi-menetelmän rajoitukset:
  • Matriisi ei saa sisältää elementtiä, joka on suurempi kuin 255 merkkiä.
  • Matriisi ei saa sisältää Null-arvoja.
  • Matriisissa saa olla enintään 5 461 elementtiä.
Jos edellä kuvattuja rajoituksia ei oteta huomioon, kun matriisi kopioidaan Excel-laskentataulukkoon, saattaa ilmetä jokin seuraavista suorituksenaikaisista virheistä:
Suorituksenaikainen virhe 13: Tyypit eivät ole yhteensopivia
Suorituksenaikainen virhe 5: Virheellinen proseduurikutsu tai argumentti
Suorituksenaikainen virhe 1004: Sovelluksen tai objektin määrittämä virhe

Suositukset

Lisätietoja rajoituksista välitettäessä matriiseja Excelin eri versioihin saat napsauttamalla seuraavaa artikkelin numeroa, jolloin pääset lukemaan artikkelin Microsoft Knowledge Base -tietokannassa:
177991 XL: Rajoitukset välitettäessä matriiseja Exceliin automaation avulla (tämä artikkeli saattaa olla englanninkielinen)
Saat lisätietoja napsauttamalla seuraavia artikkeleiden numeroita, jolloin pääset lukemaan artikkelit Microsoft Knowledge Base -tietokannassa:
146406 XL: Taulukon noutaminen Accessista Exceliin DAO-objektin avulla (tämä artikkeli saattaa olla englanninkielinen)
215965 XL2000: Ennen vuotta 1900 olevien päivämäärien sijaan näytetään 00:00:00 (tämä artikkeli saattaa olla englanninkielinen)
243394 DAO-tietuejoukon kopioiminen Exceliin automaation avulla käyttämällä MFC:tä (tämä artikkeli saattaa olla englanninkielinen)
247412 TIETOJA: Tietojen Visual Basicista Exceliin siirtämisen tapoja (tämä artikkeli saattaa olla englanninkielinen)

Ominaisuudet

Artikkelin tunnus: 246335 - Viimeisin tarkistus: 23. marraskuuta 2007 - Versio: 5.0
Artikkelin tiedot koskevat seuraavia tuotteita:
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Visual Basic 5.0 Professional Edition
  • Microsoft Visual Basic 6.0 Professional Edition
  • Microsoft Visual Basic 5.0 Enterprise Edition
  • Microsoft Visual Basic 6.0 Enterprise Edition
  • Microsoft ActiveX Data Objects 2.0
  • Microsoft ActiveX Data Objects 2.1
  • Microsoft ActiveX Data Objects 2.5
Hakusanat: 
kbhowto kbautomation kbexpertiseinter KB246335

Anna palautetta

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com