Лучшие статьи журнала ╚Компьютеры+Программы╩

В этом выпуске Вашему вниманию предлагается статья "EXCEL и WORD "на подхвате" у ACCESS", которая по резкультатам голосования разделила третье место со статьями "DBF-файлы и Access" и "Четверо в лодке, не считая принтера". Эти две статьи также будут опубликованы в ближайших выпусках рассылки.

EXCEL и WORD "на подхвате" у ACCESS

Сергей ГУЩЕНКО,
publish@nics.kiev.ua

Для большинства пользователей самой "главной" программой в настоящее время, вероятно, является MS Word. Многие знают и Excel. В этой статье мы расскажем вам, как, используя несложные приемы программирования, можно автоматизировать передачу в эти программы данных из СУБД Access.

Начнем с конкретного примера, который был рассмотрен в №1 журнала за этот год (база находится в каталоге C:\BAZA). Допустим, в результате работы была получена таблица следующего содержания (рис. 1).

Рис. 1. Пример итоговой таблицы в ACCESS

NN	FIO	QNT
1	Участок 1	553
2	Участок 2	608
3	Участок 3	196
4	Участок 4	12
5	Участок 5	12

Но ее нужно дополнить расширенной "шапкой" и итоговой строкой, например так, как показано на рис. 2.

Рис. 2. Требуемый вид таблицы для передачи другим пользователям

Итоги работы за месяц по 8-му филиалу
Дата отчета: 12 января 2002

Учетный №	Название участка	Общий итог
1	Участок 1	553
2	Участок 2	608
3	Участок 3	196
4	Участок 4	12
5	Участок 5	12
Всего по филиалу:		1381

В самом Access эту задачу можно решить с помощью механизма отчетов (Reports). Теория их создания достаточно хорошо излагается в различных пособиях и ее использование не представляет больших трудностей. Хотя, конечно, есть и небольшие хитрости, которые полезно знать. Например, можно сдвигать текстовые данные правее от левого края ячейки, если указать формат вывода для нее как !\ @. Аналогичного действия для числовых ячеек, но от правого края, можно добиться с помощью формата #\  (причем вводить его надо как #" "). Вариант отчета в режиме Конструктор по рассматриваемому примеру показан на рис. 3.

Рис. 3. Вид отчета в ACCESS в режиме Конструктор

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

Решаем поставленную задачу

Решение задачи существует, причем двумя способами - вручную и автоматически. Если подобная задача возникает редко, то можно потратить некоторое время и перебросить таблицу из Access в Excel вручную через буфер обмена (clipboard), а затем дописать к ней требуемые данные. Но если такая ситуация возникает часто (например, если требуется постоянно передавать из базы разнообразные "выписки" в виде счетов, заказов, предложений и рассылать их по электронной почте на бланке фирмы), то возникает необходимость автоматизации этого процесса, выполнить которую можно с помощью переменных типа "объект".

Общая схема

Общая схема решения при этом будет выглядеть так:

Если на компьютере установлена почтовая программа Outlook Express, то при необходимости перед закрытием Excel можно добавить команду вывода на экран диалогового окна отправки таблицы по электронной почте (к сожалению, в VBA-Excel нет команды автоматической отправки файла, аналогичной той, что есть в Access).

Рис. 4. Вид кнопки в экранной форме ACCESS

Для реализации приведенного алгоритма в Access надо предусмотреть кнопку в какой-нибудь форме (например, как на рис. 4), при нажатии которой будет запускаться следующая процедура:

Private Sub ExportExcel_Click()

 Dim DateVA As String, OPROS As Byte

 DoCmd.Hourglass True

 DateVA = "c:\baza\ITOGI.xls"

 On Error Resume Next

 Kill DateVA

 On Error GoTo 0

 DoCmd.TransferSpreadsheet acExport, 8, "In_Excel", DateVA

 Set xlObj = CreateObject("Excel.Application")

 xlObj.Application.Workbooks.Open filename:=DateVA

 xlObj.Visible = True

 Call ExcelWork

 DoCmd.Hourglass False

 OPROS = MsgBox("Отчет печатать? ", vbOKCancel + vbDefaultButton2
+ vbQuestion, "Запрос на отчет")

 If OPROS = 1 Then

  MsgBox ("Вставьте лист бумаги в принтер и нажмите <OK>")

  DoCmd.OpenReport "Итоги"

 End If

