Помогите сделать действие в Excel через COM

[kashperuk]

Господа.
Я снова к вам со странной проблемой.

В Excel есть возможность группировки данных, если кто не в курсе.
Так вот, после группировки слева от рабочего листа появляются уровни, которыми можно регулировать отображение ячеек - показывать их свернутыми, или только второй уровень, или все развернутые и тд..
Также на местах группировки слева появляются плюсики/минусики, нажав на который, сворачиваем ТОЛЬКО эту группу ячеек.

Замечание.
1. Меня интересует только группировка строк
2. Код для установки общего уровня отображения (в Эксель) такой:

    ActiveSheet.Outline.ShowLevels RowLevels:=1 ()

Вопрос:
Как отобразить или свернуть ячейки определенной группы из Axapta?
Макрос в Excel что-то ничего на это действие нажатия по плюсику не записывает

[Gustav]

Цитата:

Сообщение от kashperuk

Макрос в Excel что-то ничего на это действие нажатия по плюсику не записывает

Если сделать не "плюсиком", а по меню: Данные \ Группа и структура \ Отобразить данные, то в VBA запишется оператор примерно следующего вида (Application добавил я для наглядности):

Application.ExecuteExcel4Macro "SHOW.DETAIL(1,3,TRUE,,0)"

далее в справочнике по макрофункциям Excel 4 читаем:

Цитата:

SHOW.DETAIL
Macro Sheets Only

Expands or collapses the detail under the specified expand or collapse button.

Syntax

SHOW.DETAIL(rowcol, rowcol_num, expand, show_field)

Rowcol is a number that specifies whether to operate on rows or columns of data.

Rowcol Operates on
1 Rows
2 Columns
3 The current cell's row or column. The second argument, rowcol_num, is then ignored.


Rowcol_num is a number that specifies the row or column to expand or collapse. If you are in A1 mode, you must still give the column as a number. If rowcol_num is not a summary row or column, SHOW.DETAIL returns the #VALUE! error value and interrupts the macro.

Expand is a logical value that specifies whether to expand or collapse the detail under the row or column. If expand is TRUE, Microsoft Excel expands the detail under the row or column; if FALSE, it collapses the detail under the row or column. If expand is omitted, the detail is expanded if it is currently collapsed and collapsed if it is currently expanded.

Show_Field is a string specifying the name of the field to add to a PivotTable report, if the selection is inside a PivotTable report. The new field is added as the new innermost field. Available for only innermost row or column fields.

Related Function

SHOW.LEVELS Displays a specific number of levels of an outline

P.S. Записывается макрорекордером почему-то 5 аргументов, а не 4 как сказано в хелпе. Если после записи попробовать выполнить этот оператор ExecuteExcel4Macro "SHOW.DETAIL(1,3,TRUE,,0)", то возникнет ошибка типа "Много аргументов". Достаточно в принципе первых трех.

[aidsua]

Цитата:

Сообщение от kashperuk

Господа.
Я снова к вам со странной проблемой.

В Excel есть возможность группировки данных, если кто не в курсе.
Так вот, после группировки слева от рабочего листа появляются уровни, которыми можно регулировать отображение ячеек - показывать их свернутыми, или только второй уровень, или все развернутые и тд..
Также на местах группировки слева появляются плюсики/минусики, нажав на который, сворачиваем ТОЛЬКО эту группу ячеек.

Замечание.
1. Меня интересует только группировка строк
2. Код для установки общего уровня отображения (в Эксель) такой:

    ActiveSheet.Outline.ShowLevels RowLevels:=1 ()

Вопрос:
Как отобразить или свернуть ячейки определенной группы из Axapta?
Макрос в Excel что-то ничего на это действие нажатия по плюсику не записывает

Например,
Отобразить 4 уровень:

   Sheets(Sheets(1).Name).Select
   ActiveSheet.Outline.ShowLevels RowLevels:=4, ColumnLevels:=1

[kashperuk]

Цитата:

Сообщение от Gustav

Если сделать не "плюсиком", а по меню: Данные \ Группа и структура \ Отобразить данные, то в VBA запишется оператор примерно следующего вида (Application добавил я для наглядности):

Application.ExecuteExcel4Macro "SHOW.DETAIL(1,3,TRUE,,0)"

далее в справочнике по макрофункциям Excel 4 читаем:

Похоже, что это то, что мне нужно.
А вот воспользоваться не получается из Аксапты
Пишет, что ошибка в формуле!!! Непонятно при чем здесь формула

Конкретно, следующая ошибка:

Цитата:

Метод "ExecuteExcel4Macro" в COM-объекте класса "Excel.Application" возвратил код ошибки 0x800A03EC (<неизвестно>), который означает: Ошибка в формуле.

