Cara Menggunakan Excel untuk Membaca dan Menulis Database Eksternal

Excel dapat melakukan hampir semua hal; apakah harus dibuat untuk melakukan segalanya adalah masalah lain. Meskipun spreadsheet sangat kuat dalam memanipulasi data, itu tidak terlalu bagus dalam menyimpan data yang dinormalisasi. Memanfaatkan Excel ke database relasional seperti SQL Server meningkatkan kekuatan aplikasi.

Sebagai langkah awal, Anda memerlukan MS Access atau versi yang lebih stabil dan gratis – SQL Server Mengekspresikan. Diasumsikan bahwa pembaca memiliki pita Pengembang Excel yang ditampilkan, dan terbiasa dengan Editor VBA dan Bahasa Kueri Terstruktur (SQL). Artikel ini menggunakan SQL Server string koneksi. Untuk MS-Access, lihat Google.

Sementara Excel memiliki rutinitas bawaannya sendiri untuk mendapatkan informasi SQL Server ke dalam (katakanlah) tabel pivot, contoh kami akan memberikan lebih banyak fleksibilitas dalam pemilihan data.

String Koneksi

Saya akan menggunakan database pribadi; masukkan informasi driver Anda sendiri di tempat saya di sub rutin ConnectDatabase. Kami kemudian menggunakan koneksiDB sebagai saluran komunikasi ke database kami - dalam kasus saya untuk mengembalikan hasil dari prosedur yang tersimpan. Anda mungkin menggunakan pernyataan SQL yang lebih standar seperti "Pilih * dari ..."

Urutan Bisnis

Pertama, kami akan memuat pilihan kotak kombo dari SQL Server Saat buku kerja dibuka, menggunakan makro Auto_open, dan membuangnya ke lembar "ComboData". Baik server berada di cloud atau lokal, tidak akan ada penundaan yang terlihat dalam memulai Excel – selama basis data dapat diakses dari workstation.

Selanjutnya, kami akan mengekstrak data yang difilter dari database dan memasukkannya ke Excel, kolom F ke K.

Antarmuka

Milik saya memiliki kotak drop-down untuk memfilter informasi dari database. Itu Peran kotak kombo memicu pencarian untuk mengisi tabel di kanan.

Ubah nama "Sheet1" menjadi "Utama". Tambahkan setidaknya satu kotak kombo.

Kode

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 kontrol kotak kombo untuk membaca lembar "Data Kombo". Kemudian klik kanan kotak kombo untuk menetapkan sub prosedur ReadData ke dalamnya. Ketika sebuah item dipilih dalam kotak kombo, tulis kuncinya ke sheet "Utama", sel D7. Kode VBA akan menggunakan kunci ini sebagai filter (lihat intRole, di atas).

Referensi ke pustaka dll

Gunakan Tools > References di jendela kode untuk menambahkan referensi ke pustaka Microsoft Active X Data Objects. Ini akan memungkinkan Excel untuk menggunakan objek ADODB yang dideklarasikan dalam kode.

Sub rutin ReadData di atas menggunakan struktur data relasional, seperti yang ditunjukkan di bawah, yang sulit dicapai hanya di Excel.

Perubahan data lebih lanjut dapat memicu penulisan kembali ke database, dengan pernyataan Pembaruan SQL yang sesuai diikuti oleh connDB.execute (strSQL).

Terakhir, lindungi kode Anda agar tidak dilihat atau diubah:  Alat> Properti> Perlindungan.

Tangani masalah Excel:

Dari waktu ke waktu, terutama saat menjalankan program yang kompleks, Excel mungkin macet dan gagal menutup kembali dengan benar. Dalam hal a xlsx rusak Memiliki alat pemulihan data yang efektif akan menyelesaikan sebagian besar masalah.

Pengantar Penulis:

Felix Hooker adalah pakar pemulihan data di DataNumen, Inc., yang merupakan pemimpin dunia dalam teknologi pemulihan data, termasuk memperbaiki rar kesalahan dan produk perangkat lunak pemulihan sql. Untuk informasi lebih lanjut kunjungi www.datanumen.com