End Sub

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

Private Sub ExportExcel_Click()
 Объявить две переменные.
 Включить "песочные часы".
 В переменную DateVA занести имя таблицы в Excel-формате и путь к ней на диске.
 В случае возникновения ошибки перейти на следующий оператор.
 Попытаться удалить таблицу с таким именем (т. е. старый вариант, если он есть).
 Отключить переход на следующий оператор в случае ошибки.
 Экспортировать Access-таблицу "In_Excel" на диск в Excel-формате с именем DateVA .
 Через объектную переменную "xlObj" запустить на выполнение программу Excel (замечание: саму переменную "xlObj" желательно объявить как глобальную (Public xlObj As Object) в каком-нибудь стандартном модуле - она пригодится в дальнейшем).
 Загрузить в Excel созданную таблицу.
 Отобразить окно Excel на экране.
 Вызвать на выполнение Excel-овский макрос, хранящийся в стандартном модуле Access.
 Отключить "песочные часы".
 Вывести на экран диалоговое окно с запросом о необходимости распечатать "отчет" из Access. Результат занести в переменную OPROS.
 Если печатать нужно, то:
  вывести на экран предупреждающее сообщение;
  распечатать "отчет";
 закончить блок печати.
End Sub

Создаем макрос Excel

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

Сразу предупреждаем, макрос в этом первоначальном виде работать в Access не будет. Его нужно изменить. Рассмотрим этот процесс на примере.

Допустим, начало скопированного макроса в модуле Access имеет следующий вид (вместе со служебными строками VBA-Access и командой объявления объектной переменной):

Option Compare Database

Option Explicit

Public xlObj As Object



Public Sub Макрос1()

'

' Макрос1 Макрос

' Макрос записан 12.01.02 (Lab-511)

'

 Cells.Select

 With Selection.Font



  .Name = "MS Sans Serif"

  .Size = 12

  .Strikethrough = False

  .Superscript = False

Во-первых, надо помнить, что при автоматической записи будет формироваться довольно много лишних строк. Так, в данном примере блок With был создан макрорекордером при установке высоты шрифта в 12 пунктов. Это же действие можно выполнить одной строкой:

 Selection.Font.Size = 12

Дело в том, что VBA-Access "не понимает" чисто Excel’eвских выражений. Например, просто так команда Cells.Select им выполнена не будет. Он "не знает", кто такие Cells и как их выбирать. Решается эта проблема довольно просто - достаточно указать, что такие команды являются элементом коллекции xlObj.Application. Таким образом, после преобразования начало вышерассмотренной процедуры будет иметь следующий вид (с учетом удаления лишних команд, сгенерированных макрорекордером, и добавления команды объявления некоторых переменных):

Public Sub ExcelWork()
'===== Процедура для работы в EXCEL =========

Dim JJ1 As Integer, L1 As Integer, CLF As String

With xlObj.Application

 .Cells.Select

 .Selection.Font.Size = 12

 .Range("A1").Select

 .ActiveCell.FormulaR1C1 = "Учетный" & Chr(10) & "№"

 .Range("B1").Select

 .ActiveCell.FormulaR1C1 = "Название" & Chr(10) & "участка"

 . . . . .

End With

Причем, если удалить лишние строки, то необходимость в Excel-блоках With обычно отпадает и можно применить один блок With xlObj.Application. Если же нет желания удалять строки, то в Excel-блоках With надо тоже ставить точки перед Selection, например, так:

With .Selection.Font

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

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

With xlObj.Application

 .Cells.Select

 '(заполнение верхних строк)

 . . . . .

End With

'--- Выяснение кол-ва заполненных ячеек с учетом того, что
'--- 1-ая заполненная строка таблицы после вставки строк сверху
'--- оказывается 8-ой ---

JJ1 = 1: L1 = 8

Do While xlObj.Application.Range("A8").Offset(JJ1, 0).Value > ""

 JJ1 = JJ1 + 1

Loop

L1 = L1 + JJ1

'--- Заполнение строк снизу ---

