Создание интерактивных офисных документов в Excel

Раздел "Реквизиты заказчика"

Сама по себе задача формирования этой части бланка достаточно простая. Необходимо в соответствующие поля бланка занести информацию о покупателе, - его реквизиты. Однако, учитывая, что большинство заказчиков офиса РР давно с ним сотрудничают, то информация о них хранится в базе данных. Поэтому, как уже говорилось, не следует заново заполнять соответствующие поля вручную, а следует перенести в них данные, взятые из базы данных.

С другой стороны, при появлении нового заказчика данные о нем поневоле придется вводить вручную в поля бланка, создавая тем самым новую информацию. Крайне важно, чтобы новая информация не пропала, а сохранилась в базе данных. Это общие принципы работы с интерактивными документами.

Для организации обмена данными между документом и базой данных давайте добавим в раздел четыре командные кнопки, по нажатию которых будут решаться следующие задачи:

Вот как выглядит теперь этот раздел документа с добавленными кнопками:

Рассмотрим подробнее суть каждой из этих задач, и то, что должен делать обработчик события, происходящего при нажатии соответствующей командной кнопки.

Выбор заказчика

Я напомню, что в спроектированной в предыдущих главах базе данных офиса РР хранятся сведения о заказчиках, в частности, есть таблица "Заказчики", имеются и стандартные запросы, связанные с заказчиками. Чтобы нужные данные извлечь из базы данных и затем перенести в поля бланка, первым делом нужно задать ключ поиска. Проще всего в качестве такового указать название организации, напомню, что это поле является ключевым для таблицы "Заказчики". Поэтому один из возможных подходов к решению проблемы состоит в том, чтобы сформировать список всех организаций, хранящихся в базе данных, и дать возможность пользователю выбрать нужную организацию. Выбор пользователя определяет ключ поиска, а уж извлечение по ключу данных из базы и перенос их в поля бланка является делом техники.

Таким образом, задача выбора заказчика сводится к следующим подзадачам:

А теперь взгляните, как выглядит обработчик события Click командной кнопки "Выбрать", вызываемый при нажатии этой кнопки:

Private Sub CommandButton1_Click()
	'выбор заказчика из базы данных
	Choose
End Sub	
Public Sub Choose()
	'Данные о заказчиках выбираются из базы данных
	CreateListCustomers
	FormListCustomers
	Customers.Show
End Sub

Сохраняя свой стиль, обработчик события свожу к вызову соответствующей процедуры стандартного модуля. Замечу, что в этом стиле имеется и определенный резон, поскольку процедуру, заданную обработчиком, можно вызывать не только при нажатии командной кнопки. В частности, именно так обстоит дело с процедурой Choose, которая будет вызываться по ходу решения задачи в одной из процедур стандартного модуля.

Сама по себе процедура Choose не решает полностью задачу выбора заказчика. Две процедуры, вызываемые в процедуре Choose, решают лишь первую подзадачу, завершая свои действия показом формы со списком. Дальнейшие действия определяются пользователем и зависят от его выбора. Но, прежде чем говорить о решении других подзадач, давайте более подробно разберемся с тем, что делается в процедуре Choose.

Формирование и показ списка

Чтобы была возможность показать пользователю список всех заказчиков, я спроектировал форму с именем Customers, в которую поместил два элемента управления - список и командную кнопку. Вот как выглядит эта форма в процессе работы:

Решать задачу формирования списка нужно начинать с получения из базы данных набора записей, содержащих названия заказчиков. Возможно, Вы помните, что в спроектированной нами базе данных содержится стандартный запрос с именем "Список заказчиков", решающий именно эту задачу. Уже тогда, при проектировании базы было понятно, что такой запрос может понадобиться. Поэтому теперь единственное, что нужно сделать, - получить набор записей, соответствующий этому запросу. Как это реализовать с помощью объектов ADO, - подробно описывалось в предыдущих главах. Так что осталось взглянуть на текст соответствующей процедуры CreateListCustomers, которая первой вызывается в процедуре Choose:

Public Sub CreateListCustomers()
	'Создание	и выполнение команды,
	'позволяющей получить данные о заказчиках
	Dim strSQL1 As String
	strSQL1 = "Select * FROM [Список заказчиков]"
	'задание объекта Command
	Cmd1.CommandText = strSQL1
	'вызов команды на исполнение методом Execute
	Set Rst1 = Cmd1.Execute
End Sub

Как видите, получить нужный набор записей достаточно просто. Конечно, нужно иметь в виду, что глобальные объекты Con1, Cmd1, Rst1 уже созданы, соединение с базой данных и конфигурирование команды - объекта Cmd1 выполнено при инициализации документа. Так что в данной процедуре осталось задать лишь текст команды, который в данном случае сводится к выполнению стандартного запроса "Список заказчиков". При выполнении команды формируется объект Rst1, который и содержит названия заказчиков.

Назначение следующей процедуры состоит в том, чтобы данные из набора записей - объекта Rst1 - перенести в список спроектированной формы Customers. Все делается совершенно просто и естественно:

Public Sub FormListCustomers()
	'Перенос данных о заказчиках из набора записей
	'в список формы Customers
	 With Rst1
		.MoveFirst
		Customers.ListBox1.Clear
		Do While Not .EOF
			'Текущая запись переносится в список
			Customers.ListBox1.AddItem .Fields(0)
			.MoveNext
		Loop
	End With
End Sub

Заметьте, схема прохождения набора записей обсуждалась в главе 5, а о работе с элементами управления, в частности, с объектами класса ListBox я подробно рассказывал в предыдущих книгах этой серии, например, в книге [1]. Может быть, стоит лишь напомнить, что запрос "Список заказчиков" возвращает запись, содержащую одно поле с названием организации заказчика. Содержимое этого поля и переносится в список.

Теперь, когда данные о заказчиках получены, список с названиями организаций сформирован, осталось предъявить форму Customers пользователю. Эту операцию и выполняет метод Show объекта Customers, что отражено в последнем выполняемом операторе процедуры Choose. Форма, появляющаяся на экране и показана на рис. 7.3. В открывшейся форме пользователь может выбрать из списка нужного заказчика, после чего нажать командную кнопку "Выбери меня". Обработчик события Click этой кнопки и отвечает за последующие действия, позволяя решить следующую упоминавшуюся подзадачу - поиск по ключу в базе данных сведений о реквизитах выбранного заказчика. Вот текст этого обработчика:

Private Sub CommandButton1_Click()
	'Данные о заказчике, выбранном из списка,
	'ищутся в базе данных и затем переносятся
	'из набора записей в поля бланка Счет-фактура
	FromListToFields
	
End Sub

Содержательные действия выполняет процедура FromListToFields, которая и реализует решение подзадачи по формированию запроса на поиск.

Формирование запроса на поиск

Выбрав заказчика из списка, пользователь тем самым задает ключ, позволяющий отобрать нужную запись в таблице базы данных "Заказчики". Формирование запроса на поиск в базе данных, когда ключ поиска известен, технически сводится к корректному заданию SQL-оператора. Получение набора записей по динамически сформированному запросу принципиально не сложнее, чем уже рассмотренная задача, когда использовался стандартный запрос. Вот как все это решается в процедуре FromListToFields:

