АРХИВ СТАТЕЙ ЖУРНАЛА «МОЙ КОМПЬЮТЕР» ЗА 2003 ГОД

Доступный Access 2

Все журналы \ Номер 45/268 `2003 от 10.11.2003 \ Доступный Access 2

Наталья ЛИТВИНЕНКО litnat@railway.donetsk.ua

При работе из-под Access периодически возникает необходимость в переносе аксесовского отчета в тот же Excel (см. статью «Доступный Access», МК №43 (266)). Это можно сделать стандартным методом — если, конечно, результат работы устроит. Альтернативой стандартным методам являются методы нестандартные. То есть, нужно состряпать программу на VB. Об одном таком способе и пойдет речь.

Вначале — декорации. Пусть у нас есть некоторая формочка, назначение которой — приготовить все для совершения запроса к базе или базам, результатом коего будет отчет (для просмотра, для вывода на принтер начальству), вывод данных в форме (для просмотра или редактирования) или перегон тех же данных в Excel. На формочке есть кнопочка, с которой, собственно, и начинается сказка. Обсуждение формочки и способов ее работы в мои планы на эту статью не входит, это другая песня, и довольно длинная. Можно только сказать, что нажатия всех кнопочек, куда бы они не выводили данные, передаются вначале небольшой функции в четыре строки (если считать вместе со стандартным обработчиком ошибок); задача этой функции состоит в передаче управления с некоторым параметром Большой Подпрограмме, которая анализирует, что же выбрал пользователь на форме, какие он хочет получить данные. Причина такого разделения труда — не игра в структурное программирование, просто пользователь моей программы может позволить себе действительно многое, например, может наложить условия почти на все поля базы. Как результат — процедура достаточно длинная. Замечу, проект стал таким как сейчас не сразу, количество выпадающих списков и полей на формочке потихонечку растет.

Вышеупомянутый параметр указывает, в какой форме должен быть выдан результат запроса — форма, отчет, Excel и т.п. Он обрабатывается в самом конце Большой Подпрограммы, в case, и там или сразу дается команда на выдачу (для отчета или запроса), или (в случае с Excel) уходит в следующую процедуру, которой я и буду заниматься вместе с вами всю эту статью. Ее будут звать, например, All_Exl(stLinkCriteria), где параметр stLinkCriteria — это кусок запроса, который идет после WHERE , сформированный в Большой Подпрограмме по итогам выбора пользователя. С декорациями все. Могу только заметить, что, естественно, необязательно все должно быть столь страшно и сложно. Я просто расписываю, как оно устроено у меня в реальном АРМе. В вашем же все может быть проще — например, один и тот же запрос, простая формочка с рожицей —:-) — и пара кнопок. Конец вступлению, перейдем собственно к делу.

Для корректной работы программы нужно прикрутить библиотеку Microsoft Excel 8.0 Object Library. Для этого нужно находиться в окне, где мы пишем макрос, потом в меню Сервис нужно выбрать Ссылки, там уже находим нужную библиотеку и помечаем птичкой.

Если быть совершенно точным, то в Excel мы передаем не собственно отчет, а данные, оный составляющие. Рассмотрим вариант by Alexander Gaman (http://talk.mail.ru/article-26440714.html), мною изрядно «отредактированный»:

Sub All_Exl(stLinkCriteria)
Dim App As Object 
Dim wkb As Object
Dim wks As Object
Dim rst As Recordset
Dim intCountObj As Integer
Dim strFileName As String 
On Error GoTo er
strFileName = "C:\Форма11.xls" ' так будут звать новый файл
Set rst = CurrentDb.OpenRecordset("Запрос")
'к базе делается запрос, как результат получается выборка данных или recordset, который и присваивают объявленной объектной переменной
 If rst.RecordCount > 0 Then
'проверяем, не пуст ли recordset. Если в неводе что-то есть, он будет равняться единице
   rst.MoveLast
   intCountObj = rst.RecordCount
 Else
   MsgBox "Отчет пуст!"
   Exit Sub
 End If
'не забудем прокрутить recordset на начало — нам ведь потом данные из него вставлять.
rst.MoveFirst
'собственно начинаем работать с Access
Set App = CreateObject("Excel.Application")
'если Excel уже открыт, ошибки не будет — просто рядом с уже открытым Excel’ем откроется еще один
Set wkb = App.Workbooks.Open("C:\Shablon\Форма11.xlt")
'тут возможны варианты. Совсем не обязательно порождать новый файл от шаблона, можно просто открыть уже существующий файл, вставить в него новую страницу или даже записать данные на уже наличествующую. Это удобно в тех случаях, когда нужно программу передавать с дискетой куда-нибудь туда, где операцию по переписыванию файла еще выполнить могут, а создать папку, положить в нее шаблон, да еще и потом не удалить — уже нет. Если хотим открыть файл, то вместо комментируемой строки пишем следующий код:
If Not Dir(strFileName) = "" Then
Set wkb = appExcel.Workbooks.Open(strFileName)
Else: Set wkb = appExcel.Workbooks.Add
End If
'мы вначале проверили, а существует ли открываемый файл. Если да — открываем; strFileName в этом случае означает имя открываемого файла. При отсутствии — создаем. Для отладки удобно сделать Excel видимым, чтобы удобнее было наблюдать за работой программы. Потом можно будет его и спрятать. Но пока:
app.Visible = True
'открываем некоторый лист — например, второй. 
Set wks = wkb.Sheets(2) 
'или создаем новый лист
Set wks = wkb.Worksheets.Add
wks.Range("A18").CopyFromRecordset rst ' копирование всего массива данных из recerdset’а на лист. Понятно, что А18 поставлено «от фонаря» и для примера. Если процесс вставки грозит затянуться, то неплохо бы дать о том знать юзеру — например, изменив вид курсора на часы перед началом вставки:
DoCmd.Hourglass True
'а после завершения вставки — вернуть часам исходный вид
DoCmd.Hourglass False
'неплохо также на всякий случай вставить возвращение часов к исходному виду в обработчик ошибок — вдруг случится чего в процессе вставки. 
'далее возможны дизайнерские и покрасочные работы — рисование рамочек, заголовков и т.п. Но по-моему, если мы порождаем новый файл от шаблона, разумнее заголовок и вообще все что только можно отрисовать заранее. Выглядит сие примерно так:
With wks.Range("A18:H" & Trim(str(intCountObj + 17)))
'манипулируем куском страницы от того же А18 до строки, определяемой размером recordset. 
 'издевательство над рамочками:
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
'издевательство над шрифтами
.Font.Size = 9
.Font.name = "Arial"
.Font.Size = 8
.Font.Strikethrough = False
.Font.Superscript = False
.Font.Subscript = False
.Font.OutlineFont = False
.Font.Shadow = False
.Font.Underline = xlUnderlineStyleNone
.Font.ColorIndex = xlAutomatic
'выравнивание
.VerticalAlignment = xlCenter
'подгонять ли ширину столбца под текст
.WrapText = True
End With
'можно также разрисовать под Рязань нашу страничку не после вставки данных или до того, а непосредственно в процессе. Для этого нужно сделать цикл по recordset и в каждом шаге рядом со вставкой размещать и покраску. Это разумно, если вы, например, собираетесь раскрасить строки в какие-то два цвета по очереди — строка одного цвета, строка другого и т.д. Выглядеть это может примерно так (вместо строки вставки и строк дизайна):
Do Until rst.EOF
     For I = 0 To intCountFields — 1' где intCountFields — это количество полей базы данных в каждой записи recordset
     .Cells(J, I + 1).Value = rst.Fields(I).Value
	.Cells(J, I + 1).Font.Size = 9
.Cells(J, I + 1).Font.name = "Arial"
.Cells(J, I + 1).Font.Size = 8
.Interior.ColorIndex = J + sgn(J-2*int(J/2)) 'окраска фона ячейки в зависимости от четности 
'и тому подобное, другие свойства дизайна ячейки
     Next
     J = J + 1
     rst.MoveNext
 Loop
'Далее нам нужно сохранить результат наших трудов. То ли мы спрашиваем пользователя, как назвать файл и куда его деть, то ли мы сохраняем его под уже заранее известным именем. Если файл с таким именем уже существует, старую версию уничтожаем перед сохранением новой. 
If Not Dir(strFileName) = "" Then
  Kill (strFileName)
End If

wkb.SaveAs (strFileName)
'теперь начинается уборка мусора за собой.
wkb.Close
App.Application.Quit
Set wks = Nothing
Set wkb = Nothing
Set appExcel = Nothing

er:
DoCmd.Hourglass False
MsgBox "Error №" & Err.Number & " " & Err.Description, vbCritical, "Внимание"

End Sub

В принципе, и все.

Теперь поговорим о хорошем — о глюках :-). Почему этот разговор хорош — потому что старый, известный глюк лучше новых двух. Речь, естественно, не пойдет о том, что кто-то забыл закрыть IF или не описал переменную — борьба с такими ошибками отнимает больше всего времени программиста, это наша работа, точнее даже — быт. Самый основной специфический глюк этой задачи случается тогда, поименованные библиотеки оказываются не прикручены. Если их не установить, то при попытке запустить программу не будут находиться типы, функции и т.п. Или вот аналогичный глюк: «законная», действительно существующая и указанная в книге/доках функция не работает, и компилятор ругается. Опять же посмотрите, прикручена ли нужная библиотека. Особо подлая ситуация — встретить этот глюк, когда на твоей машине функция работает, а на другой, для которой пишется — нет…

Другая ситуация (такое случилось у меня на машине) — при попытке передать данные в Excel программа просто складывалась: «…выполнила недопустимую операцию… коврик должен перегрузиться :-)». Причем такое безобразие творилось даже в отладчике, просто при попытке поднести мышку к глючащему объекту. Ну что ты тут поделаешь? Не сработали старые приемы, которые вроде бы когда-то помогали: сжатие базы, уменьшение количества строк программы путем устранения пустых строк и комментариев (да-да-да, не смейтесь!), копирование формы — удаление исходного экземпляра — переименование старого... Что вообще делает программер при встрече с неожиданным глюком?

1. Запускает программу еще раз.

2. Закрывает, открывает программу и запускает еще раз.

3. Закрывает, открывает Access

4. Потом перегружается — верное средство от многих случайных глюков и… надоедливых юзеров, живущих «на другом конце деревни». Обычно помогает! А если нет?

5. Смотрит в календарь — обнаруживает пятницу. Смотрит на часы — три пятнадцать. А не проиграть ли нам в Квяку/почитать почту/разархивировать книгу? Смех, но ведь действительно помогает — то, что в пятницу решительно не шло и работать и не собиралось, в понедельник пашет и не показывает характер. В чем здесь причина — упрямство ли компа, усталость ли программера или недельный перегрев обоих, наложенное и помноженное много раз само на себя — непонятно. Но факт остается фактом. Не начинайте паниковать заранее и портить дурными мыслями выходные.

6. Сносит Access (в понедельник), устанавливает Access, пробует.

7. Сносит, перегружает, переставляет, пробует.

8. Сносит весь Офис, перегружает, переставляет, пробует…

9. Начинает думать…

А теперь без шуток :-). Вариантов было два — виноват дистрибутив Офиса и виновата Windows. Сносить операционку решительно не хотелось, потому программа была запущена на другом компе, где и отработала успешно. Желаю вам глюков (не желаю, конечно, но…), которые победимы менее травматичными и морочными способами.

Или вот еще одна интересная ошибка. Access в упор не видел необходимых программе библиотек. Что только я не делала — и сверяла с соседним компом папочки с библиотеками, и переписывала их содержимое, и тыкала программу мордой в оные через «Обзор», и воевала с autoexec.bat, прописывая пути, да все не впрок. Непонятный глюк требует абсурдного метода решения — я просто переставила Офис с диска Е на диск С. Пока ожившая программа, как турбина, гнала данные в Excel, я исполняла вокруг компа от полноты эмоций ритуальные танцы неизвестного народа — под заинтересованным взглядом админа. Хороший урок — не оригинальничайте с местом жизни программы без острой в том необходимости. Если уж Офис допускает такие ситуации, то нестабильная работа (точнее, стабильная неработа) продуктов от менее известных компаний может исправится именно таким незамысловатым путем.

В Сети хватает различных примеров такого рода программ, более или менее подробных, различных по функциональности, длине и количеству комментариев. Например, http://www.databasejournal.com/features/mssql/article.php/1493901(с примером установки параметров печати страницы), http://www.mvps.org/access/modules/mdl0035.htm, http://www.experts-exchange.com/Databases/MS_Access/Q_20646694.html, http://bluejack.binus.ac.id/19082002/viewtopic.php?topic=708&forum=40(с применением ADO), http://www-tcsn.experts-exchange.com/Programming/Q_20659684.html, http://dbforums.com/t890500.html(длинный пример), http://www.df.ru/%7Eswan/inf2think_2/inf186.htm(с применением OLE). Свод многих методов и путей перевода данных можно найти на странице http://www.zmey.1977.ru/Access_To_Excel.htm, здесь же сравнивается скорость их работы.

Рекомендуем ещё прочитать:

3D-максимум. (№7/230 `2003) Сергей Бондаренко, Марина Двораковская
Файловая конституция. (№13/236 `2003) Сергей Уваров
Толстый-толстый слой Фотошопа. (№15/238 `2003) Виктор Борщак
Быт и бытие одной OS. (№19/242 `2003) Сергей Бурачек (Bur)
Препарируем пингвина. (№24/247 `2003) Сергей А. Яремчук
Между объективом и принтером. (№40/263 `2003) Сергей Уваров
Штаб тотального командования. (№40/263 `2003) Владимир Aka «Ворон»Роздобудько
Доступный Access 2. (№45/268 `2003) Наталья Литвиненко

Предыдущая статья Назад К списку журналов Следующая статья