1

Тема: VBS/VBA: искользование ADOX для создания файлов XLS без Excel

Microsoft® ActiveX® Data Objects Extensions for Data Definition Language and Security (ADOX) - расширение объекта ADO(компонент имеет описание "Microsoft ADO Ext. 2.8 for DDL and Security"). Данный компонент предоставляет дополнительные возможности, как то:
- создание, изменение и удаление объектов схемы(таблицы, процедуры, представления)
- управление объектами безопасности БД(пользователями и группами), а также предоставление или непредоставление прав на определённые действия над объектами схемы конкретным группам или пользователям.

Некоторые возможности ADOX могут поддерживаться или нет - в зависимости от поставщика данных(MSDN: Provider Support for ADOX (ADO)).

С практической точки зрения(т.е. для использования в скриптах и VBA), как мне кажется, наиболее интересна возможность создания файла-каталога в отсутствии на компьютере соответствующего установленного приложения. Например, создание XLS без установленного Excel, MDB без установленного Access.
Структуру объекта можно посмотреть тут: MSDN: ADOX Object Model
http://i.msdn.microsoft.com/dynimg/IC55588.gif(c) MSDN

В общем случае для создания файловой БД достаточно воспользоваться методом .Create объекта Catalog. Например для MDB файла:


Option Explicit
Dim cat
Set cat = CreateObject("ADOX.Catalog")
cat.Create "Provider='Microsoft.Jet.OLEDB.4.0';Data Source='new.mdb'"
Set cat = Nothing

В результате в папке со скриптом будет создана пустая база в формате Microsoft Access.

С файлами формата XLS дело обстоит несколько сложнее. Так как не бывает пустой книги Excel, то невозможно создать пустой файл. В нём обязательно должен присутствовать хотя бы один лист(объект Table). Статья на MSKB: MSKB: How To Use ADOX with Excel Data from Visual Basic or VBA, машинный перевод статьи(пользоваться желательно с английским вариантом, ибо перевод весьма несовершенен). Поэтому для создания книги метод .Create неприменим, нужно задать соединение с каталогом, добавить в него таблицу содержащую хотя бы одну колонку. Простейший пример:


Option Explicit
Const adDouble = 5, adDate = 7, adCurrency = 6, adBoolean = 11, adVarWChar = 202, adLongVarWChar = 203

Dim cat
Dim tbl
Dim col
Set cat = Createobject("ADOX.Catalog")
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=cbook1.xls;Extended Properties=Excel 8.0"
Set tbl = Createobject("ADOX.Table")
tbl.Name = "TestTable"
Set col = Createobject("ADOX.Column")
With col
    .Name = "Col1"
    .Type = adVarWChar
End With
tbl.Columns.Append col
cat.Tables.Append tbl

Set col = Nothing
Set tbl = Nothing
Set cat = Nothing

После успешного создания базы данных(файла Excel) свойство ActiveConnection нашего объекта cat будет содержать ссылку на объект ADODB.Connection соединения с вновь созданной базой данных. Таким образом можно не переоткрывая созданный файл, сразу приступить к его наполнению:


Option Explicit
Const adDouble = 5, adDate = 7, adCurrency = 6, adBoolean = 11, adVarWChar = 202, adLongVarWChar = 203

Dim cat
Dim tbl
Dim col
Set cat = Createobject("ADOX.Catalog")
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=cbook3.xls;Extended Properties=Excel 8.0"
Set tbl = Createobject("ADOX.Table")
tbl.Name = "TestTable"
Set col = Createobject("ADOX.Column")
With col
    .Name = "Col1"
    .Type = adVarWChar
End With
tbl.Columns.Append col
Set col = Nothing
Set col = Createobject("ADOX.Column")
With col
    .Name = "Col2"
    .Type = adVarWChar
End With
tbl.Columns.Append col
cat.Tables.Append tbl

Dim i, oRs, oCon, oCmd
Set oCon = cat.ActiveConnection

For i = 1 To 10
  Set oRs = oCon.Execute("INSERT INTO TestTable VALUES ('"& i & "', '" & i*2 & "')")
Next

Set oRs = Nothing
Set oCon = Nothing
Set col = Nothing
Set tbl = Nothing
Set cat = Nothing

В данном случае лучше было использовать другой тип данных(например, adDouble). При этом не существенно, передаются в запросе число как число или как стока - если тип данных колонки строковый, то в ячейку заносится именно строка. Если ячейка имеет числовой тип, а передаваемое значение неявно может быть приведено к данному типу, то в ячейку добавляется число. Если неявное приведение невозможно(например, системный десятичный разделитель это точка, а вставить пытаемся значение '10,32'), то получим ошибку "Microsoft JET Database Engine: Несоответствие типов данных в выражении условия отбора.".
Стоит также обратить внимание на то, что строковые данные в ячейке начинаются с символа апострофа. Возможно в каких-то ситуациях это может быть критично. На значение ячейки в VBA это не влияет. При обращении к этим данным через ADO апостроф также игнорируется - значения получаются без него. Для ранее созданного(в предыдущем примере) файла код:


