| BiVANT Книги К началу главы |
Глава 22(2)
Теперь мы готовы к рассмотрению реализации бланка заказа с новой БД. Базу данных офиса "РР" мы благополучно перенесли из Access на Excel. Как с ней работать, мы знаем. Бланк создан, дело за малым,— осталось перепрограммировать кнопки!
Эта кнопка позволяет выбрать из БД реквизиты заказчика. Щелчок кнопки открывает диалоговую форму, содержащую список с названиями организаций-заказчиков. После выбора заказчика из списка и щелчка командной кнопки "Выбери Меня" из БД автоматически выбираются реквизиты этого заказчика и заполняются соответствующие поля бланка. Мы сохраним весь этот внешний интерфейс, но реализацию изменим. Как можно будет увидеть, многие вещи на Excel решаются гораздо проще. Например, достаточно сложный обработчик командной кнопки "Выбрать" записывается буквально в одну строку. Теперь он имеет вид:
Private Sub SelectFromBase_Click()
frmCustomers.Show
End Sub
Вся его работа теперь сводится к открытию диалоговой формы. Ранее нам приходилось открывать БД Access, выполнять стандартный запрос и, работая с DAO объектом переписать в список диалоговой формы полученное в результате запроса множество записей. Заполнение списка происходило программным путем с использованием метода AddItem. Вообще, решение задачи требовало серьезного программирования. При работе со списком Excel подобные стандартные запросы реализуются автоматически, — достаточно изменить одно свойство у списка диалоговой формы (элемента класса ListBox). При открытии формы список будет формироваться автоматически, если в его свойстве RowSource указать источник (объект Range) для заполнения строк списка. В нашем случае источником является таблица "Заказчики", а точнее один из ее столбцов, хранящий названия организаций - заказчиков. Так что свойство RowSource имеет следующее значение:
Заказчики!B2:Заказчики!B102
Мы задали диапазон с учетом возможного роста таблицы в будущем. Хотя список в форме состоит из 100 элементов, большая часть которых пуста, эффективность от этого не снижается: записи не дублируются, а обработку при желании всегда можно построить так, чтобы учитывались только непустые элементы. Зато никаких забот о формировании списка!
Вторую и основную часть работы по заполнению полей бланка выполнял обработчик кнопки "Выбери Меня". Его реализация требовала формирования запроса к БД, осуществляющего в БД (в таблице "Заказчики") поиск записи с фиксированным значением в поле "Название". Данные, полученные в результате запроса, переносились затем из записи в ячейки листа бланка. Этой схеме мы будем следовать и сейчас. Только запрос теперь будет формироваться не на SQL, а с использованием метода AdvancedFilter. Помните, насколько сложные запросы можно реализовать этим методом? Наш случай является простейшим. Здесь поиск идет по одному полю и требуется найти точное соответствие образцу. Единственная сложность состоит в том, чтобы правильно выделить области под критерий и результаты поиска. С этой целью мы выделяем область непосредственно на листе, содержащем таблицу, в которой идет поиск. Рабочий лист "Заказчики" выглядит так:
{Рис. 22_5. Область рабочего листа, содержащая запрос и его результаты}
Приведем теперь текст самого обработчика:
Private Sub
ВыбериМеня_Click()
'Формирование
области критериев запроса
Sheets("Заказчики").Activate
If Not IsEmpty(lstCustomers.Value)
Then
Range("K3")
= lstCustomers.Value
'А
теперь сам запрос!
Range("таблЗаказчики").AdvancedFilter
Action:=xlFilterCopy, _
CriteriaRange:=Range("K2:K3"), CopyToRange:=Range("M2:T2")
'Заполнение
полей бланка.
'Обмен
данными между рабочими листами
Range("data2")
= Range("Заказчики!N3")
Range("data3")
= Range("Заказчики!O3")
Range("data4")
= Range("Заказчики!P3")
Range("data5")
= Range("Заказчики!Q3")
Range("data6")
= Range("Заказчики!S3")
Unload
Me
Else
MsgBox
("Выберите Заказчика")
End If
Sheets("БланкЗаказа").Activate
End Sub
Надеемся, метод AdvancedFilter пояснять не нужно, а обмен данными между ячейками двух рабочих листов совершенно естественен.
Сейчас мы добавим данные в таблицу Excel. На данном этапе это главная проблема. И дело не в самом переносе данных, а в том, чтобы определить, где реально заканчивается таблица, которая может быть объявлена на максимальное число элементов. Решить эту задачу можно по-разному. Например, сохранять текущую длину таблицы. Мы же использовали метод CurrentRegion Range-объекта, возвращающий область Range-объекта, окаймленную пустыми строками и столбцами. Это работает, если выполняется правило об отсутствии записей, все поля которых пусты. Метод выделит реально занятую область таблицы (объект Range), а затем, используя свойства Rows и Count, мы получим индекс последней строки. Вот текст соответствующей процедуры:
Public Sub
SaveToBase_Click()
Sheets("Заказчики").Activate
Dim lastRow As
Integer
Dim adrRow As
String, adrCol As String
'Определение
"настоящего" конца таблицы данных
lastRow =
ActiveSheet.Range("таблЗаказчики").CurrentRegion.Rows.Count
adrRow = LTrim(Str(lastRow
+ 1))
'Добавление
новой записи
adrCol = "B"
+ adrRow
ActiveSheet.Range(adrCol)
= Range("data2")
adrCol = "C"
+ adrRow
ActiveSheet.Range(adrCol)
= Range("data3")
adrCol = "D"
+ adrRow
ActiveSheet.Range(adrCol)
= Range("data4")
adrCol = "E"
+ adrRow
ActiveSheet.Range(adrCol)
= Range("data5")
adrCol = "G"
+ adrRow
ActiveSheet.Range(adrCol)
= Range("data6")
Sheets("БланкЗаказа").Activate
End Sub
В предыдущей реализации возникали некоторые сложности в заполнении списка, состоящего из двух столбцов. Сейчас их не будет, так как свойство RowSource позволяет в качестве источника данных указывать диапазон, состоящий из нескольких столбцов. Значения установленных свойств списка и сам список, состоящий из двух столбцов, представлены на рисунке:
{Рис. 22_6 Список "Книги Редакции" и его свойства}
Так как заполнение списка происходит автоматически, реализация обработчика занимает одну строку:
Private Sub Книги_Click()
frmBooks.Show
End Sub
Основную работу выполняет обработчик командной кнопки "Выбери Нас", который вступает в действие по завершении выбора книг из списка и выбора кнопки, расположенной в открывшемся диалоговом окне. С идейной точки зрения, в нем ничего нового, все основные моменты, так или иначе, мы рассмотрели. Мы знаем, как:
· используя метод AdvancedFilter, выполнить запрос к таблице БД Excel на получение данных о выбранных книгах;
· организовать цикл по числу заказанных книг;
· в цикле передать записи о книгах, полученные в ходе запроса в таблицу заказов. Заметим, по ходу выполнения этой операции требуется вычисление текущих адресов ячеек.
Конечно, есть некоторые важные детали, характерные для этого обработчика. Его текст позволит проследить за всеми подробностями:
Private Sub ВыбериНас_Click()
Dim intLoop As
Integer, intSelect As Integer
Dim ВыборСделан As Boolean
Dim KeyAuthor As
String, KeyName As String
Dim strSelect As
String
Dim strAdr As
String, strAddress As String
Dim strAdr1 As
String, strAddress1 As String
ClearTable
Sheets("Книги").Activate
ВыборСделан = False
intLoop = 0
intSelect = 0
'Начало цикла поиска выделенных элементов в списке
lstBooks
Do
If
frmBooks.lstBooks.Selected(intLoop) Then
'Найден
очередной
элемент
ВыборСделан
= True
intSelect = intSelect + 1
KeyAuthor = frmBooks.lstBooks.Column(0, intLoop)
KeyName = frmBooks.lstBooks.Column(1, intLoop)
'Формирование
условия
выбора
в области
критериев
strAdr = "M" + LTrim(Str(intSelect + 2))
ActiveSheet.Range(strAdr) = KeyAuthor
strAdr = "N" + LTrim(Str(intSelect + 2))
ActiveSheet.Range(strAdr) = KeyName
End If
intLoop
= intLoop + 1
Loop Until intLoop
= frmBooks.lstBooks.ListCount
'Формирование диапазона области критериев
strAdr =
"M2:" + strAdr
'Запрос к базе
данных
Range("таблКниги").AdvancedFilter
Action:=xlFilterCopy, _
CriteriaRange:=Range(strAdr), CopyToRange:=Range("Q2:Z2")
'Цикл по книгам
заказа
For i = 1 To
intSelect
'Формирование адресов ячеек, задающих
поля
электронного
бланка
strAdr
= LTrim(Str(30 + i))
strAdr1
= LTrim(Str(2 + i))
strAddress
= "E" & strAdr
strAddress1
= "S" & strAdr1
Sheets("БланкЗаказа").Range(strAddress)
= Sheets("Книги").Range(strAddress1)
strAddress
= "C" & strAdr
strAddress1
= "R" & strAdr1
Sheets("БланкЗаказа").Range(strAddress)
= Sheets("Книги").Range(strAddress1)
strAddress
= "I" & strAdr
strAddress1
= "V" & strAdr1
Sheets("БланкЗаказа").Range(strAddress)
= Sheets("Книги").Range(strAddress1)
strAddress
= "J" & strAdr
strAddress1
= "W" & strAdr1
Sheets("БланкЗаказа").Range(strAddress)
= Sheets("Книги").Range(strAddress1)
strAddress
= "B" & strAdr
Sheets("БланкЗаказа").Range(strAddress)
= i
Next i
Sheets("БланкЗаказа").Activate
ClearTable (intSelect)
'Включение кнопки "Сохранить
Заказ"
Dim myButton As
CommandButton
Set myButton =
ActiveSheet.OLEObjects(7).Object
myButton.Enabled =
True
If ВыборСделан Then
Unload
Me
Else
MsgBox
("Выберите
книги")
End If
End Sub
Осталось привести новую реализацию обработчика, сохраняющего заказ в таблицах БД "Заказы" и "Заказано". Работа с двумя таблицами усложняет задачу, но ничего принципиально нового здесь нет. Поэтому мы просто приведем текст процедуры:
Private Sub
SaveOrder_Click()
'Сохраняет
заказ на книги в таблицах Заказы и Заказано
'Определение
элементов управления, хранящих нужные
данные
Dim myDate As
Object
Dim myCombo As
ComboBox
Dim OrderCode As
Long
Set myCombo =
Sheets("БланкЗаказа").OLEObjects(5).Object
Set myDate =
Sheets("БланкЗаказа").OLEObjects(6).Object
'Добавление
новой записи в таблицу "Заказы"
'В ее поля
переносятся данные из ячеек и элементов
управления бланка
Sheets("Заказы").Activate
Dim lastRow As
Integer
Dim adrRow As
String, adrCol As String
lastRow =
ActiveSheet.Range("таблЗаказы").CurrentRegion.Rows.Count
If lastRow = 1
Then
OrderCode
= 1
Else
adrRow
= "A" & LTrim(Str(lastRow))
OrderCode
= ActiveSheet.Range(adrRow) + 1
End If
adrRow = LTrim(Str(lastRow
+ 1))
'Добавление
новой записи
adrCol = "A"
+ adrRow
ActiveSheet.Range(adrCol)
= OrderCode
adrCol = "B"
+ adrRow
ActiveSheet.Range(adrCol)
= Range("data2")
adrCol = "C"
+ adrRow
ActiveSheet.Range(adrCol)
= myCombo.Text
adrCol = "D"
+ adrRow
ActiveSheet.Range(adrCol)
= myDate.Caption
adrCol = "E"
+ adrRow
ActiveSheet.Range(adrCol)
= Sheets("БланкЗаказа").Range("Итого")
Sheets("Заказано").Activate
'Добавление
новых записей в таблицу "Заказано"
'Каждая
запись соответствует одной строке таблицы
заказов
Dim i As Integer
Dim strAdr As
String, strAdr1 As String
lastRow = Sheets("Заказано").Range("таблЗаказано").CurrentRegion.Rows.Count
i = 1
'Цикл по
строкам таблицы заказов
Do
strAdr
= "B" & LTrim(Str(30 + i))
If
IsEmpty(Range(strAdr)) Then Exit Do
'иначе
строка записывается в базу данных
strAdr1
= "A" & LTrim(Str(lastRow + i))
Sheets("Заказано").Range(strAdr1)
= OrderCode
strAdr
= "E" & LTrim(Str(30 + i))
strAdr1
= "B" & LTrim(Str(lastRow + i))
Sheets("Заказано").Range(strAdr1)
= Sheets("БланкЗаказа").Range(strAdr)
strAdr
= "H" & LTrim(Str(30 + i))
strAdr1
= "C" & LTrim(Str(lastRow + i))
Sheets("Заказано").Range(strAdr1)
= Sheets("БланкЗаказа").Range(strAdr)
strAdr
= "K" & LTrim(Str(30 + i))
strAdr1
= "D" & LTrim(Str(lastRow + i))
Sheets("Заказано").Range(strAdr1)
= Sheets("БланкЗаказа").Range(strAdr)
i = i
+ 1
Loop
Sheets("БланкЗаказа").Activate
'Выключение
кнопки "Сохранить Заказ"
Dim myButton As
CommandButton
Set myButton =
Sheets("БланкЗаказа").OLEObjects(7).Object
myButton.Enabled =
False
End Sub
Итак, во многих случаях БД с успехом можно вести на Excel. Это особенно удобно, если сам документ представляет рабочую книгу Excel. Средства Excel позволяют осуществлять запросы к БД, пополнять и корректировать хранящиеся в ней записи. Конечно, при росте объема БД может встать вопрос о переходе к другой, более мощной БД. Мы рассказывали о преобразовании БД Access (ее таблиц) в БД (списки) Excel. Теперь посмотрим, как осуществить обратное преобразование, но сначала обговорим один технический вопрос.
Назад
Вперед 