With xlObj.Application

 'Преобр. L1 в символы с отбросом пробелов

 CLF = "A" & Trim(Str$(L1))

 .Range(CLF).Select

 .ActiveCell.FormulaR1C1 = "Всего по филиалу:"

 .Selection.Font.Bold = True

 CLF = "C" & Trim(Str$(L1))

 .Range(CLF).Select

 .Selection.FormulaR1C1 = "=SUM(R[-" & Trim(Str$(JJ1)) & "]C:R[-1]C)"

 .Selection.Font.Bold = True

 . . . . .   '(и т. д.)

Но и это еще не все. VBA-Access "не понимает" констант Excel, которые очень широко использует макрорекордер, например таких:

.Selection.HorizontalAlignment = xlCenter

.Rows("1:6").Select

.Selection.Insert Shift:= xlDown

VBA-Access "не знает" значений переменных xlCenter, xlDown и др. Их надо заменить явными значениями:

.Selection.HorizontalAlignment = -4108  'xlCenter

.Rows("1:6").Select

.Selection.Insert Shift:=-4121 'xlDown

Однако может возникнуть вопрос: где взять значения констант Excel? Ответ на него прост: в самом редакторе VBA-Excel - вызвав в нем просмотр объектов, в котором достаточно набрать имя константы, нажать на Enter и прочитать значение в нижней строке (рис. 5).

Рис. 5. Вид окна просмотра объектов при поиске значения констант VBA-Excel

Поскольку этот путь не очень удобен, лучше подготовить для себя таблицу констант, например такую:

Константа	Значение	Константа	Значение
xlAutomatic	-4105	xlFillDefault	0
xlBottom	-4107	xlContinuous	1
xlCenter	-4108	xlThin	2
xlDown	-4121	xlDiagonalDown	5
xlFormats	-4122	xlDiagonalUp	6
xlLeft	-4131	xlEdgeLeft	7
xlNone	-4142	xlEdgeTop	8
xlRight	-4152	xlEdgeBottom	9
xlUnderLineStyleNone	-4142	xlEdgeRight	10
xlUp	-4162	xlInsideVertical	11
xlValues	-4163	xlInsideHorizontal	12
		xlDialogSendMail	189

На этом преобразование макроса можно закончить. Хотя есть еще одна возможность сократить его текст. Дело в том, что команды создания рамок ячеек в Excel макрорекордер записывает в виде группы практически одинаковых блоков With, отличающихся значением исходной константы. Этот код можно разместить в отдельной процедуре с многократным ее вызовом, например так:

' - - - Вызовы процедуры обрамления - - - - -

Call LineRis(7): Call LineRis(8): Call LineRis(9)

Call LineRis(10): Call LineRis(11): Call LineRis(12)

' - - - Сама процедура - - - - -

Private Sub LineRis(NL1 As Byte)

 With xlObj.Application.Selection.Borders(NL1)

  .Linestyle="1" 'xlContinuous

  .Weight = 2  'xlThin

  .ColorIndex = -4105 'xlAutomatic

 End With

End Sub

И в заключение добавим строки записи созданной таблицы на диск и закрытия Excel:

With xlObj.Application

 .Range("A1").Select

 .ActiveWorkbook.Save

 .Application.Quit

End With

Для отправки таблицы по электронной почте этот фрагмент макроса будет выглядеть так:

With xlObj.Application

 .Range("A1").Select

 .ActiveWorkbook.Save

 .Application.Dialogs(189).Show

 .Application.Quit

End With

Момент появления диалогового окна почтовой программы (Outlook Express, которая может работать параллельно с другими почтовыми программами) показан на рис. 6. Достаточно вписать адрес, текст сообщения и нажать на кнопку Отправить.

Рис. 6. Вид окна Excel после автоматического создания таблицы и вывода на экран диалогового окна отправки по почте

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

Что касается размещения макроса Excel, то в некоторых случаях возможен и другой вариант: запуск его из файла Excel, если такой файл уже есть на диске и в нем есть макрос (например, под именем Auto_Open), который, однако, в этом случае автоматически запускаться не будет. Текст кода таков:

Set xlObj = CreateObject("Excel.Application")

xlObj.Application.Workbooks.Open filename:="c:\rasklad\rasklad.xls"

xlObj.Visible = True

