Excel deyarli hamma narsani qila oladi; hamma narsani qilish kerakmi, boshqa masala. Elektron jadval ma'lumotlarni manipulyatsiya qilishda juda kuchli bo'lsa-da, normallashtirilgan ma'lumotlarni saqlashda unchalik yaxshi emas. Excelni relyatsion ma'lumotlar bazasiga ishlatish SQL Server ilovaning kuchini oshiradi.
Boshlash uchun sizga MS Access yoki undan barqaror va bepul versiya kerak bo'ladi - SQL Server Ekspress. O'quvchida Excel Developer tasmasi ko'rsatilgan va VBA muharriri va Strukturaviy so'rovlar tili (SQL) bilan tanish deb taxmin qilinadi. Ushbu maqoladan foydalaniladi SQL Server ulanish qatorlari. MS-Access uchun Google-ga qarang.
Excel-da ma'lumot olish uchun o'zining o'rnatilgan tartiblari mavjud SQL Server pivot jadvalga (aytaylik), bizning misolimiz ma'lumotlarni tanlashda ko'proq moslashuvchanlikni beradi.
Ulanish qatori
Men shaxsiy ma'lumotlar bazasidan foydalanaman; ConnectDatabase quyi dasturida meniki o'rniga o'zingizning haydovchi ma'lumotlaringizni kiriting. Keyin foydalanamiz connDB bizning ma'lumotlar bazasiga aloqa kanali sifatida - mening holimda saqlangan protsedura natijalarini qaytarish uchun. “… dan * ni tanlang” kabi standart SQL iboralaridan foydalanishingiz mumkin.
Biznes tartibi
Birinchidan, biz Combo-box tanlovlarini yuklaymiz SQL Server ish daftari ochilganda, Auto_open makrosidan foydalanib va uni “ComboData” varagʻiga joylashtiradi. Server bulutda yoki mahalliy boʻlishidan qatʼi nazar, Excelni ishga tushirishda sezilarli kechikish boʻlmaydi – agar maʼlumotlar bazasiga ish stantsiyasidan kirish mumkin boʻlsa.
Keyinchalik, biz ma'lumotlar bazasidan filtrlangan ma'lumotlarni ajratib olamiz va uni Excelga, F dan K gacha ustunlarga tashlaymiz.
Interfeys
Mening ma'lumotlar bazasidan ma'lumotlarni filtrlash uchun ochiladigan qutilar mavjud. The roli ochilgan oyna o'ng tarafdagi jadvalni to'ldirish uchun qidiruvni ishga tushiradi.
"Sheet1" nomini "Asosiy" deb o'zgartiring. Kamida bitta kombinatsiyalangan quti qo'shing.
Kodeks
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
"ComboData" varaqlarini o'qish uchun birlashtirilgan quti boshqaruvini formatlang. Keyin ReadData quyi protsedurasini tayinlash uchun ochilgan oynani o'ng tugmasini bosing. Biror oynada element tanlanganda, uning kalitini "Asosiy" varaqning D7 katakchasiga yozing. VBA kodi ushbu kalitni filtr sifatida ishlatadi (yuqoridagi intRole-ga qarang).
dll kutubxonasiga havolalar
Microsoft Active X Data Objects kutubxonasiga havola qilish uchun kod oynasida Tools>References dan foydalaning. Bu Excelga kodda e'lon qilingan ADODB obyektlaridan foydalanish imkonini beradi.
Yuqoridagi ReadData quyi tartibi quyida ko'rsatilgan relyatsion ma'lumotlar strukturasidan foydalanadi, bunga faqat Excelda erishish qiyin.
Ma'lumotlarning keyingi o'zgarishlari tegishli SQL Update bayonoti bilan ma'lumotlar bazasiga qayta yozishni boshlashi mumkin connDB.execute(strSQL).
Nihoyat, kodingizni ko'rish yoki o'zgartirishdan himoya qiling: Asboblar> Xususiyatlar> Himoya.
Excel bilan bog'liq muammolarni hal qilish:
Vaqti-vaqti bilan, ayniqsa murakkab dasturlarga ega bo'lsa, Excel ishlamay qolishi va uni to'g'ri qoplamasligi mumkin. A bo'lgan taqdirda shikastlangan xlsx fayl bo'lsa, samarali tiklash vositasiga ega bo'lish ko'pgina muammolarni hal qiladi.
Muallif kirish:
Feliks Xuker ma'lumotlarni qayta tiklash bo'yicha mutaxassis DataNumenMa'lumotlarni qayta tiklash texnologiyalari bo'yicha jahon yetakchisi bo'lgan , Inc ta'mirlash rar xato va sql-ni tiklash dasturiy mahsulotlar. Qo'shimcha ma'lumot olish uchun tashrif buyuring www.datanumen.com