Public Sub FromListToFields()
	'Данные о заказчике, выбранном из списка,
	'ищутся в базе данных и затем переносятся
	'из набора записей в поля бланка Счет-фактура.
	Const Кавычка = "'"
	Dim Key As String
	Dim strSQL1 As String
	If Customers.ListBox1.ListIndex >= 0 Then 'Выбор сделан.
		'Формирование запроса к базе данных.
		Key = Customers.ListBox1.List(Customers.ListBox1.ListIndex)
		strSQL1 = "Select * FROM [Заказчики]WHERE [Название]= " _
			 Кавычка  Key  Кавычка
		'Изменение описания команды.
		Cmd1.CommandText = strSQL1
		Set Rst1 = Cmd1.Execute
		
		'Перенос данных из набора записей в поля бланка.
		FromRstToFields
	 'Форма сделала свое дело - форма закрывается.
	 Customers.Hide
	Else
		MsgBox ("Выберите заказчика!")
	End If
End Sub

Как видите, все происходит по задуманному плану. Формируется строка с ключом, строка с SQL-запросом, изменяется текст команды у объекта Command и при запуске его метода Execute формируется нужный набор записей, состоящий в данном случае из одной записи, содержащей все реквизиты заказчика.

Вызов процедуры FromRstToFields позволяет решить последнюю подзадачу в этом пункте по переносу данных из полей текущей записи объекта Rst1 в поля бланка. Последний выполняемый оператор этой процедуры - Customers.Hide прячет форму Customers, которая сделала свое дело и должна быть закрыта.

Перенос результатов запроса в поля бланка

Приведу вначале текст процедуры FromRstToFields, а потом уже дам краткий комментарий, поясняющий ее работу:

Public Sub FromRstToFields()
'Перенос данных из набора записей Rst1 в поля бланка
	Dim curField As Range
	VBA.Randomize
	Set curField = Range("D19")	'Поле с названием организации
	curField = Rst1!Название
	curField.Offset(1) = Rst1!Адрес
	curField.Offset(2) = "tel: "  Rst1!Телефон  _
		"	Email: "  Rst1!Email
	curField.Offset(3) = VBA.Int(VBA.Rnd * 999999999 + 100000000)
	'Заполнение полей - грузотправитель и грузополучатель
	Set curField = Range("E25:J25")
	curField = "Издательство: Родная Речь, "  Range("F9")
	curField.Offset(1) = Rst1!Название  ", "  Rst1!Адрес
End Sub

Процедура, как и положено, коротенькая, так что текст ее самодостаточен для понимания. Значения полей записи присваиваются свойству Value объектов Range, задающих поля бланка. В последней части процедуры заполняются поля документа, задающие грузоотправителя и грузополучателя, в качестве которых выступает офис РР и выбранный заказчик книг.

На этом можно было бы закончить комментарий, если бы не необходимость привести некоторые пояснения моим читателям. Дело в том, что я проектировал бланк документа и базу данных в разное время. По этой причине есть определенная несогласованность между реквизитами заказчика, представленными на бланке и реквизитами, хранящимися в таблице "Заказчики" базы данных офиса РР. В частности в базе данных хранятся такие реквизиты, как, например, "Город", "Директор", которые не вынесены на бланк, но нет реквизита "ИНН", присутствующего на бланке. Я не стал приводить в полное соответствие формат бланка документа и базу данных, найдя в этом даже некоторую пользу для целей обучения. Примером того, что некоторые поля документа могут быть вычислимыми, в данном случае является поле "ИНН", которое я формирую случайным образом, используя функции Randomize и Rnd.

На этом закончу рассмотрение действий, выполняемых при нажатии командной кнопки "Выбрать" и перейду к рассмотрению задач, решение которых осуществляется по нажатию следующей инструментальной кнопки "Найти".

Поиск заказчика

В предыдущем разделе я подробно описал, как можно перенести данные из базы данных в поля документа. Решение было основано на том, что пользователю, заполняющему документ, предъявлялся некоторый список, выбор из которого задавал ключ поиска нужных сведений в базе данных. Иногда приходится более сложным образом задавать ключ поиска. Именно такую ситуацию я хочу сейчас продемонстрировать на примере поиска заказчика, название организации которого может быть неизвестно, а есть лишь некоторые отрывочные сведения. Предположим, что требуется найти заказчика по таким сведениям, как телефон или фамилия директора, или по названию города, или по частично известному названию организации.

Для решения задачи в такой постановке и служит инструментальная кнопка "Найти". Приведу код обработчика события, возникающего при нажатии этой кнопки:

Private Sub CommandButton3_Click()
	'Поиск заказчика
	'Открыть форму для задания реквизитов поиска
	LookCustomer.Show
End Sub

Вся обработка события сводится к открытию специальной формы, в полях которой можно задавать ключи поиска. Так что первое, с чего я начал при решении этой задачи, это спроектировал форму с именем "LookCustomer", в текстовых полях которой пользователь задает известную ему информацию, которая и служит ключом поиска. Вот как выглядит эта форма в процессе работы с ней:

В приведенном на рисунке примере будут отобраны все заказчики, в названии которых содержится слово "лавка" или директором которых является "Рондова", или заказчики, расположенные в Твери. Заметьте, не обязательно задание всех полей формы, достаточно задание хотя бы одного поля. Обращаю внимание и на мое решение о том, что при поиске поля будут объединяться логической связкой " ИЛИ ", а не " И ".

Нажатие кнопки "Найти" в форме LookCustomer инициирует начало поиска. Взгляните на код обработчика события Click:

Private Sub CommandButton1_Click()
	'Найти заказчика по заданным реквизитам
	LookingFor
End Sub

Реальную работу выполняет процедура LookingFor стандартного модуля. Вот ее текст:

Public Sub LookingFor()
 'Найти заказчика по заданным реквизитам
	If (LookCustomer.TextBox1 <> "") Or (LookCustomer.TextBox2 <> "") Or _
		(LookCustomer.TextBox3 <> "") Or (LookCustomer.TextBox4 <> "") Or _
		(LookCustomer.TextBox5 <> "") Then
		'Критерии поиска заданы.
		'Спрятать форму.
		LookCustomer.Hide
		'Создать набор записей с реквизитами заказчиков.
		CreateCustomers
		'Сформировать список заказчиков, удовлетворяющих критериям поиска.
		FormListSelectedCustomers
		If SelectedCustomers.ListBox1.ListCount > 0 Then
			'Найдены заказчики, удовлетворяющие критериям.
			SelectedCustomers.Show
		Else
			'Показ всех заказчиков.
			MsgBox ("Нет записей, удовлетворяющих заданным критериям!" _
				 " Будут показаны все заказчики!")
			Choose
		End If
	Else
		MsgBox ("Задайте значение хотя бы в одном поле!")
	End If
End Sub

Приведу краткие комментарии к работе этой процедуры:

Public Sub CreateCustomers()
	'Создание	и выполнение команды,
	'позволяющей получить данные о заказчиках
	Dim strSQL1 As String
	strSQL1 = "Select * FROM [Заказчики]"
	'задание объекта Command
	Cmd1.CommandText = strSQL1
	'вызов команды на исполнение методом Execute
	Set Rst1 = Cmd1.Execute
End Sub

Формирование и показ списка отобранных заказчиков

Эта задача является некоторым усложнением аналогичной задачи, когда показывался список всех заказчиков. Принципиально задача остается той же. После получения набора записей нужно сформировать список для предъявления его пользователю. Усложнения касаются двух моментов:

Приведу текст процедуры, которая решает эту задачу:

Public Sub FormListSelectedCustomers()
	'Готовит список заказчиков, удовлетворяющих критериям поиска
	Dim txt As String
	Dim i As Integer
	Dim RowIndex As Integer
	Dim B1 As Boolean, B2 As Boolean, B3 As Boolean
	Dim B4 As Boolean, B5 As Boolean
	'Число столбцов списка - реквизитов поиска
	Const ColumnCount = 5
	
	'Поиск заказчиков в наборе по заданным реквизитам
	SelectedCustomers.ListBox1.Clear
	SelectedCustomers.ListBox1.ColumnCount = ColumnCount
	RowIndex = 0
	With Rst1
		.MoveFirst
		Do While Not .EOF
			B1 = False: B2 = False: B3 = False: B4 = False: B5 = False
			On Error Resume Next
			txt = LookCustomer.TextBox1.Text
			B1 = txt <> "" And VBA.InStr(!Название, txt)
			txt = LookCustomer.TextBox2.Text
			B2 = txt <> "" And VBA.InStr(!Адрес, txt)
			txt = LookCustomer.TextBox3.Text
			B3 = txt <> "" And VBA.InStr(!Город, txt)
			txt = LookCustomer.TextBox4.Text
			B4 = txt <> "" And VBA.InStr(!Телефон, txt)
			txt = LookCustomer.TextBox5.Text
			B5 = txt <> "" And VBA.InStr(!Директор, txt)
			If B1 Or B2 Or B3 Or B4 Or B5 Then
				'Текущая запись переносится в список
				'Первый столбец
				SelectedCustomers.ListBox1.AddItem .Fields(1)
				'Остальные столбцы
				For i = 1 To ColumnCount - 1
					txt = ""
					txt = .Fields(i + 1)
					SelectedCustomers.ListBox1.Column(i, RowIndex) = txt
				Next i
				RowIndex = RowIndex + 1
			End If
			.MoveNext
		Loop
	End With
	
End Sub

Фильтр, через который проходят все записи, состоит из пяти условий, объединенных операцией " ИЛИ ". Каждое условие определяет, задан ли ключ для определенного поля записи, и, если да, то является ли ключевое слово частью поля записи. Записи, прошедшие фильтр, добавляются в список формы SelectedCustomers, при этом поля записи переносятся в соответствующие столбцы списка. Обратите внимание на оператор обработки исключительных ситуаций - On Error Resume Next - позволяющий справиться с возможной ошибкой при выполнении функции InStr, возникающей в ситуации, когда некоторые из полей записи базы данных не определены.

В заключение, взгляните, как выглядит спроектированная форма SelectedCustomers в процессе работы:

Поскольку записей, прошедших фильтр, может быть несколько, то и здесь окончательный выбор остается за пользователем, работающим с документом. Вот что происходит, когда, сделав выбор в списке, он нажимает кнопку "Выбери меня" формы SelectedCustomers:

Private Sub CommandButton1_Click()
	'Данные о реквизитах заказчика переносятся
	'из набора записей в поля бланка Счет-фактура
	FromSelectedListToFields
	
End Sub

Процедура FromSelectedListToFields, выполняющая основную работу, похожа на уже рассмотренную процедуру FromListToFields. Разница состоит лишь в том, что работа идет с другой формой:

Public Sub FromSelectedListToFields()
	'Данные о заказчике, выбранном из списка,
	'ищутся в базе данных и затем переносятся
	'из набора записей в поля бланка Счет-фактура
	Const Кавычка = "'"
	Dim Key As String
	Dim strSQL1 As String
	
	If SelectedCustomers.ListBox1.ListIndex >= 0 Then 'Выбор сделан
		'Формирование запроса к базе данных
		Key = SelectedCustomers.ListBox1.Column _
				(0, SelectedCustomers.ListBox1.ListIndex)
		strSQL1 = "Select * FROM [Заказчики]WHERE [Название]= " _
			 Кавычка  Key  Кавычка
		Cmd1.CommandText = strSQL1
		Set Rst1 = Cmd1.Execute
		'Перенос данных из набора записей в поля бланка
		FromRstToFields
	 'Форма сделала свое дело - форма закрывается
	 SelectedCustomers.Hide
	Else
		MsgBox ("Выберите заказчика!")
	End If
End Sub

На этом можно было бы закончить описание того, как работает поиск с фильтрацией в базе данных. Весь процесс поиска и получения нужных данных рассмотрен. Но для полноты картины следует проанализировать другой, альтернативный способ фильтрации записей.

Запросы с фильтрацией

Возможно, Вы обратили внимание на то, как я решал данную задачу по организации поиска нужных мне данных, - вначале получил весь набор записей, а затем организовал их фильтрацию средствами VBA. Возможен и другой способ, в ряде случаев более предпочтительный, - он состоит в том, чтобы фильтрацию выполнять в момент чтения набора записей, задав соответствующим образом оператор SELECT языка SQL. Оператор Select имеет в своем арсенале конструкцию Like, позволяющую задать шаблон поиска и тем самым проводить фильтрацию непосредственно при чтении записей, что может приводить к существенному уменьшению объема набора записей, передаваемого клиенту.

Давайте подробно рассмотрим этот вариант работы. Полагаю, это будет полезным, как с содержательной, так и с программистской точки зрения, поскольку, например, я сам не сразу разобрался, как корректно следует на VBA задавать конструкцию Like.

Начнем повторно наше рассмотрение с того момента, когда после задания критериев поиска пользователь нажимает кнопку "Найти" в форме LookCustomer. Я изменил код обработчика события Click для этой кнопки следующим образом:

Private Sub CommandButton1_Click()
	'Найти заказчика по заданным реквизитам
	'LookingFor
	Version2LookingFor
End Sub

Как видите, вместо ранее вызываемой процедуры LookingFor я буду вызывать ее версию, которую и рассмотрим подробно. Начнем с текста:

Public Sub Version2LookingFor()
 'Найти заказчика по заданным реквизитам
	If (LookCustomer.TextBox1 <> "") Or (LookCustomer.TextBox2 <> "") Or _
		(LookCustomer.TextBox3 <> "") Or (LookCustomer.TextBox4 <> "") Or _
		(LookCustomer.TextBox5 <> "") Then
		'Критерии поиска заданы.
		'Спрятать форму.
		LookCustomer.Hide
		'Создать набор отфильтрованных записей с реквизитами заказчиков.
		CreateFiteredCustomers
		'Сформировать список заказчиков, удовлетворяющих критериям поиска.
		FormListSelectedCustomers
		If SelectedCustomers.ListBox1.ListCount > 0 Then
			'Найдены заказчики, удовлетворяющие критериям.
			SelectedCustomers.Show
		Else
			'Показ всех заказчиков.
			MsgBox ("Нет записей, удовлетворяющих заданным критериям!" _
				 " Будут показаны все заказчики!")
			Choose
		End If
	Else
		MsgBox ("Задайте значение хотя бы в одном поле!")
	End If
End Sub

Я не буду подробно описывать работу этой процедуры, поскольку она, во многом, похожа на своего двойника - процедуру LookingFor. Главное отличие с программной точки зрения состоит в том, что вместо относительно простой процедуры CreateCustomers здесь вызывается более сложная процедура CreateFilteredCustomers. С содержательной точки зрения отличие состоит в том, что первая процедура создает полный набор всех заказчиков, а вторая - набор заказчиков, удовлетворяющих условию фильтра.

Приведу программный код процедуры CreateFilteredCustomers:

