Некоторые "фокусы" при работе стаблицами в VBA for MS Word

Предисловие
В настоящее время основная часть документооборота происходит в документах MS Word и многие из них содержат таблицы. Иногда просто необходимо представить данные из удаленной базы данных (MS SQLServer, DB2, Oracle и т.д.), в связи с чем приходится решать проблемы скорости импортирования данных из БД в документы MS Word.

Постановка задачи
Сформулируем задачу: необходимо импортировать данные, находящиеся на MS SQLServer 6.5, в документ MSWord, и будем использовать Visual Basic for Application для решения данной проблемы. Многие знают, что данную операцию можно выполнить средствами MSWord, не прибегая к помощи VBA (если вызвать панель базы данных и самим создавать источники базы данных и, используя средства MSQuery, строить запросы к БД), но не многие пользователи обладают достаточными знаниями для настроек источников БД (Data Source) и, конечно, не многие знают, какую необходимо вызвать хранимую процедуру (Stored Procеdure) и какие указать параметры для получения необходимой информации. В связи с этим программистам приходится решать эти проблемы с помощью VBA. Будем считать, что у нас на сервере инсталлирован MS SQLServer 6.5, на котором находится некоторая БД, и на машине клиента установлен ODBC. Клиент имеет свой Login на сервере (авторизация происходит на уровне сервера), и для него настроены все права (permissions) для доступа к данным (таблицам, хранимым процедурами т.д.).

"Общие приготовления"
В MicroSoft Visual Basic создадим форму UserForm для ввода некоторых параметров и сделаем так, что она будет сразу вызываться при запуске макроса:
Sub mine()
UserForm.Show
End Sub

Допустим, форма имеет следующий вид:

Как видно, форма имеет поля редактирования для ввода имени и пароля клиента (эти данные будут использоваться для подключения к серверу БД через ODBC) и возможность ввода и выбора реквизитов, которые будут использоваться как параметры вызываемой хранимой процедуры. Таким образом, мы будем вызывать хранимую процедуру на сервере с указанием необходимых параметров (но так как вызов хранимой процедуры осуществляется посредством обычного SQL, то возможно также исполнение SELECT-конструкций).

Первый "страшный" подход
Попробуем решить проблему, не зная досконально VBA for MSWord, и первым делом приходит в голову идея создать таблицу, выполнить запрос и после получения данных заполнить каждую ячейку таблицы (конечно же, используя циклы). Вот код исполнения данного "замысла":
Задаем строку для подключения к БД, где:
ODBC — указывает на то, что подключение к серверу будет производиться через ODBC, а не через ядро Microsoft Jet
DSN — имя DataSource добавленного в ODBC Manager
UID — имя пользователя
PWD — пароль
Database — имя БД на сервере

connstring = "ODBC;DSN=mssql;UID=" + strUID + ";PWD=" + strPassword + ";Database=budget"

Dim dbs As Database
Dim qdf As QueryDef
Dim rst As Recordset

Открываем БД на сервере
Set dbs = OpenDatabase("mssql", _
dbDriverNoPrompt, True, _
connstring)

Set qdf = dbs.CreateQueryDef("")

Задаем параметры объекта RecordSet (sqlstring — SQL-конструкция типа "EXEC sp_p_returndata" либо "SELECT * from ...")
With qdf
.Connect = connstring
.Sql = sqlstring
Set rst =.OpenRecordset()
If rst.RecordCount = 0 Then
MsgBox ("Данные отсутствуют")
GoTo Error
End If
End With

ActiveDocument.PageSetup.Orientation = wdOrientLandscape
Set myrange = ActiveDocument.Range(Start:=0, End:=0)

Создаем таблицу в документе (заранее известно, что в таблице будет 23 колонки — параметр NumColumns)

ActiveDocument.Tables.Add Range:=myrange, NumRows:=rst.RecordCount + 1, NumColumns:=23

Двигаясь по всем ячейкам, заносим данные из RecordSet в таблицу
For I = 2 To rst.RecordCount + 1
For K = 0 To 22
ActiveDocument.Tables(1).Cell(I, K + 1).Range = rst.Fields(K).Value
Next K
rst.MoveNext
Next I

ActiveDocument.Tables(1).AutoFormat

В результате получаем таблицу, заполненную необходимыми данными, где все прекрасно работает, изумительно отформатированную, содержащую точные данные. Но такие мысли греют душу программиста до того момента, пока запрос возвращает не более 100 строк. Но все эти восхищения выполненной работой проходят после того, как вы пытаетесь выполнить запрос, который возвращает 1500 строк (в БД больших предприятий это просто мизерный набор данных). И после того как вы прождете полдня и ничего не увидите, вы поймете, что здесь что-то не так. Тогда программисту остается либо обучать всех пользователей работе с БД в MSWord, что является сущим наказанием, либо искать другие варианты решения проблемы, либо переходить из "программистов в пользователи". Конечно же, существует оптимальное решение проблемы.

Второй "приятный" подход
Теперь обратим взор к Excel. В Excel проблема решена тем, что в нем существует возможность импортирования данных через Range (которая по сравнению с Word показывает рекорд по времени, что логично, так как Excel изначально и разработан для работы с табличными данными). И не верится, что Microsoft "обидела" Word данной возможностью. Теперь приведем текст, который выполняет импорт данных из БД другим методом:

Задаем строку для подключения к БД
connstring = "ODBC;DSN=mssql;UID=" + strUID + ";PWD=" + strPassword + ";Database=budget"

Dim dbs As Database
Dim qdf As QueryDef
Dim rst As Recordset

Открываем БД на сервере
Set dbs = OpenDatabase("mssql", _
dbDriverNoPrompt, True, _
connstring)

Импортируем данные из БД
With Selection
.Collapse Direction:=wdCollapseEnd
.Range.InsertDatabase Format:=wdTableFormatSimple2, Style:=16, _
LinkToSource:=False,Connection:=connstring, SQLStatement:=sqlstring
End With

Как видно, исходный текст уже стал намного короче (чем же не улучшение?). Уже отсутствует "явное" создание таблицы в документе, нет никаких циклов!!! Если сравнить быстродействие первого примера и второго, то скорость второго просто поражает.
Конечно, интересно, за счет чего это достигается, но пока Microsoft не открыла свои исходные тексты программ, это остается загадкой. Если посмотреть на параметры последнего оператора, то интересным является параметр Style, который позволяет изначально задавать стиль форматирования таблицы, которая получается в результате его выполнения, т.е. нет никакой потребности в вызове оператора ActiveDocument. Tables(1).AutoFormat (a в MS Word этот оператор выполняется довольно долго), что, конечно же, влияет на скорость.

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

Козловский Сергей