Excel может делать практически все; нужно ли его заставить делать все это другой вопрос. Хотя электронная таблица очень эффективна в манипулировании данными, она не слишком хороша для хранения нормализованных данных. Использование Excel в реляционной базе данных, например SQL Server увеличивает мощность приложения.
К starт с, вам понадобится MS Access или более стабильный и бесплатный — SQL Server Выражать. Предполагается, что у читателя отображается лента Excel Developer и он знаком с редактором VBA и языком структурированных запросов (SQL). В этой статье используется SQL Server строки подключения. Для MS-Access обратитесь к Google.
Хотя в Excel есть собственные встроенные процедуры для получения информации из SQL Server в (скажем) сводную таблицу, наш пример даст больше гибкости в выборе данных.
Строка подключения
Я буду использовать частную базу данных; вставьте свою собственную информацию о драйвере вместо моей в подпрограмме ConnectDatabase. Затем мы используем коннДБ в качестве канала связи с нашей базой данных — в моем случае для возврата результатов хранимой процедуры. Вы можете использовать более стандартные операторы SQL, такие как «Выбрать * из…»
Порядок работы
Во-первых, мы загрузим варианты выбора из поля со списком из SQL Server когда рабочая книга открывается, используя макрос Auto_open и выгружая его на лист «ComboData». Независимо от того, находится ли сервер в облаке или локально, заметной задержки в s не будет.tarting Excel – до тех пор, пока база данных доступна с рабочей станции.
Далее мы будем извлекать отфильтрованные данные из базы данных и помещать их в Excel, столбцы от F до K.
Интерфейс
У меня есть раскрывающиеся списки для фильтрации информации из базы данных. Роли Поле со списком запускает поиск для заполнения таблицы справа.
Переименуйте «Лист1» в «Основной». Добавьте хотя бы одно поле со списком.
Кодекс
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).
Ссылки на библиотеку dllrary
Используйте Tools>References в окне кода, чтобы сослаться на библиотеку Microsoft Active X Data Objects.rarу. Это позволит Excel использовать объекты ADODB, объявленные в коде.
Вышеупомянутая подпрограмма ReadData использует реляционную структуру данных, показанную ниже, которую трудно реализовать только в Excel.
Дальнейшие изменения данных могут вызвать обратную запись в базу данных с соответствующим оператором обновления SQL, за которым следует connDB.execute(strSQL).
Наконец, защитите свой код от просмотра или изменения: Инструменты>Свойства>Защита.
Решение проблем с Excel:
Время от времени, особенно когда он содержит сложные программы, Excel может аварийно завершить работу и не восстановиться должным образом. В случае поврежденный xlsx файл, имея под рукой эффективный инструмент восстановления,ost проблемы.
Об авторе:
Феликс Хукер — эксперт по восстановлению данных в DataNumen, Inc., которая является мировым лидером в области технологий восстановления данных, включая ремонт rar ошибка и программные продукты для восстановления sql. Для получения дополнительной информации посетите www.datanumen.com