Public Sub CreateFiteredCustomers()
	'Создание	и выполнение команды,
	'позволяющей получить данные о заказчиках,
	'удовлетворяющих фильтру
	Dim strSQL1 As String
	'Вызов функции FormSQLStatement, формирующей строку SQL
	strSQL1 = FormSQLStatement
	'задание объекта Command
	Cmd1.CommandText = strSQL1
	'вызов команды на исполнение методом Execute
	Set Rst1 = Cmd1.Execute
	'Перенос данных из набора записей в список
	SelectedCustomers.ListBox1.Clear
	RowIndex = 0
	With Rst1
		.MoveFirst
		Do While Not .EOF
				'Текущая запись переносится в список
				'Первый столбец
				SelectedCustomers.ListBox1.AddItem .Fields(1)
				'Остальные столбцы
				On Error Resume Next
				 For i = 1 To ColumnCount - 1
					txt = ""
					txt = .Fields(i + 1)
					SelectedCustomers.ListBox1.Column(i, RowIndex) = txt
				Next i
				RowIndex = RowIndex + 1
			.MoveNext
		Loop
	End With
 End Sub

И в этой процедуре немного нового. Она построена по образцу уже приведенных выше процедур. Схематично действия, выполняемые в ней следующие: формируется строка SQL, задающая текст команды, создается команда - объект Command, команда выполняется, данные из набора записей, полученного в результате выполнения команды, переносятся в поля списка формы SelectedCustomers.

То новое, на что хочу обратить внимание, связано с формированием строки SQL. Как положено, я написал специальную функцию FormSQLStatement, которая и решает поставленную задачу. Именно она и интересует нас в первую очередь. Вот ее текст:

Public Function FormSQLStatement() As String
	'Возвращает SQL оператор, фильтрующий записи
	Dim strSQL As String
		strSQL = "Select * FROM [Заказчики] WHERE "
	Dim txt As String
	Const Кавычка = "'"
	txt = LookCustomer.TextBox1.Text
	If txt <> "" Then strSQL = strSQL  "[Название] Like "  _
		 Кавычка  "%"  txt  "%"  Кавычка  " Or "
	txt = LookCustomer.TextBox2.Text
	If txt <> "" Then strSQL = strSQL  "[Адрес] Like "  _
		Кавычка  "%"  txt  "%"  Кавычка  " Or "
	txt = LookCustomer.TextBox3.Text
	If txt <> "" Then strSQL = strSQL  "[Город] Like "  _
		Кавычка  "%"  txt  "%"  Кавычка  " Or "
	txt = LookCustomer.TextBox4.Text
	If txt <> "" Then strSQL = strSQL  "[Телефон] Like "  _
		Кавычка  "%"  txt  "%"  Кавычка  " Or "
	txt = LookCustomer.TextBox5.Text
	If txt <> "" Then strSQL = strSQL  "[Директор] Like "  _
		Кавычка  "%"  txt  "%"  Кавычка  " Or "
	'Удалить последние пять символов - Or
	strSQL = Left(strSQL, Len(strSQL) - 4)
	FormSQLStatement = strSQL
End Function

Поговорим подробнее о работе этой функции. Наша цель состоит в том, чтобы задать сложное выражение WHERE оператора Select, задающее фильтр. По условиям задачи это выражение состоит из нескольких слагаемых - логических условий, соединенных связкой OR (ИЛИ). Число слагаемых формируется динамически и может быть от одного до пяти, в зависимости от того, сколько полей заполнил пользователь в форме LookCustomer. Напомню, что хотя бы одно поле он обязан задать, чтобы можно было говорить о ключе поиска. Все слагаемые формируются по одной схеме, - если задано соответствующее ключевое поле в форме, то формируется и соответствующее ему слагаемое.

Каждое условие задается шаблоном, заданным с помощью конструкции Like, и имеет следующий вид - %текст%. Этот шаблон соответствует любой строке, содержащей вхождение строки текст в качестве подстроки. Символы шаблона %, окаймляющие подстроку текст, указывают на произвольный префикс и произвольное окончание искомой строки.

Вот, наверное, и все, что хотелось сказать о формировании строки фильтра. Но прежде, чем закончить эту тему сделаю два замечания, связанные с шаблонами.

Замечание 1. Шаблоны не новость ни в математике, ни в программировании. Традиционно, символ ? (знак вопроса) соответствует произвольному одиночному символу. Мне совершенно непонятно, почему в шаблонах на VBA нужно было изменять традиции и использовать другие символы - знак % (процент) вместо звездочки и знак _ (подчеркивание) вместо знака вопроса. Это тем более странно, что при формировании запроса с выражением Like непосредственно в Access используются традиционные символы.

Замечание 2. Экспериментируя с шаблонами, я наткнулся на одну ошибку в Access, связанную, видимо, с локализацией. Ошибка проявляется в следующей ситуации. Пусть в Access с помощью конструктора строится запрос по таблице, у которой первое поле является ключевым и содержит, скажем, название организации. Предположим, что на это поле накладывается фильтр типа Like - S* , где S - буква русского алфавита. Этот фильтр, естественно, означает, что запросу удовлетворяю организации, начинающиеся с буквы S . Так вот, все работает правильно за исключением, когда в качестве начальной буквы задается "Г" или "К". Замечу, что в других полях шаблон "Г*" работает правильно.

Сохранение реквизитов

Как я говорил ранее, иногда информация создается при формировании документа и тогда крайне желательно, чтобы она тут же была занесена в базу данных. Эта ситуация достаточно естественно появляется при работе с нашим документом. Действительно, представьте, что появился новый заказчик, о котором нет сведений в базе данных. Тогда данные о нем, - его реквизиты естественно придется вручную занести в соответствующие поля документа. Инструментальная кнопка "Сохранить" поможет перенести эту информацию в базу данных.

Следует заметить, что решение обратной задачи по переносу данных из документа в базу данных даже проще, чем рассматриваемая ранее прямая задача, и никаких особых новинок здесь не появляется. Вот ее программное решение:

Private Sub CommandButton2_Click()
	'Сохранить реквизиты заказчика
	CreateNewCustomer
End Sub

Процедура CreateNewCustomer, решающая задачу имеет вид:

Public Sub CreateNewCustomer()
	'добавление записи в базу данных
	Dim recExist As Boolean
	Dim curField As Range
	Dim txtField As String, txtTel As String, txtMail As String
	Cmd1.CommandText = "Select * From [Заказчики]"
	Set curField = Range("D19")	'Поле с названием организации
	txtField = curField.Text
	'Открытие обновляемого объекта Recordset
	With Rst1
		If Rst1.State = adStateOpen Then Rst1.Close
		.Open Source:=Cmd1, CursorType:=adOpenDynamic, _
			LockType:=adLockOptimistic
		
		recExist = False
		.MoveFirst
		Do While Not .EOF
			'Проверка существования записи
			If LCase(!Название) = LCase(txtField) Then recExist = True
			.MoveNext
		Loop
		If Not recExist Then
			 .AddNew
			!Название = txtField
			txtField = curField.Offset(1).Text
			!Адрес = txtField
			txtField = curField.Offset(2).Text
			Call Parsing(txtField, txtTel, txtMail)
			!Телефон = txtTel
			!Email = txtMail
			'Запрос значений недостающих полей
			AddingFields.Show
			.Update
		Else
			MsgBox ("В базе данных уже существует" _
			 " организация с таким названием!")
		End If
	End With
End Sub

На три момента хочу обратить внимание:

Public Sub Parsing(Field As String, Tel As String, Mail As String)
	'разбор поля бланка, содержащего телефон и EMail организации
	Dim Ind1 As Integer
	Ind1 = InStr(5, Field, "Email")
	Tel = Mid(Field, 5, Ind1 - 5)
	Mail = Right(Field, Len(Field) - Ind1 - 5)