xlObj.Application.Run "RASKLAD.xls!Auto_Open"

Напоследок о Word

С MS Word ситуация аналогична, поэтому конкретный пример рассматривать не будем. В этой программе тоже есть макрорекордер, с помощью которого можно составить макрос Word. Еще потребуется составить таблицу Word-констант и освоить cgtwbfkmyst операторы работы с элементами текстов Word. Нужно признать, что Excel больше подходит для обработки таблиц Access, поэтому пример и был приведен на основе Excel. Но в Word есть одна интересная команда: Сервис->Слияние, которую можно рассматривать как вариант отчета Access, но результаты ее работы могут получиться более красивыми. В частности, при проектировании отчетов в Access высота строк в области данных фиксирована, а обрамить можно только ячейки размера, установленного в Конструкторе. При заполнении они могут оказаться обрамленными не во всю высоту, если данные не помещаются по ширине и переносятся на строки ниже. А в Word этого нет - в нем все рамки остаются целыми, независимо от того, растягиваются они вниз или нет. Поэтому при широком использовании Access неразумно забывать о Word. И, видимо, поэтому в меню Access есть команда Сервис->Связи с Office->Слияние c MS Word.

Таким образом, совместное использование возможностей программ комплекта MS Office может повысить уровень автоматизации обработки таблиц с минимальными затратами на составление программных кодов.

 

Рассылка таблиц из Access

Напомним вкратце технологию автоматической рассылки "чистых" таблиц прямо из Access. Допустим, в некоторой базе есть вспомогательная таблица Адреса, куда переносится группа єлектронніх адресов некоторых клиентов из более полной базы по какому-то критерию. И есть таблица email_out, которую надо отправить по этим адресам. Для выполнения команды отправки необходимо заполнить несколько символьных переменных: ADRES1 - первый адрес; ADRESN - адреса копий; TAB_NAME - имя таблицы для отправки; TEMA - текст темы письма; MAIL_TIP - текст сообщения. Большая часть кода программы выполняет заполнение адресов (которых может не быть, или может быть только один). Вот пример из реально работавшей программы: DoCmd.Hourglass True  zapa.Close  Set zapa = ddg.OpenRecordset("Адреса", dbOpenDynaset)  If zapa.BOF Then   MsgBox ("Адресов нет, некуда отправлять!!!")  Else   zapa.MoveFirst: ADRES1 = "": ADRESN = ""   Do Until zapa.EOF    If zapa![Kod_out] Then     ADRES1 = "" Then      If Len(zapa![Email]) > 1 Then ADRES1 = zapa![Email]     Else      If Len(zapa![Email]) > 1 Then       If Len(ADRESN) > 4 Then ADRESN = ADRESN & "; "       ADRESN = ADRESN & zapa![Email]      End If     End If    End If    zapa.MoveNext   Loop  TAB_NAME = "Email_out"  TEMA = "Query from Merox Kiev"  MAIL_TIP = "Прошу сообщить условия поставки комплектующих, указанных " & _    "в прикрепленном файле. С уважением, Буроленко Э.Н. EDUARD@merox.com.ua"  DoCmd.Hourglass False  If Len(ADRES1) > 1 Then DoCmd.SendObject acTable, TAB_NAME, _     "MicrosoftExcel(*.xls)", ADRES1, "", ADRESN, TEMA, MAIL_TIP, False Последняя команда в примере - DoCmd.SendObject - отправляет по группе адресов выбранную таблицу в формате Excel. Вышеуказанную технологию можно применять в разных ситуациях, в том числе и для отправки листов рассылки в интернет-проектах. Причем отсылаемая таблица может быть не только в формате Excel, но и в других - HTML, текстовом или RTF. Этой же командой можно отправлять простые сообщения, если пропустить первые три параметра, например: DoCmd.SendObject , , , "proba@fng.cpm.ua", "", "proba2@ten.cpm.ua", "О чем-то", "Прошу сообщить", False

Сергей ГУЩЕНКО,
publish@nics.kiev.ua

До следующего выпуска!
Елена Полонская, редактор "К+П"
www.cp.comizdat.com

Перепечатка материалов этой рассылки разрешается только по согласованию с редакцией журнала "Компьютеры+Программы"