Otomasyon ile ADO Kayıt Kümesinden Excel'e veri aktarma

Özet

Excel'i otomatikleştirerek bir ADO kayıt kümesinin içeriğini Microsoft Excel çalışma sayfasına aktarabilirsiniz. Kullanabileceğiniz yaklaşım, otomatikleştirdiğiniz Excel sürümüne bağlıdır. Excel 97, Excel 2000 ve Excel 2002'de kayıt kümesini bir aralığa aktarmak için kullanabileceğiniz bir CopyFromRecordset yöntemi vardır. Excel 2000 ve 2002'de CopyFromRecordset, DAO veya ADO kayıt kümesini kopyalamak için kullanılabilir. Ancak, Excel 97'deki CopyFromRecordset yalnızca DAO kayıt kümelerini destekler. Bir ADO kayıt kümesini Excel 97'ye aktarmak için, kayıt kümesinden bir dizi oluşturabilir ve ardından bir aralığı bu dizinin içeriğiyle doldurabilirsiniz.

Bu makalede her iki yaklaşım da ele alınmaktadır. Sunulan örnek kod, bir ADO kayıt kümesini Excel 97, Excel 2000, Excel 2002, Excel 2003 veya Excel 2007'ye nasıl aktarabileceğinizi gösterir.

Daha Fazla Bilgi

Aşağıda sağlanan kod örneği, Microsoft Visual Basic'ten otomasyon kullanarak bir ADO kayıt kümesinin Microsoft Excel çalışma sayfasına nasıl kopyalandığını gösterir. Kod önce Excel sürümünü denetler. Excel 2000 veya 2002 algılanırsa, verimli olduğundan ve daha az kod gerektirdiğinden CopyFromRecordset yöntemi kullanılır. Ancak, Excel 97 veya önceki bir sürüm algılanırsa, kayıt kümesi ilk olarak ADO kayıt kümesi nesnesinin GetRows yöntemi kullanılarak bir diziye kopyalanır. Dizi daha sonra, kayıtların ilk boyutta (satırlarda) ve alanların ikinci boyutta (sütunlarda) olması için dönüştürülür. Ardından, dizi bir hücre aralığına atanarak bir Excel çalışma sayfasına kopyalanır. (Dizi, çalışma sayfasındaki her hücrede döngü yerine bir adımda kopyalanır.)

Kod örneği, Microsoft Office'e dahil olan Northwind örnek veritabanını kullanır. Microsoft Office'i yüklerken varsayılan klasörü seçtiyseniz veritabanı şu konumda bulunur:

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

Northwind veritabanı bilgisayarınızda farklı bir klasörde bulunuyorsa, veritabanının yolunu aşağıda sağlanan kodda düzenlemeniz gerekir.

Sisteminizde Northwind veritabanı yüklü değilse, örnek veritabanlarını yüklemek için Microsoft Office kurulumu için Ekle/Kaldır seçeneğini kullanabilirsiniz.

Not 2007 Microsoft Office'i yüklediğinizde Northwind veritabanı yüklenmez. Northwind 2007'yi edinmek için aşağıdaki Microsoft Web sitesini ziyaret edin:

Office şablonları & temaları

Örnek Oluşturma Adımları

1. Visual Basic'i başlatın ve yeni bir Standart EXE projesi oluşturun. Form1 varsayılan olarak oluşturulur.

2. Form1'e CommandButton ekleyin.

3. Proje menüsünden Başvurular'a tıklayın. Microsoft ActiveX Veri Nesneleri 2.1 Kitaplığı'na başvuru ekleyin.

4. Aşağıdaki kodu Form1'in kod bölümüne yapıştırın:

   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. Projeyi çalıştırmak için F5 tuşuna basın. Form1 görüntülenir.

6. Form1'de CommandButton'a tıklayın ve Orders tablosunun içeriğinin Excel'deki yeni bir çalışma kitabında görüntülendiğini unutmayın.

CopyFromRecordset kullanma

Verimlilik ve performans için tercih edilen yöntem CopyFromRecordset'tir. Excel 97 yalnızca CopyFromRecordset içeren DAO kayıt kümelerini desteklediğinden, Bir ADO kayıt kümesini Excel 97 ile CopyFromRecordset'e geçirmeye çalışırsanız, aşağıdaki hatayı alırsınız:

