Excel neredeyse her şeyi yapabilir; her şeyi yapması gerekip gerekmediği başka bir konudur. Elektronik tablo, verileri manipüle etmede çok güçlü olsa da, normalleştirilmiş verileri depolamada çok iyi değildir. Excel'i aşağıdaki gibi ilişkisel bir veritabanında kullanmak SQL Server uygulamanın gücünü artırır.
Öncelikle MS Access'e veya daha istikrarlı ve ücretsiz olanına ihtiyacınız olacak. SQL Server İfade etmek. Okuyucunun Excel Geliştirici şeridinin görüntülendiği ve VBA Düzenleyicisi ile Yapılandırılmış Sorgu Dili'ne (SQL) aşina olduğu varsayılmaktadır. Bu makale kullanır SQL Server bağlantı dizeleri. MS-Access için Google'a bakın.
Excel'in bilgi almak için kendi yerleşik yordamları olsa da SQL Server örneğin bir pivot tabloya dönüştürürseniz, örneğimiz veri seçiminde daha fazla esneklik sağlayacaktır.
Bağlantı dizisi
Özel bir veritabanı kullanacağım; kendi sürücü bilgilerinizi ConnectDatabase alt rutininde benimkinin yerine ekleyin. daha sonra kullanırız bağlantıDB veritabanımıza bir iletişim kanalı olarak - benim durumumda saklı bir prosedürden sonuçları döndürmek için. “Select * from…” gibi daha standart SQL deyimleri kullanabilirsiniz.
İş Sırası
İlk olarak, açılan kutu seçimlerini şu adresten yükleyeceğiz: SQL Server Çalışma kitabı açıldığında, bir Auto_open makrosu kullanılarak "ComboData" sayfasına aktarılır. Sunucu bulutta veya yerel olsun, veritabanına iş istasyonundan erişilebildiği sürece Excel'in başlatılmasında fark edilebilir bir gecikme olmayacaktır.
Ardından, filtrelenmiş verileri veritabanından çıkaracağız ve F'den K'ye kadar olan sütunlar olan Excel'e bırakacağız.
Arayüz
Mine, veritabanından bilgileri filtrelemek için açılan kutulara sahiptir. bu Rol birleşik giriş kutusu, sağdaki tabloyu doldurmak için bir aramayı tetikler.
"Sayfa1"i "Ana" olarak yeniden adlandırın. En az bir birleşik giriş kutusu ekleyin.
Kod
Public connDB As New ADODB.Connection
Public rstNew As New ADODB.Recordset
Public rs As New ADODB.Recordset
Public strSQL As String
Public nID As Integer
Sub auto_Open()
Call PopulateComboData 'kicks off the first process on Open
End Sub
Sub PopulateComboData()
Sheets("ComboData").Range("A3:C100").ClearContents
Call ConnectDatabase 'use the ConnectDatabase routine
strSQL = "Select DeptID, Department, Phase from tblDept Order by Department"
Set rs = connDB.Execute(strSQL)
ActiveSheet.Range("A3").CopyFromRecordset rs 'copies the recordset in bulk
End Sub
Sub ReadData()
intRole = Sheets("main").Range("D7")
Sheets("Main").Range("F4:L100").ClearContents
Call ConnectDatabase
strSQL = "EXEC DBTest " & intRole 'calls a stored proc with parameter
Set rs = connDB.Execute(strSQL)
ActiveSheet.Range("F4").CopyFromRecordset rs 'copies the recordset in bulk
End Sub
Sub ConnectDatabase()
On Error GoTo ErrConnect
If connDB.State = 1 Then connDB.Close 'closes connection if already open
strServer = "197.200.28.164"
strDBase = "Qcrew_sql"
strUser = "joesoap_sql"
strPWD = "frU6ra!@"
If strPWD > "" Then
strConnectionstring = "DRIVER={SQL Server};Server=" & strServer & _
";Database=" & strDBase & ";Uid=" & strUser & ";Pwd=" & strPWD & _
";Connection Timeout=30;"
Else 'Use windows authentication
strConnectionstring = "DRIVER={SQL Server};SERVER=" & strServer & _
";Trusted_Connection=yes;DATABASE=" & strDBase
End If
connDB.Open strConnectionstring
Exit Sub
ErrConnect:
MsgBox Err.Description
End Sub
Birleşik giriş kutusu denetimini "ComboData" sayfalarını okumak için biçimlendirin. Ardından, ReadData alt prosedürünü ona atamak için birleşik giriş kutusuna sağ tıklayın. Açılan kutuda bir öğe seçildiğinde, anahtarını "Ana" sayfası, D7 hücresine yazın. VBA kodu bu anahtarı bir filtre olarak kullanacaktır (yukarıdaki intRole'e bakın).
DLL kütüphanesine yapılan referanslar
Kod penceresinde Araçlar > Referanslar seçeneğini kullanarak Microsoft ActiveX Veri Nesneleri kitaplığına referans verin. Bu, Excel'in kodda tanımlanan ADODB nesnelerini kullanmasını sağlayacaktır.
Yukarıdaki ReadData alt yordamı, aşağıda gösterilen ve yalnızca Excel'de elde edilmesi zor olan bir ilişkisel veri yapısı kullanır.
Daha fazla veri değişikliği, uygun SQL Update deyiminin ardından veritabanına bir geri yazmayı tetikleyebilir. connDB.execute(strSQL).
Son olarak, kodunuzu görüntülenmeye veya değiştirilmeye karşı koruyun: Araçlar>Özellikler>Koruma.
Excel sorunlarını ele alın:
Zaman zaman, özellikle karmaşık programları barındırdığında, Excel çökebilir ve düzgün bir şekilde yeniden yükleyemeyebilir. bir durumda hasarlı xlsx Dosyanızda herhangi bir sorun varsa, etkili bir kurtarma aracına sahip olmak çoğu problemi çözecektir.
Yazar Tanıtımı:
Felix Hooker, veri kurtarma uzmanıdır. DataNumendahil olmak üzere veri kurtarma teknolojilerinde dünya lideri olan , Inc. onarım rar hata ve sql kurtarma yazılımı ürünleri. Daha fazla bilgi için ziyaret edin www.datanumen.com