1

Тема: WSH: преобразуем макрос VBA в скрипт VBScript

При автоматизации Microsoft Office средствами Windows Script Host довольно часто бывает проще записать макрос непосредственно в самом приложении Microsoft Office, а затем перенести текст полученного макроса Visual Basic for Apllication в скрипт Windows Script Host. Как правильно осуществить этот перенос?

Далее упоминания об использовании обычного скрипта *.vbs обозначены как VBS, об использовании формата скрипта *.wsf (WSH: пишем сценарии в формате WSF) — как WSF.


1. Константы

При переносе констант Microsoft Office в скрипт Windows Script Host нужно помнить следующее:

VBS: нужно вручную описать все используемые константы посредством ключевого слова Const.
WSF: достаточно добавить тэг reference, либо же использовать методику, изложенную в предыдущем пункте.

2. Оператор «:=», именованные и необязательные аргументы

Оператор «:=» присваивания значения некоторому параметру метода (используемый в макросах VBA для сокращения записи, когда задаются значения не всех параметров, либо параметры перечисляются в порядке, отличающемся от заданного прототипом) в Windows Script Host не поддерживается, поскольку Windows Script Host не поддерживает именованные аргументы, а только позиционные. Посему требуется убрать наименования аргументов (вместе с оператором «:=») и расставить аргументы в правильную позицию, так, как это описано в объектной модели того или иного метода или события. В большинстве случаев при работе с приложениями Microsoft Office необязательные аргументы допускается просто пропускать.

3. Глобальные объекты, раннее и позднее связывание

В глобальном контексте приложения Microsoft Office обычно наличествуют один или более глобальных объектов, таких, как объект Apllication целевого приложения, объекты CommandBars, FileDialog, Scripts (и т.д.) объектной модели самого Microsoft Office, объект UserForms объектной модели Visual Basic for Apllication. Ссылки на библиотеки, реализующие поддержку этих объектов изначально присутствуют в свойствах проекта VBA (\Tools\References…). В отличие от VBA, поддерживающего как раннее, так и позднее связывание, Windows Script Host поддерживает только позднее связывание при создании объектов. Исходя из вышеизложенного, надо помнить, что любой глобальный объект Microsoft Office требует явного предварительного создания перед использованием, как правило, завершения работы приложения методом «.Quit()» и очистки объектной переменной путём присвоения ей значения Nothing.

VBS:

Set <Var> = WScript.CreateObject("<ProgID>")
…
<Var>.Quit
Set <Var> = Nothing

WSF: объект может быть создан при помощи тэга object, либо может быть использована та же методика, что и для VBS.

Замечание: последний способ не рекомендуется использовать для Microsoft Word, ибо может возникать ситуация (при вызове скрипта *.wsf с параметром «/?», чтобы посмотреть справку скрипта), когда приложение останется в памяти после завершения работы скрипта. Особенности поведения приложений Microsoft Office при использовании технологии Automation изложены в KB288902: GetObject and CreateObject behavior of Office automation servers, в частности:

Note For Word, the UserControl property is read-only. It cannot be set to True or False. Word always remains running when the last reference is released.

Поэтому для Microsoft Word рекомендуется использовать методику, описанную в предыдущем пункте — непосредственное создание объекта в коде и завершение его методом «.Quit()».

4. Обработка событий

Поддержка обработки событий в Windows Script Host для объектов Microsoft Office осуществляется стандартными способами.

VBS:

Set <Var> = WScript.CreateObject("<ProgID>", "<Prefix>")
…
Sub <Prefix><Event>(<Argument List>)
    …
End Sub

либо же:

Set <Var> = WScript.CreateObject("<ProgID>")

WScript.ConnectObject <Var>, "<Prefix>"
…
WScript.DisconnectObject <Var>

WSF: можно добавить атрибут events со значением «true» в тэг «object». Кроме того, поддерживается использование методики, описанной в предыдущем пункте.

Замечание: к сожалению, большую часть событий приложений Microsoft Office обрабатывать не удаётся.

2

Re: WSH: преобразуем макрос VBA в скрипт VBScript

Рассмотрим практический пример переноса макроса, записанного макрорекордером в приложении Microsoft Office, в скрипт Windows Script Host.

Например, откроем документ Microsoft Excel, выделим всю использованную область на первом рабочем листе, установим для выделенной области выравнивание и шрифт текста, сменим фон ячеек, отпечатаем два экземпляра рабочего листа, сохраним и закроем документ.

Запускаем Microsoft Excel, в меню Сервис выбираем пункт Макрос, затем Начать запись. В появившемся диалоговом окне Запись макроса убеждаемся, что в списке Сохранить в: указано Эта книга, нажимаем OK. Запись макроса началась.

Откроем документ «Hello World.xls» из папки «C:\Temp». Выделим всю использованную область первого рабочего листа. Для этого перейдём в первую ячейку (Ctrl-Home), затем выделим все ячейки, вплоть до последней использованной (удерживая Shift, нажимаем Ctrl-End). установим для выделенной области выравнивание текста в ячейках (Формат, Ячейки…): по горизонтали — по левому краю, по вертикали — по центру. Фон ячеек зададим светло-голубой. Поменяем шрифт выделения на «Verdana». Отпечатаем два экземпляра рабочего листа. Сохраним документ и закроем его. Остановим запись (Сервис, Макрос, Остановить запись).

