Excel boleh melakukan apa sahaja; adakah harus dibuat untuk melakukan semuanya adalah perkara lain. Walaupun spreadsheet sangat kuat dalam memanipulasi data, tidak terlalu bagus untuk menyimpan data yang dinormalisasi. Memanfaatkan Excel ke pangkalan data hubungan seperti SQL Server meningkatkan kekuatan aplikasi.
Untuk bermula, anda memerlukan MS Access atau yang lebih stabil dan percuma – SQL Server Menyatakan. Diandaikan bahawa pembaca mempunyai pita Pembangun Excel yang dipaparkan, dan biasa dengan Editor VBA dan Bahasa Pertanyaan Berstruktur (SQL). Artikel ini menggunakan SQL Server rentetan sambungan. Untuk Akses MS, rujuk Google.
Walaupun Excel mempunyai rutin terbina dalam untuk mendapatkan maklumat dari SQL Server ke dalam (katakanlah) jadual pangsi, contoh kita akan memberikan lebih banyak fleksibiliti dalam pemilihan data.
Rentetan Sambungan
Saya akan menggunakan pangkalan data peribadi; masukkan maklumat pemandu anda sendiri di tempat saya dalam sub rutin ConnectDatabase. Kami kemudian menggunakan konDB sebagai saluran komunikasi ke pangkalan data kami - dalam kes saya untuk mengembalikan hasil dari prosedur yang tersimpan. Anda mungkin menggunakan pernyataan SQL yang lebih standard seperti "Pilih * dari ..."
Urutan Perniagaan
Pertama, kami akan memuatkan pilihan kotak kombo dari SQL Server apabila buku kerja dibuka, menggunakan makro Auto_open dan membuangnya ke dalam helaian “ComboData”. Sama ada Pelayan berada di awan atau setempat, tidak akan ada kelewatan yang ketara dalam memulakan Excel – selagi pangkalan data boleh diakses dari stesen kerja.
Seterusnya, kami akan mengekstrak data yang disaring dari pangkalan data dan memasukkannya ke dalam Excel, lajur F hingga K.
Antara Muka
Tambang mempunyai kotak lungsur untuk menyaring maklumat dari pangkalan data. The Peranan kotak kombo mencetuskan carian untuk mengisi jadual di sebelah kanan.
Namakan semula "Lembaran1" sebagai "Utama". Tambahkan sekurang-kurangnya satu komboboks.
Kod ini
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
Format kawalan kotak kombo untuk membaca helaian "ComboData". Kemudian klik kanan kotak kombo untuk menetapkan sub prosedur ReadData kepadanya. Apabila item dipilih dalam kotak kombo, tulis kuncinya ke helaian "Utama", sel D7. Kod VBA akan menggunakan kunci ini sebagai penapis (lihat intRole, di atas).
Rujukan kepada pustaka dll
Gunakan Tools>Rujukan dalam tetingkap kod untuk merujuk pustaka Microsoft Active X Data Objects. Ini akan membolehkan Excel menggunakan objek ADODB yang diisytiharkan dalam kod.
Sub rutin ReadData di atas menggunakan struktur data hubungan, ditunjukkan di bawah, yang sukar dicapai dalam Excel sahaja.
Perubahan data lebih lanjut dapat memicu penulisan kembali ke pangkalan data, dengan pernyataan SQL Update yang sesuai diikuti oleh connDB.execute (strSQL).
Akhirnya, lindungi kod anda daripada dilihat atau diubah: Alat> Sifat> Perlindungan.
Tangani masalah Excel:
Dari semasa ke semasa, terutama ketika memegang program yang rumit, Excel mungkin mogok dan gagal menutup kembali dengan betul. Sekiranya berlaku a rosak xlsx fail, mempunyai alat pemulihan yang berkesan di tempat kerja akan menyelesaikan kebanyakan masalah.
Pengenalan Pengarang:
Felix Hooker adalah pakar pemulihan data di DataNumen, Inc., yang merupakan pemimpin dunia dalam teknologi pemulihan data, termasuk pembaikan rar kesilapan dan produk perisian pemulihan sql. Untuk maklumat lebih lanjut, lawati www.datanumen.com