Как да използвам Excel за четене и писане на външна база данни

Excel може да прави почти всичко; дали трябва да се накара да прави всичко е друг въпрос. Макар че електронната таблица е много мощна за манипулиране на данни, тя не е много добра за съхранение на нормализирани данни. Използване на Excel към релационна база данни като SQL Server подобрява мощността на приложението.

За начало ще ви е необходим MS Access или по-стабилната и безплатна версия – SQL Server Експрес. Предполага се, че четецът показва лентата на Excel Developer и е запознат с редактора на VBA и езика за структурирани заявки (SQL). Тази статия използва SQL Server низове за връзка. За MS-Access се обърнете към Google.

Докато Excel има свои собствени вградени процедури за получаване на информация от SQL Server в (да речем) обобщена таблица, нашият пример ще даде по-голяма гъвкавост при избора на данни.

Свързващ низ

Ще използвам частна база данни; вмъкнете вашата собствена информация за драйвера вместо моята в подпрограмата ConnectDatabase. След това използваме connDB като комуникационен канал към нашата база данни - в моя случай за връщане на резултати от съхранена процедура. Може да използвате по-стандартни SQL изрази като „Изберете * от ...“

Поръчка на бизнеса

Първо, ще заредим избора на комбинирани полета от SQL Server когато работната книга се отвори, използвайки макрос Auto_open и изхвърляйки данните в лист „ComboData“. Независимо дали сървърът е в облака или локално, няма да има забележимо забавяне при стартирането на Excel – стига базата данни да е достъпна от работната станция.

След това ще извлечем филтрирани данни от базата данни и ще ги пуснем в Excel, колони F до K.

Интерфейсът

Моят има падащи полета за филтриране на информация от базата данни. The Роля комбинираното поле задейства търсене за попълване на таблицата вдясно.

Преименувайте „Sheet1“ като „Main“. Добавете поне един комбобокс.

Кодексът

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“. След това щракнете с десния бутон върху комбинираното поле, за да му присвоите подпроцедурата ReadData. Когато в комбинираното поле е избран елемент, напишете неговия ключ на лист „Основен“, клетка D7. Кодът на VBA ще използва този ключ като филтър (вижте intRole, по-горе).

Препратки към dll библиотека

Използвайте „Инструменти“>„Препратки“ в прозореца с код, за да направите препратка към библиотеката с данни на Microsoft Active X. Това ще позволи на Excel да използва декларираните в кода ADODB обекти.

Подпрограмата ReadData по-горе използва релационна структура от данни, показана по-долу, което е трудно да се постигне само в Excel.

По-нататъшните промени в данните могат да предизвикат обратно изписване в базата данни, със съответния израз на SQL Update, последван от connDB.execute (strSQL).

И накрая, защитете кода си от преглед или промяна:  Инструменти> Свойства> Защита.

Справяне с проблеми с Excel:

От време на време, особено когато съдържа сложни програми, Excel може да се срине и да не успее да покрие правилно. В случай на a повреден xlsx файл, наличието на ефективен инструмент за възстановяване под ръка ще реши повечето проблеми.

Въведение на автора:

Феликс Хукър е експерт по възстановяване на данни в DataNumen, Inc., която е световен лидер в технологиите за възстановяване на данни, включително ремонт rar грешка и sql софтуерни продукти за възстановяване. За повече информация посетете WWW.datanumen.com