End Sub

При нажатии кнопки "OK" формы AddingFields текст из полей формы переносится в поля записи, после чего форма закрывается:

Private Sub CommandButton1_Click()
	'Добавление полей "Город" и "Директор" в запись
	Rst1!Город = Me.TextBox1.Text
	Rst1!Директор = Me.TextBox2.Text
	Me.Hide
End Sub

После закрытия формы управление возвращается в процедуру CreateNewCustomer, где и выполняется оператор Update, добавляющий полностью сформированную запись в таблицу "Заказчики" нашей базы данных.

Чистка полей

Чтобы закончить рассмотрение вопросов, связанных с приданием интерактивности той части документа, где речь идет о реквизитах заказчика, нам осталось рассмотреть самую простую задачу - чистки соответствующих полей документа. Эта задача является частью более общей задачи чистки всех полей документа, но она может возникать и в процессе работы с разделом "Реквизиты заказчика". Понятно, что с программистской точки зрения особых проблем здесь возникать не может. Тем не менее, приведу текст обработчика события Click командной кнопки "Очистить":

Private Sub CommandButton4_Click()
	'Чистка полей бланка с реквизитами заказчика
	ClearCustomerFields
End Sub	
Public Sub ClearCustomerFields()
	'Чистка полей бланка с реквизитами заказчика
	Dim curField As Range
	Set curField = Range("D19:J22")
	curField.ClearContents
	Set curField = Range("D21")
	curField = "tel:			Email: "
End Sub

Единственное, о чем стоит упомянуть, это то, что при чистке сохраняется формат, который следует выдерживать при задании новой информации в поле, отведенном для указания телефона и Email адреса.

Раздел "Сотрудники"

В этом разделе нашего документа будем сохранять информацию о сотруднике офиса, оформляющего заказ. Конечно, проще всего ввести фамилию сотрудника непосредственно в соответствующее поле документа. Но быстрее и методически правильнее выбирать нужные данные из списка фамилий, предоставляемого интерактивным документом. Заполнение этого списка ведется на основе сведений о сотрудниках, хранящихся в базе данных. Вот несколько очевидных преимуществ такого подхода:

Для реализации этого подхода я добавил в документ элемент управления класса Combobox, предназначенный для хранения списка сотрудников. Сразу же скажу еще об одном элементе управления, добавленном мною в документ, - класса Label, метке или надписи, предназначенной для хранения текущей даты. Понятно, что без текущей даты не обходится ни один из документов, и эта дата должна создаваться автоматически, используя соответствующую встроенную функцию Date, возвращающую текущую дату в нужном формате. Конечно в документах, создаваемых на основе Excel, для хранения даты можно использовать любую из ячеек рабочего листа, и я применю этот подход чуть позже при формировании листа печати. Сейчас же хочу продемонстрировать использование различных элементов управления, и потому я предпочел для отображения даты ввести отдельный элемент управления. Взгляните, как выглядит интерфейс этой части документа с добавленными элементами управления:

Добавленным элементам управления я дал соответственно имена - AccountDate и ListOfTeam, и установил подходящее форматирование и другие свойства этих элементов. Останавливаться на этих деталях не буду, отмечу только, что для списка свойство Style я установил, равным 0, что запрещает вносить данные в поле ввода списка в момент работы с документом, - разрешен только выбор из заранее сформированного списка.

Главный вопрос при работе с этими элементами, - как и когда задавать текущую дату и формировать список фамилий на основе сведений из базы данных. Вопрос "когда" решается очевидным способом, - разумнее всего инициализацию отдельных элементов документа проводить в момент общей инициализации, то есть в момент открытия документа. На вопрос "как" проще всего ответить, приведя программный код. Поскольку, благодаря принятой стратегии построения обработчиков событий, текст их остается неизменным, то мне достаточно показать дополнения, сделанные в процедуре InitAccount, вызываемой в обработчике события Open нашего документа:

Public Sub InitAccount()
	'Установить соединение с базой данных
	CreateConnection
	'Конфигурирование команды
	ConfigCommand
	'Очистить поля бланка
	ClearFields
	'Задать дату и создать список сотрудников
	CreateListOfTeam
End Sub

Сделанные изменения подсвечены, и, как видите, они состоят из вызова процедуры CreateListOfTeam, текст которой сейчас приведу:

Public Sub CreateListOfTeam()
	'Задать дату и создать список сотрудников
	Dim myDate As Object
	Dim myCombo As ComboBox
	'Установить дату
	Set myDate = ThisWorkbook.Worksheets(1).OLEObjects("AccountDate").Object
	myDate.Caption = Date
	'Создать список сотрудников
	Set myCombo = ThisWorkbook.Worksheets(1).OLEObjects("ListOfTeam").Object
	Cmd1.CommandText = "Select [ФИО] From [Сотрудники]"
	Set Rst1 = Cmd1.Execute
	With Rst1
		.MoveFirst
		Do While Not .EOF
			myCombo.AddItem .Fields(0)
			.MoveNext
		Loop
	End With
End Sub

Приведу краткие комментарии:

Давайте теперь перейдем к основному разделу нашего документа, связанному с оформлением самого заказа, и покажем, как добавить интерактивность этой части документа.

Раздел "Заказы"

Интерактивность, которую я добавлю в этом разделе документа, должна обеспечить, по крайней мере, решение двух основных задач:

Займемся этими задачами. И первое, с чего начнем, добавим в этот раздел документа три инструментальные кнопки с надписями "Сформировать заказ", "Сохранить заказ", "Очистить Заказ" и именами - "FormOrder", "SaveOrder", "ClearOrder". Вот как выглядит интерфейс документа с добавленными кнопками:

Формирование заказа

Какие задачи предполагается решать на этапе формирования заказа? Их несколько:

Принципиально, решение всех этих задач уже обсуждалось. Мы умеем связываться с базой данных, получать нужный набор записей, формировать по данным этих записей список в некоторой форме, которая предъявляется пользователю с предоставлением ему возможности выбора нужных элементов списка. Обсуждалась также и задача переноса данных из набора записей в поля документа. Так что нам осталось посмотреть, как выглядит реализация, написанная мной для решения этих задач в данной конкретной ситуации.

Решение всех перечисленных задач инициирует пользователь в тот момент, когда он нажимает кнопку "Сформировать заказ". Рассмотрим работу обработчика события Click, запускаемого при нажатии данной командной кнопки:

Private Sub FormOrder_Click()
	'Формирование заказа
	OrderForm
End Sub

Как всегда, основную работу выполняет вызываемая в обработчике процедура. Вот ее текст:

Public Sub OrderForm()
	'Формирование заказа!
	Dim txt As String
	Dim i As Integer
	Dim RowIndex As Integer
	'Число столбцов списка
	Const ColumnCount = 4
	'Создание набора записей с информацией о книгах
	Cmd1.CommandText = "Select [Автор],[Название],[Год издания],"  _
										"[Цена] From [Книги]"
	Set Rst1 = Cmd1.Execute
	'Формирование списка формы Books
	Books.ListOfBooks.Clear
	Books.ListOfBooks.ColumnCount = ColumnCount
	RowIndex = 0
	With Rst1
		.MoveFirst
		Do While Not .EOF
			On Error Resume Next
			'Текущая запись переносится в список
			'Первый столбец
			Books.ListOfBooks.AddItem .Fields(0)
			'Остальные столбцы
				For i = 1 To ColumnCount - 1
					txt = ""
					txt = .Fields(i)
					Books.ListOfBooks.Column(i, RowIndex) = txt
				Next i
			RowIndex = RowIndex + 1
			.MoveNext
		Loop
	End With
	'Показать форму со списком книг
	Books.Show