Option Explicit
' константы для работы с ADO
Const adUseClient = 3 : Const adSchemaTables = 20 : Const adSchemaColumns = 4
' Типы данных ADOX, доступные для Excel:
Const adDouble = 5, adDate = 7, adCurrency = 6, adBoolean = 11, adVarWChar = 202, adLongVarWChar = 203

Dim oConn
Set oConn = CreateObject("ADODB.Connection")
With oConn
  .Provider = "Microsoft.Jet.OLEDB.4.0"
  .Properties("Extended Properties").Value = "Excel 8.0;"
  .CursorLocation = adUseClient
  .Open "cbook3.xls"
End With

Dim oRs
Set oRs = oConn.Execute("SELECT * FROM TestTable")

Do While Not (oRs.EOF)
  wscript.echo "[" & oRs("Col1").Value & "]; [" & oRs("Col2").Value & "]"
  oRs.MoveNext
Loop

Set oRs = Nothing
Set oConn = Nothing

выведет:

[1]; [2]
[2]; [4]
[3]; [6]
[4]; [8]
[5]; [10]
[6]; [12]
[7]; [14]
[8]; [16]
[9]; [18]
[10]; [20]

Как видим, никаких апострофов .

Есть несколько способов обратиться к таблицам в файле XLS (MSKB: Пример использования технологии ADO для чтения и записи данных в книге Excel):
-Имя листа, а затем знак доллара (например, [Лист1$] или [Мой лист$]). Книги таблицы, на который ссылается таким образом состоит из всего используемого диапазона листа.
           oRS.Open "Select * from [Sheet1$]", oConn, adOpenStatic
- Использовать диапазон с определенным именем (например, ["Table1"]).
           oRS.Open "Select * from Table1", oConn, adOpenStatic
- Использовать диапазон с конкретного адреса (например, [Лист1$ A1: B10]).
           oRS.Open "Select * from [Sheet1$A1:B10]", oConn, adOpenStatic

В примере использован второй вариант, который в данном случае даёт тот-же результат что и первый([TestTable$]). Но в прочих ситуациях результат может быть иной(если в книге создавался диапазон с именем листа). Поэтому для только что программно созданного файла это некритично, в прочих случаях - если нужно обратиться именно к листу, лучше обращаться именно к нему.

Вообще, число типов данных доступных для работы с XLS файлом достаточно ограничено(хотя, наверное, для большей части задач этих типов достаточно):
           adDouble, type 5
           adDate, type 7
           adCurrency, type 6
           adBoolean, type 11
           adVarWChar, type 202 [строка в Юникоде длиной в 255 символов (DT_WSTR)]
           adLongVarWChar ("memo"), type 203 [текстовый поток в Юникоде (DT_NTEXT)]
Примечание: приведено имя константы и её численное значение.

Полный список доступных в ADOX(или ADO - ибо в данном контексте это одно и тоже) типов данных можно найти в MSDN: MSDN: DataTypeEnum.

Главным слабым местом данного способа является то, что файл рассматривается как БД, и работа с файлом ведётся как с БД. Как следствие - формула добавленная в ячейку будет всего лишь строкой с текстом формулы. Превратить текст в формулу минуя Excel.Application видимо не удастся. При этом для "родного" файла Excel достаточно переввести формулу средствами Excel, а для файла созданного через ADOX придётся ещё и апостроф убирать(из макроса достаточно переприсвоить значение, т.к. апостроф там не виден: Selection.Value = Selection.Value).

Итого: формулы, форматирование, сложные отчёт - для этого потребуется один из офисов, для вывода данных(с названием колонок или без) в формат XLS на компьютере где отсутствует Microsoft Office вполне можно воспользоваться ADOX. Полученный файл корректно открывается и в OOo.

В статье по программному созданию XLS файлов через ADOX резюмируется:

Conclusion
   ADOX works best with Excel when a new table and its columns are all created at the same time.
   ADO works best with Excel data when that data is stored in a worksheet that is created using ADOX.

В чём заключается преимущество, позволяющее "работать лучше" в упомянутых ситуациях, я сказать затрудняюсь.

P.S. Изначально этот объект заинтересовал меня для вывода данных на компьютере без офиса из adp-шного проекта MS Access 2003(запускается из рантайма). Так вот, выяснилось что:

В Microsoft Office Access 2003 или Microsoft Access 2002 запрещается вносить изменения, добавлять или удалять данные, источником которых являются книги Excel.
<...>
Из-за возникающих юридических вопросов корпорация Майкрософт отключила функции в Access 2003 и в Access 2002, которые позволяли пользователям изменять данные в связанных таблицах, которые указывали на диапазон в книге Excel.

Другими словами, через Application.Excel "можно всё", при доступе через ADO/DAO из MS Access - только почитать.

При работе из Excel такого ограничения нет, но при наличии Excel уже есть способы более гибкой работы с этим форматом . Работу под VBA в  MS Word я не проверял.
P.P.S. Если всё-таки нужно выводить данные из Microsoft Access, рекомендую обратить внимание на встроенные(как оказалось ) методы DoCmd.Output и DoCmd.TransferSpreadsheet. Второй из них, судя по темам на SQL.RU, даёт возможность выгружать данные на разные листы одной книги, при этом присутствует возможность задать её название. Первый метод проверен, второй пока на уровне ОБС.