• Для получения дополнительных сведений нажмите кнопку ''Справка''.
• Чтобы получить справку по использованию функции, выберите команду "Мастер функций" на вкладке "Формулы" (группа "Библиотека функций").
• Старайтесь не использовать вне формул знак равенства (=) и минус (-) или ставьте перед ними одинарную кавычку ( ' ).

[Gustav]

Смотри PS в моем посте выше.

[kashperuk]

Цитата:

Сообщение от Gustav

Смотри PS в моем посте выше.

лишнюю запятую, я конечно же, пробовал убирать.
+ пробовал без четвертого параметра. Ошибка та же.

[Gustav]

Покажи весь оператор, который получается в Аксапте?

[kashperuk]

m_ComApplication.ExecuteExcel4Macro("SHOW.DETAIL(1,5,TRUE)");

[Gustav]

хм... может, эта конструкция хочет вернуть какое-нибудь значение? типа как здесь: Как определить конец страницы в Excel

[kashperuk]

Цитата:

Сообщение от Gustav

хм... может, эта конструкция хочет вернуть какое-нибудь значение? типа как здесь: Как определить конец страницы в Excel

Не хотит. Что со значением, что без.
Ошибка та же.
Еще варианты пожалуйста. Буду пробовать

[Gustav]

Ну, чисто фантазирую на ходу мозговым штурмом :
- попробуй одинарные кавычки вместо двойных
- попробуй вместо TRUE и FALSE просто 1 и 0
- попробуй сначала строку подготовить отдельно, типа как здесь: Вызов Item() для коллекций Excel
- попробуй поставить четвертым параметром 0

[kashperuk]

Эх. Что ж такое-то?
Все попробовал, кстати, параллельно с тем, как ты это писал
Мозговой штурм похоже работает видимо

Кстати, попробовал только что запустить JOB отсюда
Вызов Item() для коллекций Excel
Та же ошибка.

У меня Офис 2007. Попробовал на 2003 - та же ошибка.

[Gustav]

Цитата:

Сообщение от kashperuk

Кстати, попробовал только что запустить JOB отсюда
Вызов Item() для коллекций Excel
Та же ошибка.

хм... вот это уже интересно... это уже принципиальный момент... может, какие-нить региональные настройки Виндов мешают? Например, поиграться разделителем списка: запятая или точка с запятой. Я смогу у себя проверить только в понедельник.

[Gustav]

Конечно, интересно разобраться до конца с этим SHOW.DETAIL, но находятся и другие варианты решения. Например, можно попробовать переложить на X++ следующие команды VBA:

'Скрыть детали (нажатие на "минусик")
Application.CommandBars("PivotTable").Controls( 5 ).Execute

'Отобразить детали (нажатие на "плюсик")
Application.CommandBars("PivotTable").Controls( 6 ).Execute

Можно еще попробовать воплотить в X++ такую универсальную процедуру на VBA:

Sub RunExcelMenu(ByVal menuID As Long)

    'в Аксапте все переменные будут типа COM
    Dim cmdBars As CommandBars
    Dim newCmdBar As CommandBar
    Dim ctrls As CommandBarControls
    Dim newCtrl As CommandBarControl
    
    Set cmdBars = Application.CommandBars
    Set newCmdBar = cmdBars.Add
    Set ctrls = newCmdBar.Controls
    Set newCtrl = ctrls.Add(1, menuID)
    
    newCtrl.Execute
    newCmdBar.Delete
    
End Sub

'Скрыть детали (нажатие на "минусик")
    RunExcelMenu( 464 )

'Отобразить детали (нажатие на "плюсик")
    RunExcelMenu( 462 )

А коды команд меню Excel можно узнать, например, в окне отладки:

? Application.CommandBars("PivotTable").Controls( 5 ).Id
 464 
? Application.CommandBars("PivotTable").Controls( 6 ).Id 
 462

Подпрограмма RunExcelMenu - адаптированная для последующего перевода на X++ версия подпрограммы RunMenu, заимствованная со стр. 418 из книги: Excel 2003 и VBA. Справочник программиста

[Gustav]

Понедельник. Сваял джобик. Все три способа прекрасно работают у меня...
Предлагаю призвать заинтересованную общественность прогнать джоб на своих компах и сравнить настройки Виндов, Экселя и т.п.

#define.xlListSeparator( 5 )
static void TestShowDetail(Args _args)
{
    COM xlApp, wbks, wbk, wkss, currSheet;
    COM rng, rows, currRows;
    int i;
    COMVariant dummy    = new COMVariant();
    COM cmdBars, cmdBar, ctrls, ctrl;

    str listSeparator;
    COMVariant varListSeparator;

    void runExcelMenu(int menuID)
    {
        cmdBars = xlApp.CommandBars();
        cmdBar = cmdBars.Add();
        ctrls = cmdBar.Controls();
        ctrl = ctrls.Add(1, menuID);

        ctrl.Execute();
        cmdBar.Delete();
    }
;
    xlApp = new COM('Excel.Application');
    xlApp.Visible(true);

    // определение разделителя списка
    varListSeparator = xlApp.International(#xlListSeparator);
    listSeparator = varListSeparator.bStr();
    info( listSeparator );

    wbks = xlApp.Workbooks();
    wbk = wbks.Add();

    wkss = wbk.WorkSheets();
    currSheet = wkss.Item(1);

    rng = currSheet.Range('A1');

    for (i=1; i<= 10; i++)
    {
        rng.Value2(i);
        rng = rng.Offset(1,0);
    }

    rows = currSheet.Rows();
    currRows = COM::createFromVariant(rows.Item('4:7'));
    currRows.Select();
    currRows.Group();
    xlApp.ExecuteExcel4Macro('ALERT("Пауза 1")');

    // xlApp.ExecuteExcel4Macro('SHOW.DETAIL(1,7,FALSE)'); //Скрыть детали
    xlApp.ExecuteExcel4Macro(strfmt('SHOW.DETAIL(1 %1 7 %1 FALSE)', listSeparator)); //Скрыть детали
    xlApp.ExecuteExcel4Macro('ALERT("Пауза 2")');

    // xlApp.ExecuteExcel4Macro('SHOW.DETAIL(1,7,TRUE)'); //Отобразить детали
    xlApp.ExecuteExcel4Macro(strfmt('SHOW.DETAIL(1 %1 7 %1 TRUE)', listSeparator));  //Отобразить детали
    xlApp.ExecuteExcel4Macro('ALERT("Пауза 3")');

    cmdBars = xlApp.CommandBars();
    cmdBar = cmdBars.Item('PivotTable');
    ctrls = cmdBar.Controls();

    ctrl = ctrls.Item( 5 );
    ctrl.Execute(); //Скрыть детали
    xlApp.ExecuteExcel4Macro('ALERT("Пауза 4")');

    ctrl = ctrls.Item( 6 );
    ctrl.Execute(); //Отобразить детали
    xlApp.ExecuteExcel4Macro('ALERT("Пауза 5")');

    runExcelMenu( 464 ); //Скрыть детали
    xlApp.ExecuteExcel4Macro('ALERT("Пауза 6")');

    runExcelMenu( 462 ); //Отобразить детали
}

[Gustav]

Короче, всё ясно. Дело в разделителе списка в региональных настройках Windows. Если он установлен как "точка с запятой", то и в SHOW.DETAIL параметры должны перечисляться через точку с запятой:

xlApp.ExecuteExcel4Macro('SHOW.DETAIL(1;7;FALSE)'); //Скрыть детали

Это что касается использования именно SHOW.DETAIL. Если не заморачиватсья с определением текущего разделителя списка, то можно просто пойти другим путем и сделать через CommandBars (см. выше).

[kashperuk]

Спасибо. Действительно, видимо дело было в разделителях списков.
После изменения запятых на точки с запятой ошибка перестала вываливаться.

НО.
Группировка тоже не происходит так: SHOW.DETAIL(1;7;FALSE)

Через CommandBars проходит обоими вариантами.

НО.
В JOB срабатывает, а в реальном классе - не хочет.
Возможно дело в том, что я использую не Rows, а Range? Но врядли.
Еще отличие - я снял галочки про итоги в настройках шаблона. (то есть плюсик - сверху над сгруппированными строками).
Но тоже не то - попробовал запускать Job с шаблоном таким-же. Там сворачивает.

Gustav, с нетерпением жду след. идей и кода

[kashperuk]

Ага. Разобрался. Все дело с вызовом этих команд - нужно делать select() перед вызовом.

Давай думать, как сделать без выделения.

[Gustav]

Ну, если без выделения, тогда нужно использовать SHOW.DETAIL - там же есть второй параметр "номер строки/колонки". Но, как мы выяснили, для использования SHOW.DETAIL нужно точно знать ListSeparator. Оказывается, Excel предоставляет такую возможность (сам впервые сегодня к этому прикоснулся ):

? Application.International( xlListSeparator )
;

'где константа xlListSeparator = 5

А чем плохо выделить и приложить CommandBar'ом?

[kashperuk]

А SHOW.DETAIL не работает у меня.

кстати, xlApp.ExecuteExcel4Macro('ALERT("Пауза 5")'); тоже ничего не делает.
Хотя, я так понял, должно вывестись какое-то окно с текстом этим?

Да ничем не плохо. Просто хочется, чтобы позиция не менялась у выделенной ячейки.
сделал просто при показе перемещение в требуемую ячейку фокуса.
Спасибо, еще раз, за код.