End Sub

В процедуре можно выделить три части, согласно трем решаемым задачам. В первой части создается набор записей, содержащий информацию о товаре, в данном случае - о книгах редакции. Объекты ADO позволяют получать нужные данные естественным образом без особых затруднений.

Во второй, основной части процедуры происходит заполнение списка класса Listbox, содержащего несколько столбцов, каждый из которых хранит информацию об авторе книги, ее названии, стоимости и цене. Записи не фильтруются, и потому в список включается информация обо всех товарах - всех книгах, данные о которых хранятся в базе. Заметьте, оператор On Error позволяет справляться с трудностями, которые могут возникнуть, если не все поля записи будут заполнены.

Наконец, третья часть процедуры состоит из одного оператора, который форму Books с заполненным списком предъявляет пользователю. Вот как выглядит эта форма в процессе работы:

Я не описываю процесс проектирования формы, установки свойств формы и встроенных в нее элементов управления. Надеюсь и так понятно, что желательно список сделать многостолбцовым, чтобы при выборе товара пользователь мог видеть и его атрибуты, - в данном случае, - автора, стоимость и год издания книги. Крайне желательно предоставить возможность множественного выбора из списка, чтобы одним махом выбрать все нужные товары. Другие свойства списка упоминать не буду.

Дальнейшие действия опять инициируются пользователем. Он производит выбор в списке книг и нажимает командную кнопку с надписью "Выбери нас" в форме Books. Затем в дело вступает обработчик события Click для этой кнопки. Так и реализуется та самая интерактивность, о которой идет речь в этой главе. Вот код этого обработчика:

Private Sub SelectUs_Click()
	'Данные о выбранных книгах переносятся в поля документа
	SelectedBooks
End Sub

Что же делает процедура SelectedBooks? Задача ее проста, - зная выбор пользователя, сформировать поля бланка заказа, по крайней мере, те из них, которые не требуют дополнительных решений от пользователя. Вначале взгляните на результат ее работы:

А теперь рассмотрим, как это делается:

Public Sub SelectedBooks()
	'Данные о выбранных книгах переносятся в поля документа
	Dim i As Integer, j As Integer
	Dim curField As Range, curField1 As Range
	Dim txt As String
	Dim myNds As Object
	'Dim myNds As TextBox
	'Чистка полей документа
	ClearBookFields
	'Инициализация
	Set curField = Range("A34:D34")
	Set curField1 = Range("E34")
	With ThisWorkbook.Worksheets(1).OLEObjects
		Set myNds = .Item("NDS").Object
		.Item("NDS").Visible = True
		.Item("LabelNDS").Visible = True
		
		'Проход по списку
		With Books.ListOfBooks
			j = 0
			For i = 0 To .ListCount - 1
				If .Selected(i) Then
					'Книга выбрана
					'Перенос данных в поля документа
					curField.Offset(j) = .List(i, 0)  " '"  _
						.List(i, 1)  "'"
					curField1.Offset(j) = "шт."
					curField1.Offset(j, 2) = .List(i, 3)
					curField1.Offset(j, 4) = myNds.Text
					j = j + 1
				End If
			Next i
		End With
		'Включить кнопку "Сохранить Заказ"
		.Item("SaveOrder").Object.Enabled = True
	End With
	'Спрятать форму "Книги"
	Books.Hide
End Sub

Текст процедуры довольно большой, и он нуждается в некоторых дополнительных комментариях:

Работа с заказом

Работа с нашим интерактивным документом предполагает, что пользователь обладает определенной квалификацией, знает, какую цель он хочет достичь, и какие действия он должен предпринять, в зависимости от того, на каком этапе формирования документа он находится. Пользователь управляет процессом создания документа, прохождением всех его этапов. Замечу, что для сложных документов полезно создавать специальных Мастеров, которые ведут пользователя от этапа к этапу. Но наш документ к таковым, по-видимому, не относится.

После того, как обработчик события, связанного с нажатием кнопки "Выбери нас", закончил свое дело и частично сформировал таблицу заказа - смотри рис. 7.10, - дело опять за пользователем. Продолжая формирование заказа, он может в уже заполненной таблице заказа удалить некоторые строки, соблюдая естественное требование, чтобы в середине таблицы заказа не появлялись пустые строки. Он может изменить значение поля НДС в отдельных строках таблицы заказа, может изменить значение ставки НДС, используемое по умолчанию, введя новое значение в поле ввода открывшегося элемента управления. Все эти действия возможны, но не обязательны. Но что обязательно нужно сделать для продвижения дела формирования заказа, это проставить количество заказываемого товара, - указать для каждой книги количество заказываемых экземпляров. Можно было бы 'облегчить' работу пользователя и задать по умолчанию некоторое значение, но это опасная вещь, - количество заказываемых книг, также как их выбор, - это дело пользователя. Я предпочел только контролировать, задал ли пользователь количество всех заказанных товаров.

Заметьте, пользователь задает только количество заказываемых экземпляров, а все остальное - подсчет стоимости товара, стоимости с учетом НДС, общей стоимости заказа, - все это будет сделано автоматически, поскольку соответствующие формулы уже записаны в ячейках нашей таблицы. Взгляните, как выглядит полностью сформированная таблица заказа, сразу после того, как пользователь задал количество заказанных экземпляров для каждой выбранной книги:

Сохранение заказа

Теперь, когда заказ полностью сформирован, его следует сохранить в базе данных. Командная кнопка "Сохранить заказ" уже 'горит', и пользователю остается ее только нажать. Но прежде чем приводить программный код, стоит несколько слов сказать о том, как хранятся заказы в базе данных.

Данные о заказе хранятся в двух таблицах базы данных - "Заказы" и "Заказано", связанных между собой общим полем "Код заказа". Первая из этих таблиц содержит одну запись для каждого заказа, в которой содержится общая информация - о заказчике, о сотруднике, оформляющем заказ, дате заказа и его общей стоимости. Во второй таблице о каждом заказе содержится несколько записей, - по одной записи на каждый заказываемый товар. Теоретическим обоснованием такого представления данных является теория нормализации реляционных баз данных. Но и без теории понятно, что нет необходимости хранить в каждой записи общую информацию. Ее достаточно хранить один раз, с другой стороны, она доступна, найти ее легко, благодаря общему коду заказа, который присутствует в каждой записи.

С программной точки зрения никаких особых новых проблем при передаче данных из полей документа в таблицы базы данных не возникает. Объекты ADO достаточно просто позволяют создать обновляемый набор записей, примеры этому уже приводились. Тем не менее, полагаю, есть смысл посмотреть на код обработчика события, возникающего при нажатии командной кнопки "Сохранить заказ". Вот он:

Private Sub SaveOrder_Click()
 'Сохранение заказа
	OrderSave
End Sub

Рассмотрим код процедуры, выполняющей основную работу:

Public Sub OrderSave()
	'Сохранение заказа
	Dim myDate As Object
	Dim myCombo As ComboBox
	Dim curField As Range, curField1 As Range
	Dim i As Integer
	Dim BCount As Boolean
	BCount = True
	Set curField = Range("A34:D34")
	Set curField1 = Range("F34")
	With ThisWorkbook.Worksheets(1).OLEObjects
		Set myDate = .Item("AccountDate").Object
		Set myCombo = .Item("ListOfTeam").Object
	End With
	'Проверка заполнения полей "Количество"
	i = 0
	Do While (Not IsEmpty(curField.Offset(i)))
		BCount = BCount And Not IsEmpty(curField1.Offset(i))
		i = i + 1
	Loop
	If BCount Then	'Заполнены поля "Количество"
		 'Добавление записи в таблицу "Заказы"
		 Cmd1.CommandText = "Select * From [Заказы]"
		 With Rst1
			 If .State = adStateOpen Then .Close
			 .Open Source:=Cmd1, CursorType:=adOpenDynamic, _
				 LockType:=adLockOptimistic
			 .AddNew
			 !Заказчик = Range("D19").Text
			 !Сотрудник = myCombo.Text
			 !ДатаЗаказа = myDate.Caption
			 !Стоимость = Range("K46")
				.Update
				OrderCode = !КодЗаказа

		 End With
		 'Добавление нескольких записей в таблицу "Заказано"
		 Cmd1.CommandText = "Select * From [Заказано]"
		 With Rst1
			 If .State = adStateOpen Then .Close
			 .Open Source:=Cmd1, CursorType:=adOpenDynamic, _
				 LockType:=adLockOptimistic
			 'Цикл по строкам таблицы заказов
			 i = 0
			 Set curField = Range("A34")
			 Set curField1 = Range("F34")
			 Do While (Not IsEmpty(curField.Offset(i)))
				 .AddNew
				 !КодЗаказа = OrderCode
				 !НазваниеКниги = curField.Offset(i)
				 !Количество = curField1.Offset(i)
				 !Стоимость = curField1.Offset(i, 5)
				 .Update
				 i = i + 1
			 Loop
		 End With
		'Выключить кнопку "Сохранить Заказ"
		With ThisWorkbook.Worksheets(1).OLEObjects
			.Item("SaveOrder").Object.Enabled = False
			'Поле NDS сделать невидимым
			.Item("NDS").Visible = False
			.Item("LabelNDS").Visible = False
		End With
		'Номер заказа становится номером счета
		Range("F16") = OrderCode
	Else
		MsgBox ("Задайте значения в полях Количество!")
	End If
End Sub

Приведу комментарии к тексту процедуры:

Чистка полей таблицы заказа

Рассмотрим действие еще одной командной кнопки - "Очистить заказ". Выше, в разделе "Работа с заказом", я говорил, что последнее слово всегда остается за пользователем, и он может вручную редактировать таблицу заказов. В какой - то момент может оказаться, что он хотел бы прекратить работу с заказом, без сохранения его в базе данных. Конечно, для перехода к созданию нового заказа можно в любой момент нажать кнопку "Сформировать заказ", но, вполне естественно, предварительно нажать кнопку "Очистить заказ", чтобы очистить поля бланка. Приведу код соответствующего обработчика события:

Private Sub ClearOrder_Click()
	'Чистка полей заказа
	ClearBookFields
End Sub

Процедура ClearBookFields, которая выполняет работу по чистке полей, уже вызывалась в другом месте программного проекта. Теперь пришла пора привести ее код:

Public Sub ClearBookFields()
	'Чистка полей документа
	Dim curField As Range
	Set curField = Range("A34:G45")
	curField.ClearContents
	Set curField = Range("I34:I45")
	curField.ClearContents
	
	'Установить свойства элементов управления
	With ThisWorkbook.Worksheets(1).OLEObjects
		.Item("LabelNDS").Visible = False
		.Item("NDS").Visible = False
		.Item("SaveOrder").Object.Enabled = False
	End With

End Sub

Помимо чистки полей в процедуре делаются невидимыми уже упоминавшиеся OLE-объекты, а главное, выключается командная кнопка "Сохранить заказ". Это и понятно, - раз поля таблицы заказа пусты, - запись в базу данных должна быть запрещена, пока не будет сформирован новый заказ.

Обратите внимание на один чисто программистский аспект работы этой процедуры, связанный с OLE-объектами. Свойство Visible доступно для всех элементов коллекции OLEObjects. Но чтобы добраться до свойства Enabled, мне предварительно нужно вызвать свойство Object, и только потом у полученного объекта можно установить значение свойства Enabled.

На этом я заканчиваю рассмотрение интерактивности, добавляемой в раздел заказов нашего документа.

Подготовка документа к печати

По сути, все, что я хотел сказать и сделать по добавлению интерактивности в наш документ, уже сказано и сделано. Но есть еще один важный пункт, который следует обсудить. Его можно сформулировать так: Как избавиться от интерактивности? Как создать бумажную форму документа?

Я называю электронный документ интерактивным, - находящимся в интерактивной форме, если он содержит элементы управления и, по крайней мере, частично, заполняется автоматически. Электронный документ находится в бумажной форме, если он элементов управления не содержит и, следовательно, заполняется вручную. Может быть много причин, по которым иногда желательно преобразовать документ из интерактивной в бумажную форму. По-видимому, главная из них состоит в том, что бумажная форма, как правило, предназначается для последующего вывода на печать. Поскольку печать документа совершенно необходимая операция над документом, то необходимость в таком преобразовании очевидна.

Получить из интерактивной формы бумажную, согласно нашему определению, совсем просто, - достаточно удалить из формы все элементы управления, и она станет бумажной.

Вот что значит дать "хорошее" определение, - проблема сразу решается просто. Если же говорить более серьезно, то когда возникает необходимость напечатать документ, находящийся в интерактивной форме, то при преобразовании его в бумажную форму, пригодную для печати, может понадобиться не только удаление элементов управления. Возможно, придется удалять и другие детали, своего рода архитектурные излишества, допустимые в интерактивной форме, но которыми можно пожертвовать при выводе на печать, особенно учитывая ограниченные размеры печатного листа. Важно, однако, иметь в виду, что при удалении элементов управления можно заодно "выплеснуть и ребенка". Так, например, удалив список, содержащий фамилии сотрудников, мы удалим тем самым важную информацию, которая необходима и должна присутствовать при выводе документа на печать.

Сформулирую цель предстоящей работы. Она в следующем - создать на отдельном листе копию документа в бумажной форме, подготовленную к печати. В процессе преобразования документа в бумажную форму выделю следующие действия:

Чтобы пользователь мог управлять процессом преобразования, создадим панель с инструментальными кнопками, выбор которых и будет решать ту или иную частную задачу преобразования. С создания панели с инструментальными кнопками и начну. Я не буду останавливаться на самом процессе создания панели. Полагаю, достаточно взглянуть на то, как выглядит эта панель:

Элемент-невидимка

Вместо того чтобы удалять элементы управления из электронной формы, сделаем их невидимыми. Тогда они становятся недоступными для пользователя и одновременно не отображаются при печати. С другой стороны, в нужный момент их всегда можно включить. Так что одним щелчком форма из интерактивной преобразуется в бумажную и наоборот. Многие элементы интерфейса обладают прекрасным булевым свойством Visible. Чтобы сделать такой элемент невидимым, нужно просто выключить свойство Visible. Заметьте, что сами элементы управления - объекты класса CommandButton, Image или ComboBox таким свойством не обладают. Но при их добавлении в рабочий лист, они становятся элементами коллекции OLEObjects, каждый из элементов которой обладает этим свойством, что неоднократно использовалось в ранее приведенных макросах. Заметьте, что не только элементы коллекции, но и вся коллекция в целом обладает этим свойством. Это позволяет делать видимой или невидимой всю коллекцию одним оператором.