Çalışma zamanı hatası 430: Sınıf Otomasyonu desteklemiyor veya beklenen arabirimi desteklemiyor. Kod örneğinde, 97 sürümü için CopyFromRecordset kullanmamak üzere Excel'in sürümünü denetleyerek bu hatayı önleyebilirsiniz.

Not CopyFromRecordset kullanırken, kullandığınız ADO veya DAO kayıt kümesinin OLE nesne alanlarını veya hiyerarşik kayıt kümeleri gibi dizi verilerini içeremeyeceğini bilmelisiniz. Kayıt kümesine her iki türde de alan eklerseniz CopyFromRecordset yöntemi aşağıdaki hatayla başarısız olur:

Çalışma zamanı hatası -2147467259: Nesne Aralığı'nın CopyFromRecordset yöntemi başarısız oldu.

GetRows kullanma

Excel 97 algılanırsa, kayıt kümesini bir diziye kopyalamak için ADO kayıt kümesinin GetRows yöntemini kullanın. GetRows tarafından döndürülen diziyi çalışma sayfasındaki bir hücre aralığına atarsanız, veriler satırlar yerine sütunlar arasında gider. Örneğin, kayıt kümesinin iki alanı ve 10 satırı varsa, dizi iki satır ve 10 sütun olarak görünür. Bu nedenle, diziyi hücre aralığına atamadan önce TransposeDim() işlevinizi kullanarak diziyi dönüştürmeniz gerekir. Bir diziyi bir hücre aralığına atarken dikkat edilmesi gereken bazı sınırlamalar vardır:

Excel Range nesnesine dizi atarken aşağıdaki sınırlamalar geçerlidir:

• Dizi, OLE nesne alanlarını veya hiyerarşik kayıt kümeleri gibi dizi verilerini içeremez. Kod örneğinin bu koşulu denetlediğini ve kullanıcının alanın Excel'de görüntülenemeyeceğini fark edebilmesi için "Dizi Alanı" görüntülediğine dikkat edin.

• Dizi, 1900 yılından önceki bir tarihi olan Tarih alanlarını içeremez. (Microsoft Bilgi Bankası makale bağlantısı için "Başvurular" bölümüne bakın.) Kod örneğinin bu olası sorunu önlemek için Tarih alanlarını değişken dizeler olarak biçimlendirdiğini unutmayın.

Dizi Excel çalışma sayfasına kopyalanmadan önce diziyi değiştirmek için TransposeDim() işlevinin kullanıldığına dikkat edin. Diziyi değiştirmek için kendi işlevinizi oluşturmak yerine, aşağıda gösterildiği gibi, diziyi hücrelere atamak için örnek kodu değiştirerek Excel'in İşlemleri Değiştir işlevini kullanabilirsiniz:

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

Diziyi değiştirmek için TransposeDim() işlevi yerine Excel'in Transpose yöntemini kullanmaya karar verirseniz, Dönüştürme yöntemiyle ilgili aşağıdaki sınırlamaları bilmeniz gerekir:

• Dizi 255 karakterden büyük bir öğe içeremez.
• Dizi Null değerler içeremez.
• Dizideki öğe sayısı 5461'i aşamaz.

Bir diziyi Excel çalışma sayfasına kopyalarken yukarıdaki sınırlamalar dikkate alınmazsa, aşağıdaki çalışma zamanı hatalarından biri oluşabilir:

Çalışma Zamanı Hatası 13: Tür Uyuşmazlığı

Çalışma Zamanı Hatası 5: Geçersiz yordam

çağrı veya bağımsız değişken Çalışma Zamanı Hatası 1004: Uygulama tanımlı veya nesne tanımlı hata

Başvurular

Dizileri Excel'in çeşitli sürümlerine geçirme sınırlamaları hakkında ek bilgi için, Microsoft Bilgi Bankası'ndaki makaleyi görüntülemek üzere aşağıdaki makale numarasını tıklatın:

177991 XL: Otomasyon Kullanarak Dizileri Excel'e Geçirme sınırlamaları

247412 BİlGİ: Visual Basic'ten Excel'e Veri Aktarma Yöntemleri