В.А. Биллиг, М.И. Дехтярь
VBA и Office 97
Офисное программирование

Глава 22(3)

Передача данных между листами рабочей книги

В программах предыдущего раздела мы неоднократно решали задачу передачи данных одного листа в ячейки другого. Эта передача происходила в цикле, а адреса передаваемых ячеек вычислялись программно. При этом мы применяли надежную, но "грубую" технику, формируя адрес ячейки из имени ее столбца и номера строки. Но ведь можно программировать и более элегантно, используя технику смещений (свойство Offset объекта Range). Вместо формирования адреса текущей ячейки мы будем указывать ее смещение относительно фиксированной ячейки. Поскольку необходимо переключение между страницами, нужна переменная, хранящая значения передаваемых ячеек. Так как речь идет о передаче полей записи, элегантное программирование требует введения пользовательского типа, задающего запись. В качестве примера приведем процедуру CellsToCells, которая задает новую реализацию той части процедуры "Выбери Нас", в которой записи о книгах, выбранные по запросу, передаются из листа "Книги" к листу "БланкЗаказа". В этой процедуре используется пользовательский тип BookOrder:

Type BookOrder
     Номер As Integer
     Автор As String
     Название As String
     Количество As Integer
     ЕдиницаИзмерения As String
     Цена As Integer
     Стоимость As Long
End Type

  Вот сама процедура:

Public Sub CellsToCells()
     'Использование техники Offset при заполнении таблицы заказа
     'Определение записи пользовательского типа
     Dim книга As BookOrder
     Const intSelect As Integer = 2
     Dim myr As Range, myr1 As Range
     Set myr = Sheets("Книги").Range("Q2")
     Set myr1 = Sheets("БланкЗаказа").Range("B30")
     ClearTable
     'Цикл по книгам заказа
     For i = 1 To intSelect
       'Формирование записи
       Sheets("Книги").Activate
       книга.Номер = i
       книга.Автор = myr.Offset(i, 1)
       книга.Название = myr.Offset(i, 2)
       книга.ЕдиницаИзмерения = myr.Offset(i, 5)
       книга.Цена = myr.Offset(i, 6)
       'Перенос записи в таблицу заказа
       Sheets("БланкЗаказа").Activate
       myr1.Offset(i, 0) = книга.Номер
       myr1.Offset(i, 1) = книга.Автор
       myr1.Offset(i, 3) = книга.Название
       myr1.Offset(i, 7) = книга.ЕдиницаИзмерения
       myr1.Offset(i, 8) = книга.Цена
     Next i
End Sub

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

Преобразование базы Excel в базу Access

Связи между приложениями Office 97 тесны. В частности, предусмотрены специальные средства экспорта-импорта БД между приложениями Access и Excel (и не только между ними). Мы видели, как преобразовать таблицы Access в таблицы (списки) Excel. Обратная задача сложнее, поскольку таблица в настоящей БД сложнее списка Excel. С ней связана более сложная внутренняя структура, индексы и т. д. Поэтому это преобразование через буфер уже не проведешь. Здесь должен работать мастер — инструментальное средство, выполняющее преобразование. Для его вызова нужно перейти на страницу со списком Excel и выделить в нем какую-либо ячейку, после чего выполнить команду "Перенести в MS Access" из меню "Данные". В качестве примера, рассмотрим экспорт списка Excel "Заказано" в соответствующую таблицу Access. Весь диалог, возникающий в процессе экспорта списка, мы рассматривать не будем, но воспроизведем некоторые диалоговые окна и обсудим принимаемые решения. Вот окно, появляющееся на одном из первых шагов работы мастера:

{Рис. 22_7. Окно мастера, импортирующего в таблицу Access список Excel }

В ходе дальнейшего диалога принимаются такие решения, как:

·         в какую БД экспортируется список —новую или существующую;

·         нужно ли создавать новую таблицу или данные пополняют уже имеющиеся.

А вот как выглядит окно диалога, позволяющее установить, какие поля импортируются:

{ Рис. 22_8. Выделение импортируемых и индексных полей}

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

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

{Рис. 22_9. Заключительный шаг мастера импорта электронной таблицы}

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

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

{Рис. 22_10. Мастер-анализатор предлагает разделение таблицы}

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

    Назад        Конец главы 22