Excel може робити практично все; чи слід це робити все, це інша справа. Хоча електронна таблиця дуже потужна в обробці даних, вона не надто велика для зберігання нормалізованих даних. Використання Excel для реляційної бази даних, як SQL Server покращує потужність програми.
To start, вам знадобиться MS Access або більш стабільний і безкоштовний - SQL Server Експрес. Передбачається, що на пристрої для читання відображається стрічка Excel Developer і він знайомий з редактором VBA та мовою структурованих запитів (SQL). Ця стаття використовує SQL Server рядки з'єднання. Щодо MS-Access, зверніться до Google.
У той час як Excel має власні вбудовані процедури отримання інформації з SQL Server у (скажімо) зведену таблицю, наш приклад надасть більшу гнучкість у виборі даних.
Рядок з'єднання
Я буду використовувати приватну базу даних; вставте власну інформацію про драйвера замість моєї в підпрограму ConnectDatabase. Потім ми використовуємо connDB як канал зв'язку до нашої бази даних - у моєму випадку для повернення результатів із збереженої процедури. Ви можете використовувати більш стандартні оператори SQL, такі як "Вибрати * з ..."
Порядок ведення бізнесу
По-перше, ми завантажимо комбіновані варіанти з SQL Server коли книга відкривається, використовуючи макрос Auto_open і скидаючи її на аркуш “ComboData”. Незалежно від того, знаходиться Сервер у хмарі або локально, помітних затримок у s не будеtarting Excel - доки база даних доступна з робочої станції.
Далі ми витягнемо відфільтровані дані з бази даних і помістимо їх у Excel, стовпці F - K.
Інтерфейс
У Mine є випадаючі вікна для фільтрування інформації з бази даних. Роль поле зі списком запускає пошук для заповнення таблиці праворуч.
Перейменуйте “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 library
Використовуйте Інструменти> Посилання у вікні коду для посилання на бібліотеку об’єктів даних Microsoft Active Xrarр. Це дозволить Excel використовувати об'єкти ADODB, заявлені в коді.
Підпрограма ReadData вище використовує реляційну структуру даних, показану нижче, яку важко досягти лише в Excel.
Подальші зміни даних можуть викликати зворотний запис у базу даних із відповідним оператором SQL Update, за яким слід connDB.execute (strSQL).
Нарешті, захистіть свій код від перегляду або зміни: Інструменти> Властивості> Захист.
Вирішення проблем Excel:
Час від часу, особливо коли в ньому є складні програми, Excel може аварійно завершити роботу та не покрити належним чином. У разі виникнення пошкоджений xlsx файл, маючи ефективний інструмент відновлення зручний вирішить most проблеми.
Вступ автора:
Фелікс Хукер - фахівець з відновлення даних у DataNumen, Inc., яка є світовим лідером у галузі технологій відновлення даних, в тому числі ремонт rar помилка та програмні продукти для відновлення sql. Для отримання додаткової інформації відвідайте WWW.datanumen.com