При работе из-под 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 (), мною изрядно «отредактированный»:

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, я исполняла вокруг компа от полноты эмоций ритуальные танцы неизвестного народа — под заинтересованным взглядом админа. Хороший урок — не оригинальничайте с местом жизни программы без острой в том необходимости. Если уж Офис допускает такие ситуации, то нестабильная работа (точнее, стабильная неработа) продуктов от менее известных компаний может исправится именно таким незамысловатым путем.

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