Visual Basic'ten Excel'e veri aktarma yöntemleri
Özet
Bu makalede, Microsoft Visual Basic uygulamanızdan Microsoft Excel'e veri aktarmak için birçok yöntem ele alınmaktadır. Bu makalede, sizin için en uygun çözümü seçebilmeniz için her yöntemin avantajları ve dezavantajları da sunulur.
Ek Bilgi
Excel çalışma kitabına veri aktarmak için en yaygın olarak kullanılan yaklaşım Otomasyon'dur. Otomasyon, çalışma kitabındaki verilerinizin konumunu belirtmenin yanı sıra çalışma kitabını biçimlendirme ve çalışma zamanında çeşitli ayarlar yapma konusunda size en büyük esnekliği sağlar. Otomasyon ile verilerinizi aktarmak için çeşitli yaklaşımlar kullanabilirsiniz:
- Veri hücresine göre aktarma
- Dizideki verileri bir hücre aralığına aktarma
- CopyFromRecordset yöntemini kullanarak ADO kayıt kümesindeki verileri bir hücre aralığına aktarma
- ODBC veya OLEDB veri kaynağındaki sorgunun sonucunu içeren bir Excel çalışma sayfasında QueryTable oluşturma
- Panoya veri aktarma ve pano içeriğini excel çalışma sayfasına yapıştırma
Verileri Excel'e aktarmak için otomasyon gerektirmeyen yöntemler de vardır. Bir uygulama sunucusu tarafı çalıştırıyorsanız, bu, verileri istemcilerinizin dışında işlemenin büyük bir kısmını almak için iyi bir yaklaşım olabilir. Otomasyon olmadan verilerinizi aktarmak için aşağıdaki yöntemler kullanılabilir:
- Verilerinizi Excel'in daha sonra çalışma sayfasındaki hücrelere ayrıştırabileceği bir sekme veya virgülle ayrılmış metin dosyasına aktarma
- ADO kullanarak verilerinizi çalışma sayfasına aktarma
- Dinamik Veri Değişimi (DDE) kullanarak Excel'e veri aktarma
Aşağıdaki bölümlerde bu çözümlerin her biri hakkında daha fazla ayrıntı sağlanır.
Not Microsoft Office Excel 2007 kullandığınızda, çalışma kitaplarını kaydederken yeni Excel 2007 Çalışma Kitabı (*.xlsx) dosya biçimini kullanabilirsiniz. Bunu yapmak için aşağıdaki kod örneklerinde aşağıdaki kod satırını bulun:
oBook.SaveAs "C:\Book1.xls"
Bu kodu aşağıdaki kod satırıyla değiştirin:
oBook.SaveAs "C:\Book1.xlsx"
Ayrıca, Northwind veritabanı varsayılan olarak Office 2007'ye dahil değildir. Ancak Northwind veritabanını Microsoft Office Online'dan indirebilirsiniz.
Veri hücresini hücreye aktarmak için Otomasyon kullanma
Otomasyon ile bir çalışma sayfasına her seferinde bir hücre veri aktarabilirsiniz:
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
'Start a new workbook in Excel
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
'Add data to cells of the first worksheet in the new workbook
Set oSheet = oBook.Worksheets(1)
oSheet.Range("A1").Value = "Last Name"
oSheet.Range("B1").Value = "First Name"
oSheet.Range("A1:B1").Font.Bold = True
oSheet.Range("A2").Value = "Doe"
oSheet.Range("B2").Value = "John"
'Save the Workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit
Veri miktarının küçük olması durumunda veri hücresinin hücreye aktarılması son derece kabul edilebilir bir yaklaşım olabilir. Çalışma kitabının herhangi bir yerine veri yerleştirme esnekliğine sahipsiniz ve çalışma zamanında hücreleri koşullu olarak biçimlendirebilirsiniz. Ancak, Excel çalışma kitabına aktarabileceğiniz büyük miktarda veriniz varsa bu yaklaşım önerilmez. Çalışma zamanında edindiğiniz her Range nesnesi, verileri bu şekilde aktarmanın yavaş olabilmesi için bir arabirim isteğiyle sonuçlanabilir. Ayrıca, Microsoft Windows 95 ve Windows 98'in arabirim isteklerinde 64K sınırlaması vardır. Arabirim isteklerinde bu 64k sınırına ulaşırsanız veya sınırı aşarsanız Otomasyon sunucusu (Excel) yanıt vermeyi durdurabilir veya yetersiz belleği gösteren hatalar alabilirsiniz.
Bir kez daha, veri hücresinin hücreye göre aktarılması yalnızca az miktarda veri için kabul edilebilir. Büyük veri kümelerini Excel'e aktarmanız gerekiyorsa, daha sonra sunulan çözümlerden birini göz önünde bulundurmanız gerekir.
Excel'i Otomatikleştirme için daha fazla örnek kod için bkz. Visual Basic'ten Microsoft Excel'i otomatikleştirme.
Bir veri dizisini çalışma sayfasındaki bir aralığa aktarmak için otomasyon kullanma
Bir veri dizisi aynı anda birden çok hücre aralığına aktarılabilir:
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
'Start a new workbook in Excel
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
'Create an array with 3 columns and 100 rows
Dim DataArray(1 To 100, 1 To 3) As Variant
Dim r As Integer
For r = 1 To 100
DataArray(r, 1) = "ORD" & Format(r, "0000")
DataArray(r, 2) = Rnd() * 1000
DataArray(r, 3) = DataArray(r, 2) * 0.7
Next
'Add headers to the worksheet on row 1
Set oSheet = oBook.Worksheets(1)
oSheet.Range("A1:C1").Value = Array("Order ID", "Amount", "Tax")
'Transfer the array to the worksheet starting at cell A2
oSheet.Range("A2").Resize(100, 3).Value = DataArray
'Save the Workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit
Verilerinizi hücre hücre değil dizi kullanarak aktarırsanız, büyük miktarda veriyle muazzam bir performans kazancı elde edebilirsiniz. Yukarıdaki kodda yer alan ve çalışma sayfasındaki 300 hücreye veri aktaran bu satırı göz önünde bulundurun:
oSheet.Range("A2").Resize(100, 3).Value = DataArray
Bu satır iki arabirim isteğini temsil eder (biri Range yönteminin döndürdüğü Range nesnesi için, diğeri resize yönteminin döndürdüğü Range nesnesi için). Öte yandan, veri hücresinin hücreye göre aktarılması için Range nesnelerine 300 arabirim isteği gerekir. Mümkün olduğunda verilerinizi toplu olarak aktarma ve yaptığınız arabirim isteklerinin sayısını azaltma avantajından yararlanabilirsiniz.
ADO kayıt kümesini çalışma sayfası aralığına aktarmak için otomasyon kullanma
Excel 2000, çalışma sayfasındaki bir aralığa ADO (veya DAO) kayıt kümesini aktarmanızı sağlayan CopyFromRecordset yöntemini kullanıma sunar. Aşağıdaki kodda, CopyFromRecordset yöntemini kullanarak Excel 2000, Excel 2002 veya Office Excel 2003'i otomatikleştirme ve Northwind Örnek Veritabanı'ndaki Orders tablosunun içeriğini aktarma işlemleri gösterilmektedir.
'Create a Recordset from all the records in the Orders table
Dim sNWind As String
Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset
sNWind = _
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb" conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ sNWind & ";"
conn.CursorLocation = adUseClient
Set rs = conn.Execute("Orders", , adCmdTable)
'Create a new workbook in Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
'Transfer the data to Excel
oSheet.Range("A1").CopyFromRecordset rs
'Save the Workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit
'Close the connection
rs.Close
conn.Close
Not Northwind veritabanının Office 2007 sürümünü kullanıyorsanız, kod örneğinde aşağıdaki kod satırını değiştirmeniz gerekir:
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ sNWind & ";"
Bu kod satırını aşağıdaki kod satırıyla değiştirin:
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _ sNWind & ";"
Excel 97 bir CopyFromRecordset yöntemi de sağlar, ancak bunu yalnızca bir DAO kayıt kümesiyle kullanabilirsiniz. Excel 97 ile CopyFromRecordset ADO'yı desteklemez.
ADO ve CopyFromRecordset yöntemini kullanma hakkında daha fazla bilgi için bkz. Otomasyon ile bir ADO kayıt kümesinden Excel'e veri aktarma.
Çalışma sayfasında QueryTable oluşturmak için otomasyon kullanma
QueryTable nesnesi, dış veri kaynağından döndürülen verilerden oluşturulmuş bir tabloyu temsil eder. Microsoft Excel'i otomatikleştirirken, SQL dizesiyle birlikte bir OLEDB veya ODBC veri kaynağına bağlantı dizesi sağlayarak bir QueryTable oluşturabilirsiniz. Excel, kayıt kümesini oluşturma ve belirttiğiniz konumda çalışma sayfasına ekleme sorumluluğunu üstlenir. QueryTable'ları kullanmak CopyFromRecordset yöntemine göre çeşitli avantajlar sunar:
- Excel, kayıt kümesinin oluşturulmasını ve çalışma sayfasına yerleştirilmesini işler.
- Sorgu, güncelleştirilmiş bir kayıt kümesi elde etmek üzere daha sonra yenilenebilmesi için QueryTable ile kaydedilebilir.
- Çalışma sayfanıza yeni bir QueryTable eklendiğinde, çalışma sayfasındaki hücrelerde zaten var olan verilerin yeni verileri barındıracak şekilde kaydırılacağını belirtebilirsiniz (ayrıntılar için RefreshStyle özelliğine bakın).
Aşağıdaki kod, Northwind Örnek Veritabanı'ndaki verileri kullanarak Excel çalışma sayfasında yeni bir QueryTable oluşturmak için Excel 2000, Excel 2002 veya Office Excel 2003'i nasıl otomatikleştirebildiğinizi gösterir:
'Create a new workbook in Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
'Create the QueryTable
Dim sNWind As String
sNWind = _
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
Dim oQryTable As Object
Set oQryTable = oSheet.QueryTables.Add( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sNWind & ";", oSheet.Range("A1"), "Select * from Orders") oQryTable.RefreshStyle = xlInsertEntireRows
oQryTable.Refresh False
'Save the Workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit
Panoyu kullanma
Windows Panosu, çalışma sayfasına veri aktarma mekanizması olarak da kullanılabilir. Çalışma sayfasındaki birden çok hücreye veri yapıştırmak için, sütunların sekme karakterleriyle, satırların satır başıyla sınırlandırıldığı bir dizeyi kopyalayabilirsiniz. Aşağıdaki kod, Visual Basic'in Verileri Excel'e aktarmak için Pano nesnesini nasıl kullanabileceğini gösterir:
'Copy a string to the clipboard
Dim sData As String
sData = "FirstName" & vbTab & "LastName" & vbTab & "Birthdate" & vbCr _ & "Bill" & vbTab & "Brown" & vbTab & "2/5/85" & vbCr _
& "Joe" & vbTab & "Thomas" & vbTab & "1/1/91"
Clipboard.Clear
Clipboard.SetText sData
'Create a new workbook in Excel
Dim oExcel As Object
Dim oBook As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
'Paste the data
oBook.Worksheets(1).Range("A1").Select
oBook.Worksheets(1).Paste
'Save the Workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit
Excel'in satır ve sütunlar halinde ayrıştırabileceği sınırlandırılmış bir metin dosyası oluşturma
Excel sekme veya virgülle ayrılmış dosyaları açabilir ve verileri doğru şekilde hücrelere ayrıştırabilir. Çok az (varsa, Otomasyon) kullanırken çalışma sayfasına büyük miktarda veri aktarmak istediğinizde bu özelliğin avantajlarından yararlanabilirsiniz. Metin dosyası sunucu tarafında oluşturulabileceğinden, bu bir istemci-sunucu uygulaması için iyi bir yaklaşım olabilir. Ardından, uygun olduğu yerde Otomasyon'ı kullanarak metin dosyasını istemcide açabilirsiniz.
Aşağıdaki kod, ADO kayıt kümesinden virgülle ayrılmış metin dosyasını nasıl oluşturabileceğinizi gösterir:
'Create a Recordset from all the records in the Orders table
Dim sNWind As String
Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim sData As String
sNWind = _
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb" conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ sNWind & ";"
conn.CursorLocation = adUseClient
Set rs = conn.Execute("Orders", , adCmdTable)
'Save the recordset as a tab-delimited file
sData = rs.GetString(adClipString, , vbTab, vbCr, vbNullString)
Open "C:\Test.txt" For Output As #1
Print #1, sData
Close #1
'Close the connection
rs.Close
conn.Close
'Open the new text file in Excel
Shell "C:\Program Files\Microsoft Office\Office\Excel.exe " & _ Chr(34) & "C:\Test.txt" & Chr(34), vbMaximizedFocus
Not Northwind veritabanının Office 2007 sürümünü kullanıyorsanız, kod örneğinde aşağıdaki kod satırını değiştirmeniz gerekir:
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sNWind & ";"
Bu kod satırını aşağıdaki kod satırıyla değiştirin:
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
sNWind & ";"
Metin dosyanızın .CSV bir uzantısı varsa, Excel dosyayı Metin İçeri Aktarma Sihirbazı'nı görüntülemeden açar ve dosyanın virgülle ayrılmış olduğunu otomatik olarak varsayar. Benzer şekilde, dosyanızda .TXT bir uzantı varsa, Excel sekme sınırlayıcılarını kullanarak dosyayı otomatik olarak ayrıştırmıştır.
Önceki kod örneğinde, Excel Shell deyimi kullanılarak başlatılmıştı ve dosyanın adı komut satırı bağımsız değişkeni olarak kullanılıyordu. Önceki örnekte Otomasyon kullanılmadı. Ancak isterseniz, metin dosyasını açmak ve Excel çalışma kitabı biçiminde kaydetmek için minimum miktarda Otomasyon kullanabilirsiniz:
'Create a new instance of Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
'Open the text file
Set oBook = oExcel.Workbooks.Open("C:\Test.txt")
'Save as Excel workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls", xlWorkbookNormal
oExcel.Quit
ADO kullanarak çalışma sayfasına veri aktarma
Microsoft Jet OLE DB Sağlayıcısı'nı kullanarak, var olan bir Excel çalışma kitabındaki bir tabloya kayıt ekleyebilirsiniz. Excel'de "tablo" yalnızca tanımlı ada sahip bir aralıktır. Aralığın ilk satırı üst bilgileri (veya alan adlarını) ve izleyen tüm satırların kayıtları içermesi gerekir. Aşağıdaki adımlarda, MyTable adlı boş bir tabloyla nasıl çalışma kitabı oluşturabileceğiniz gösterilmektedir.
Excel 97, Excel 2000 ve Excel 2003
Excel'de yeni bir çalışma kitabı başlatın.
Sayfa1'in A1:B1 hücrelerine aşağıdaki üst bilgileri ekleyin:
A1: Ad B1: Soyadı
B1 hücreyi sağa hizalı olarak biçimlendirin.
A1:B1'i seçin.
Ekle menüsünde Adlar'ı ve ardından Tanımla'yı seçin. MyTable adını girin ve Tamam'a tıklayın.
Yeni çalışma kitabını C:\Book1.xls olarak kaydedin ve Excel'den çıkın.
ADO kullanarak MyTable'a kayıt eklemek için aşağıdakine benzer bir kod kullanabilirsiniz:
'Create a new connection object for Book1.xls
Dim conn As New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Book1.xls;Extended Properties=Excel 8.0;"
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
" values ('Bill', 'Brown')"
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
" values ('Joe', 'Thomas')"
conn.Close
Excel 2007
Excel 2007'de yeni bir çalışma kitabı başlatın.
Sayfa1'in A1:B1 hücrelerine aşağıdaki üst bilgileri ekleyin:
A1: Ad B1: Soyadı
B1 hücreyi sağa hizalı olarak biçimlendirin.
A1:B1'i seçin.
Şeritte Formüller sekmesine ve ardından Ad Tanımla'ya tıklayın. MyTable adını yazın ve Tamam'a tıklayın.
Yeni çalışma kitabını C:\Book1.xlsx olarak kaydedin ve Excel'den çıkın.
ADO kullanarak MyTable tablosuna kayıt eklemek için aşağıdaki kod örneğine benzer bir kod kullanın.
'Create a new connection object for Book1.xls
Dim conn As New ADODB.Connection
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\Book1.xlsx;Extended Properties=Excel 12.0;"
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
" values ('Scott', 'Brown')"
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
" values ('Jane', 'Dow')"
conn.Close
Tabloya bu şekilde kayıt eklediğinizde, çalışma kitabındaki biçimlendirme korunur. Önceki örnekte, B sütununa eklenen yeni alanlar doğru hizalamayla biçimlendirilmiştir. Satıra eklenen her kayıt, yukarıdaki satırdan biçimi ödünç alır.
Çalışma sayfasındaki bir hücreye veya hücrelere bir kayıt eklendiğinde, daha önce bu hücrelerdeki verilerin üzerine yazıldığını unutmayın; başka bir deyişle, yeni kayıtlar eklendiğinde çalışma sayfasındaki satırlar "aşağı gönderilmez". Çalışma sayfalarınızdaki verilerin düzenini tasarlarken bunu göz önünde bulundurmanız gerekir.
Not
Excel çalışma sayfasındaki verileri ADO kullanarak veya DAO kullanarak güncelleştirme yöntemi, Office 2003 Service Pack 2'yi (SP2) yükledikten sonra veya 904018 Microsoft Bilgi Bankası makalesinde yer alan Access 2002 güncelleştirmesini yükledikten sonra Access içindeki Visual Basic for Application ortamında çalışmaz. yöntemi, Word, Excel ve Outlook gibi diğer Office uygulamalarından Visual Basic for Application ortamında iyi çalışır.
Daha fazla bilgi için aşağıdaki makaleye bakın:
Excel çalışma kitabına erişmek için ADO kullanma hakkında daha fazla bilgi için bkz. ASP'den ADO Kullanarak Excel Verilerini Sorgulama ve Güncelleştirme.
Excel'e veri aktarmak için DDE kullanma
DDE, Excel ile iletişim kurmak ve veri aktarmak için otomasyona bir alternatiftir; ancak Otomasyon ve COM'un ortaya çıkmasıyla birlikte DDE artık diğer uygulamalarla iletişim kurmak için tercih edilen yöntem değildir ve yalnızca kullanabileceğiniz başka bir çözüm olmadığında kullanılmalıdır.
DDE kullanarak Excel'e veri aktarmak için, verileri belirli bir hücre aralığına dürtmek için LinkPoke yöntemini kullanabilir veya Excel'in yürüteceği komutları göndermek için LinkExecute yöntemini kullanabilirsiniz.
Aşağıdaki kod örneği, çalışma sayfasındaki hücrelere veri dürtmek ve komutları yürütmek için Excel ile DDE konuşması oluşturmayı göstermektedir. Bir DDE konuşmasının LinkTopic Excel'e başarıyla kurulması için bu örneği kullanma|MyBook.xls, adı MyBook.xls olan bir çalışma kitabının çalışan bir Excel örneğinde açılmış olması gerekir.
Not
Excel 2007 kullandığınızda, çalışma kitaplarını kaydetmek için yeni .xlsx dosya biçimini kullanabilirsiniz. Aşağıdaki kod örneğinde dosya adını güncelleştirdiğinizden emin olun. Bu örnekte Text1, Visual Basic formundaki bir Metin Kutusu denetimini temsil eder:
'Initiate a DDE communication with Excel
Text1.LinkMode = 0
Text1.LinkTopic = "Excel|MyBook.xls"
Text1.LinkItem = "R1C1:R2C3"
Text1.LinkMode = 1
'Poke the text in Text1 to the R1C1:R2C3 in MyBook.xls
Text1.Text = "one" & vbTab & "two" & vbTab & "three" & vbCr & _
"four" & vbTab & "five" & vbTab & "six"
Text1.LinkPoke
'Execute commands to select cell A1 (same as R1C1) and change the font format
Text1.LinkExecute "[SELECT(""R1C1"")]"
Text1.LinkExecute "[FONT.PROPERTIES(""Times New Roman"",""Bold"",10)]"
'Terminate the DDE communication
Text1.LinkMode = 0
Excel ile LinkPoke kullanırken, LinkItem için satır sütunu (R1C1) gösteriminde aralığı belirtirsiniz. Verileri birden çok hücreye dürtüyorsanız, sütunların sekmelerle, satırların satır başıyla sınırlandığı bir dize kullanabilirsiniz.
Excel'den komut yürütmesini istemek için LinkExecute kullandığınızda, Excel'e Excel Makro Dili (XLM) söz diziminde komutu vermelisiniz. XLM belgeleri Excel'in 97 ve sonraki sürümlerine dahil değildir.
DDE, Excel ile iletişim kurmak için önerilen bir çözüm değildir. Otomasyon en büyük esnekliği sağlar ve Excel'in sunduğu yeni özelliklere daha fazla erişim sağlar.
Geri Bildirim
Çok yakında: 2024 boyunca, içerik için geri bildirim mekanizması olarak GitHub Sorunları’nı kullanımdan kaldıracak ve yeni bir geri bildirim sistemiyle değiştireceğiz. Daha fazla bilgi için bkz. https://aka.ms/ContentUserFeedback.
Gönderin ve geri bildirimi görüntüleyin