Теперь откроем редактор VBA (Сервис, Макрос, Редактор Visual Basic). В окне проекта (View, Project Explorer) откроем наш проект (VBAProject (Книга1)), в нём последовательно раскроем Modules, Module1 и посмотрим текст записанного макроса:

Option Explicit

Sub Макрос1()
'
' Макрос1 Макрос
' Макрос записан 31.03.2011 (UserName)
'
'
    ChDir "C:\Temp"
    Workbooks.Open Filename:="C:\Temp\Hello World.xls"
    Range("A1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection.Font
        .Name = "Verdana"
        .FontStyle = "обычный"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    With Selection.Interior
        .ColorIndex = 20
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
    End With
    ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True
    ActiveWorkbook.Save
    ActiveWorkbook.Close
End Sub

Скрипт Windows Script Host, основанный на данном макросе, может выглядеть так:

Option Explicit

Const xlLeft   = &HFFFFEFDD
Const xlCenter = &HFFFFEFF4

Dim objExcel


Set objExcel = WScript.CreateObject("Excel.Application")

With objExcel
    With .Workbooks.Open("C:\Temp\Hello World.xls")
        With .Sheets.Item(1)
            With .UsedRange
                .HorizontalAlignment = xlLeft
                .VerticalAlignment   = xlCenter
                
                .Font.Name           = "Verdana"
                .Interior.ColorIndex = 20
            End With
        
            .PrintOut , , 2
        End With
        
        .Save
        .Close
    End With
    
    .Quit
End With

Set objExcel = Nothing

WScript.Quit 0

Рассмотрим процесс преобразования.

Для работы с объектом — его сначала нужно создать:

Set objExcel = WScript.CreateObject("Excel.Application")

Далее, везде где можно, используем оператор With…End With (в том числе и вложенные), чтобы сократить код и время исполнения скрипта:

With objExcel
    With .Workbooks…

Открытие документа в макросе:

    ChDir "C:\Temp"
    Workbooks.Open Filename:="C:\Temp\Hello World.xls"

преобразуем в скрипте в непосредственное указание документа для открытия:

With objExcel
    With .Workbooks.Open("C:\Temp\Hello World.xls")

Переходы и выделение в макросе:

    Range("A1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

преобразуем в скрипте в непосредственное указание использованного диапазона ячеек посредством свойства рабочего листа .UsedRange:

        With .Sheets.Item(1)
            With .UsedRange

Из множества записанных макрорекордером свойств:

    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        …

берём только те, которые мы меняли:

                .HorizontalAlignment = xlLeft
                .VerticalAlignment   = xlCenter
                
                .Font.Name           = "Verdana"
                .Interior.ColorIndex = 20

Использованные константы Microsoft Excel, как и было сказано в первом посте этой темы, перечисляем в начале скрипта:

Const xlLeft   = &HFFFFEFDD
Const xlCenter = &HFFFFEFF4

Вывод на печать:

    ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True

преобразуется по правилам, указанным в первом посте этой темы — убираем именованные параметры, вместо них используем позиционные согласно прототипу метода:

PrintOut Method

expression.PrintOut(From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate, PrToFileName)

необязательные параметры не указываем:

            .PrintOut , , 2

При сохранении и закрытии рабочей книги работаем именно с ней:

    With .Workbooks.Open("C:\Temp\Hello World.xls")
        …
        .Save
        .Close
    End With

а не просто с активной рабочей книгой, как записал макрорекордер VBA.

Закрываем приложение и очищаем объектную переменную:

…
    .Quit
End With

Set objExcel = Nothing

В формате *.wsf скрипт будет выглядеть практически так же:

<?XML version="1.0" standalone="yes" encoding="windows-1251" ?>
<job id="Hello World">
    <?job error="True" debug="True" ?>
    <runtime>
        <description>Example of conversion VBA macros to WSH script</description>
    </runtime>
    
    <reference guid="{00020813-0000-0000-C000-000000000046}" />
    <object id="objExcel" progid="Excel.Application" />
    
    <script language="VBScript">
        <![CDATA[
            Option Explicit
            
            With objExcel
                With .Workbooks.Open("C:\Temp\Hello World.xls")
                    With .Sheets.Item(1)
                        With .UsedRange
                            .HorizontalAlignment = xlLeft
                            .VerticalAlignment   = xlCenter
                            
                            .Font.Name           = "Verdana"
                            .Interior.ColorIndex = 20
                        End With
                        
                        .PrintOut , , 2
                    End With
                    
                    .Save
                    .Close
                End With
                
                .Quit
            End With
            
            WScript.Quit 0
        ]]>
    </script>
</job>

но с большими удобствами, как-то:
* объект objExcel может быть создан при помощи тэга object;
* поскольку можно задать ссылку на библиотеку типов посредством тэга reference — не требуется явно описывать константы.

3

Re: WSH: преобразуем макрос VBA в скрипт VBScript

Спасибо за статью. Имеет ли смысл работать в направлении создания переводчика из VBA в VBS и наоборот?

4

Re: WSH: преобразуем макрос VBA в скрипт VBScript

2 alexii: Почитал. Взгрустнулось....:rolleyes: Вот найти бы способ отдельно от [Word / Excel] Application выполнять макросы VBA... Мечты мечты..

Передумал переделывать мир. Пашет и так, ну и ладно. Сделаю лучше свой !