Помимо элементов управления я размещал на документе и некоторые объекты WordArt - линии, рамки, позволяющие улучшить оформление листа. При их добавлении в рабочий лист эти объекты становятся элементами коллекции Shapes. Коллекция Shapes не обладает свойством Visible, но ее элементы таким свойством обладают. Я решил при печати избавиться от всяческого "украшательства", и потому выключил видимость и этих элементов.

Операция выключения видимости объектов, очевидно, обратима. Поэтому на созданной панели инструментальных кнопок расположены две кнопки, позволяющие включать и выключать видимость объектов. Соответственно написаны и два макроса, запускаемые при нажатии этих кнопок. Вот текст макросов OlePlus и OleMinus, включающих и выключающих видимость OLE-объектов и Shape-объектов:

Public Sub OlePlus()
	'Эта процедура делает видимой коллекцию OLE - объектов
	'Видимой делается и коллекция Shapes
	
	Dim Ob As OLEObjects
	Set Ob = ThisWorkbook.Worksheets(1).OLEObjects
	If Ob.Count > 0 Then Ob.Visible = True
	For Each Shp In ThisWorkbook.Worksheets(1).Shapes
		Shp.Visible = msoTrue
	Next Shp
End Sub

Public Sub OleMinus()
	'Эта процедура делает невидимой коллекцию OLE - объектов
	'Невидимой делается и коллекция Shapes
	Dim Ob As OLEObjects
	Set Ob = ThisWorkbook.Worksheets(1).OLEObjects
	If Ob.Count > 0 Then Ob.Visible = False
	Dim Shp As Shape
	For Each Shp In ThisWorkbook.Worksheets(1).Shapes
		Shp.Visible = msoFalse
	Next Shp
End Sub

Макросы OlePlus и OleMinus связываются с соответствующими инструментальными кнопками созданной панели инструментов.

Включение и выключение фона

Включение и выключение фона, - простые операции. Достаточно выделить нужную область и установить соответствующие свойства объекта Selection. Вот два макроса, решающие эту задачу. Нетрудно догадаться, с какими кнопками нашей панели они связаны:

Public Sub FonPlus()
	'Заливает область бланка серым цветом
	Range("A1:K56").Select
	With Selection
		.Font.ColorIndex = 1
		.Interior.ColorIndex = 15
	End With
	Range("A1").Select
End Sub

Public Sub FonMinus()
	'Восстанавливает белый цвет в области бланка
	Range("A1:K56").Select
	With Selection
		.Font.ColorIndex = 1
		.Interior.ColorIndex = 2
	End With
	Range("A1").Select
End Sub

Создание листа печати

Теперь, когда некоторая предварительная работа проделана, займемся основной работой по подготовке листа печати. Замечу, что, конечно, нажав кнопки OLE- и Fon-, можно приступить к печати листа, в таком виде, как он получился. После чего, нажать кнопки OLE+ и Fon+, вернув лист в исходное состояние. Однако это не лучший способ, поскольку остались кой-какие лишние детали, а главное при выключении была потеряна важная информация. Поскольку окончательное формирование листа печати является необратимой операцией, не позволяющей вернуться к исходному состоянию, то лучшим выходом является формирование дополнительного листа, специально предназначенного для печати.

Пятая кнопка на инструментальной панели с именем "КопияП" запускает макрос, готовящий лист печати. Приведу его текст:

Public Sub CopyForPrinting()
	'подготавливает копию листа для печати
	Dim mySh As Worksheet
	Dim Ex As Boolean
	Dim curField As Range
	With ThisWorkbook
		Ex = False
		For Each mySh In .Worksheets
			If mySh.Name = "ЛистПечати" Then
				Ex = True: Exit For
			End If
		Next mySh
		If Not Ex Then
			.Worksheets.Add
			.ActiveSheet.Name = "ЛистПечати"
			.ActiveSheet.Move After:=Sheets("Заказ")
		End If
		'Очистить область листа печати
		Set curField = .Worksheets("ЛистПечати").Range("A1:K56")
		curField.Clear
		'Показ скрытых строк
		StrShow
		'Копирование листа
		Set curField = .Worksheets("Заказ").Range("A1:K56")
		curField.Copy
		.Sheets("ЛистПечати").Activate
		.ActiveSheet.Range("A1").Select
		.ActiveSheet.PasteSpecial
	End With
		'запуск макросов
		OleMinus
		FonMinus
		'Скрыть лишние строки
		StrHide
		'Дополнить нужной информацией
		AddInformation
		'Удалить сетку и другие детали
		 With ActiveWindow
			.DisplayGridlines = False	'сетка
			.DisplayHeadings = False	'заголовки
			.DisplayFormulas = False	'показ формул
			.DisplayZeros = False		'отображение нулей
		End With
	
End Sub

В работе процедуры можно выделить несколько этапов:

Приведу теперь тексты процедур StrHide и StrShow:

Public Sub StrHide()
	'Эта процедура позволяет скрыть лишние строки
	Dim curField As Range, curField1 As Range
	Dim i As Integer
		Rows("1:7").Select
		Selection.EntireRow.Hidden = True
		Rows("10:14").Select
		Selection.EntireRow.Hidden = True
		Rows("17:18").Select
		Selection.EntireRow.Hidden = True
		Rows("27:28").Select
		Selection.EntireRow.Hidden = True
		Rows("48:49").Select
		Selection.EntireRow.Hidden = True
		
		'Скрыть пустые строки в таблице заказов
		Set curField = Range("A34:D34")
		Set curField1 = Rows("34:34")
		For i = 0 To 11
			If IsEmpty(curField.Offset(i)) Then
				curField1.Offset(i).Select
				Selection.EntireRow.Hidden = True
			End If
		Next i
	End Sub

В процедуре StrHide скрываются строки, не несущие информацию. При скрытии строк внутри таблицы заказа приходится проводить анализ того, заполнена ли строка таблицы информацией.

Процедура StrShow, показывающая скрытые строки, намного проще, поскольку достаточно указать весь требуемый диапазон строк и показать все строки, которые, возможно, были скрыты. Вот текст этой простой процедуры:

Public Sub StrShow()
	'Показ скрытых строк
		Rows("1:56").Select
		Selection.EntireRow.Hidden = False
End Sub

Для полноты картины осталось привести процедуру AddInformation, добавляющую информацию в ячейки листа печати. Вот ее текст:

Public Sub AddInformation()
	'Эта процедура дополняет лист печати нужной информацией
	Dim MyCombo As Object
	With ThisWorkbook
		Set curField = .Worksheets("ЛистПечати").Range("D8")
		curField.Value = "Издательство: Родная Речь"
		curField.Font.Bold = True
		curField.Font.Size = 12
		Set curField = .Worksheets("ЛистПечати").Range("H16")
		curField.Value = Date
		curField.Font.Bold = True
		curField.Font.Size = 14
		Set curField = .Worksheets("ЛистПечати").Range("H29")
		Set MyCombo = .Worksheets("Заказ").OLEObjects("ListOfTeam").Object
		curField.Value = MyCombo.Text
		curField.Font.Bold = True
		curField.Font.Size = 12
	End With
End Sub

В завершение рассказа о подготовке листа печати давайте взглянем на то, что получается при нажатии кнопки "КопияП", ответственной за выполнение этой работы:

На этом и закончу обсуждение того, как придать интерактивность офисным документам.