К списку форумов К списку тем
Регистрация    Правила    Главная форума    Поиск   
Имя: Пароль:
Рекомендовать в новости

Как добавить на ленту Excel 2007 вкладку с кнопками для своих макросов

Гость
0 - 01.03.2013 - 15:22
Как добавить на ленту Excel 2007 вкладку с кнопками для своих макросов

Выставляю на суд читателей свой опыт. Может кому-нибудь пригодится.

Как известно для автоматизации обработки данных в книге, некоторые продвинутые пользователи :) используют самописные макросы.

По умолчанию макросы сохраняются в созданной книге в формате xlsm. Хотя существует возможность сохранять их в «Все открытые книги», «Эта книга», «%название файла%».

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

Можно создать двоичный файл %имя файла%.xlsb(по умолчанию personal.xlsb), со своими макросами, поместить его в «C:\Users\%имя пользователя%\AppData\Roaming\Microsoft\Excel\XLST ART», тогда этот %имя файла%.xlsb , будет загружаться автоматически при открытии Excel2007. Макросы в новых документах можно вызывать через эту автоматически загруженную книгу (удобно назначать макросам быстрые клавиши «типа Ctrl+S»).

Если вас устраивает такое решение можете дальше не читать.

Мы пойдем другим путем :). Мне захотелось вынести кнопки вызова моих макросов на ленту(ribbon) Excel. К сожалению, в Excel 2007 стандартными возможностями вынести кнопки макросов возможно только на панель быстрого доступа. Мне такое решение не понравилось из соображений эстетики :).

Хочу сразу предупредить, статья поможет на примере вникнуть в суть процесса, остальные «допиллинги», «свистоперделки» и т.д. на вашем усмотрении. Кому не нравится мой стиль описания, милости прошу : http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

Итак, приступим.
Начальные условия:
1. Вы продвинутый пользователь или «круче» :) и знаете что такое: лента Excel, надстройки, VBA, XML. Если нет, рекомендую погуглить эти слова :), так как нет желания возится с картинками и пошагово писать инструкцию типа «нажмите ЛКМ на крестик чтобы закрыть окно, как на рис.1».
2.У меня было: ОС Windows 7, MS Office 2007, Блокнот :), файл с макросами.
3.Естественно, в Excel должна быть правильно настроена безопасность.

Работаем :)

Лента Excel, настраивается через XML. Соответственно основные «пляски» будут вокруг этого стандарта.
1. Создаем новую книгу. Для примера назовем ее «empty». Включаем (если нет) на ленте вкладку «Разработчик» («Файл»-> «Параметры Excel» -> «Основные» -> «Показывать вкладку «Разработчик» на ленте»).

2. Сохраняем книгу как %имя будующей надстройки%. xlam(это расширение для надстроек Excel). Для нашего примера empty.xlam. Может вы заметили куда сохранился этот файл? Если нет, то вот стандартный путь к нему «C:\Users\%имя пользователя%\AppData\Roaming\Microsoft\AddIns».

3. Открываем Visual Basic(VB) (Alt+F11).

4. В окне VB слева окошко Project Explorer (если нет, то Ctrl+R или «View»->«Project Explorer»). В нем дерево проекта. Создаем в проекте модуль(«ПКМ на «VBAProject(empty)» -> «Insert»-> «Module»). В модуль вставляем наши макросы.

5. Для того чтоб макросы вызывались кнопкой с ленты необходимо в него передать параметр (ByVal Control As IRibbonControl). Например:

a. Sub Макрос1 (ByVal Control As IRibbonControl)
b. ‘ текст вашего макроса
c. End Sub

6. Отставляем в сторону Excel :).

7. Как по мне, лучше Norton Commander и нет менеджера в Винде. Но за неимением его запускаем FreeComander, он же нам пригодится для быстрого редактирования и создания файлов. Бежим по пути из п.2, копируем empty.xlam в вашу папку где вы будете курочить этот файл.

8. Как вы устели заметить всякие xml файлики(а *.xlam похоже что он и есть), почти не отличаются от архива, ну по крайней мере архиваторами типа 7-Zip они открываются. Переименовываем empty.xlam в empty.zip. Распаковываем empty.zip в папку empty.

9. Заходим в папку и видим следующую структуру (у меня так, из-за сортировки по имени):

a. Папка «empty»
i.Папка «_rels»
1. .rels
ii.Папка «docProps»
1. app.xml
2. core.xml
iii.Папка «xl»
1.Папка «_rels»
a. workbook.xml.rels
2.Папка «theme»
a. theme1.xml
3.Папка «worksheets»
a. sheet1.xml
b. sheet2.xml
c. sheet3.xml
4. styles.xml
5. vbaProject.bin
6. workbook.xml
iv.[Content_Types].xml
10. Создадим в папке «empty», папку «customui». В «customui» создадим файл customui.xml(можно Блокнотом), со следующим содержимым( без цифр
1.-14., здесь они для нужны для описания):

1.<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
2.<ribbon >
3.<tabs>
4.<tab id="MYTab" label="MYTABlabel" insertBeforeMso="TabInsert">
5.<group id="group1" label="group1label">
6.<button id="customButton1" label="custombutton1label" size="large" onAction="custombutton1macros1" />
7.</group>
8.<group id="group2" label=" group2label ">
9.<button id="customButton2" label=" custombutton2label " size="large" onAction=" custombutton2macros2" />
10.</group>
11.</tab>
12.</tabs>
13.</ribbon>
14.</customUI>

Описание, по строкам:
1. Тип xml – стандартно.
2. Начало описания ленты (ribbon).
3. Начало описания вкладок.
4. Создание вкладки «MYTAB» с названием «MYTABlabel» которая вставится перед стандартной вкладкой «TabInsert», по русски «Вставка».
5. Создание группы «Group1» во вкладке «MYTAB».
6. Создание кнопки «customButton1» с названием «custombutton1label» размера «large» с вызовом «onAction» =«custombutton1macros1» (вот зачем в макрос надо передавать значение «ByVal Control As IRibbonControl»). Тут надо пояснить, что названия макроса в «onAction» должно совпадать с названиями ваших макросов.
7. Закрываем раздел «group».
8. Тоже что и в п.5 для «group2».
9. Тоже что и в п.6 для «customButton2».
10. Закрываем раздел «group».
11. Закрываем описание вкладки «MYTAB».
12. Закрываем описание вкладОК.
13. Закрываем описание ленты.
14. закрываем описание xml.

Подробности здесь: http://novikovmaxim.narod.ru/index.h...2007/index.htm

11. Сохраняем наш customui.xml

12. Теперь добавим отношений :). В папке «_rels» (см. п.9.a.i), редактируем файл .rels:
a. Добавляем в файл следующую фразу: «<Relationship Id="R368da2b30bd84589" Type="http://schemas.microsoft.com/office/2006/relationships/ui/extensibility" Target="customUI/customUI.xml"/>». Id можно придумать свой :)
b. Получилась такая штука:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/>
<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/>
<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>
<Relationship Id="R368da2b30bd84589" Type="http://schemas.microsoft.com/office/2006/relationships/ui/extensibility" Target="customUI/customUI.xml"/>
</Relationships>

</Relationships> должен стоять в конце файла!

13. Сохраняем отношения :), в смысле .rels

14. Как вы помните :) в п.9 у нас остался файл empty.zip. Открываем его проводником(или другим архиватором, лишь бы архиватор после сохранения изменений в файле не создавал свой формат архива, иначе Excel потом будет ругаться. Этот вариант не исследовался.)

15. В открытом архиве удаляем всё содержимое и вставляем содержимое из папки empty. Содержание empty.zip, после всех манипуляций:
i.Папка «_rels»
1. .rels
ii.Папка «docProps»
1. app.xml
2. core.xml
iii.Папка «xl»
1.Папка «_rels»
a. workbook.xml.rels
2.Папка «theme»
a. theme1.xml
3.Папка «worksheets»
a. sheet1.xml
b. sheet2.xml
c. sheet3.xml
4. styles.xml
5. vbaProject.bin
6. workbook.xml
iv. Папка «customui»
1. customui.xml
v.[Content_Types].xml

16. Переименовываем empty.zip в empty.xlam и копируем в «C:\Users\%имя пользователя%\AppData\Roaming\Microsoft\AddIns». С заменой естественно.

17. Открываем Excel.

18. Включаем надстройку empty. «Файл»-> «Параметры Excel» -> «Надстройки» -> «Перейти» -> «Доступные надстройки» ставим галку напротив empty -> OK.

19. Между вкладками «Главная» и «Вставка» появится вкладка «MYTABlabel», с двумя группами и по одной кнопке в них.

20. Profit!!!

Таким макаром можно добавлять любые стандартные элементы форм на ленту и привязывать к ним макросы. Кнопкам можно присваивать картинки и придумывать всякие украшательства.

Доклад закончен, вопросы?



Гость
1 - 01.03.2013 - 16:16
Похвально. Даже очень!

Сам реализовал библиотеку макросов XLAM близким к первому способом. Считаю огромным плюсом то, что всем 200-м пользователям домена достаточно просто открыть мой пустой файл из письма - и надстройка ставится влет, обходя всю модель безопасности офиса, настраивает хоткей и делает кучу невидимой работы.

А диалог, вылетающий по Ctrl+Q - динамически перекомпоновывается в зависимости от дектекта что за док открыт. Библиотека обновляется в фоновом режиме - пиши - не хочу. Сейчас она распознает 20 видов документов, листинг кода 60 тыс. строк, позволила сократить из 30 - 5 человек, с распределением их ЗП на остальных. Библиотека самомодифицируется, подтягивая код из сетевого же репозитория.

Теперь критика вашего решения:
1) У 30% юзеров риббон свернут (Ctrl+F1)
2) На риббоне мало места, чтобы реализовать сложное интерактивное взаимодействие.

Но в целом работа сделана не зря. Можно подумать об универсальности и полезности, а может даже и платности решения.
Гость
2 - 02.03.2013 - 15:44
1-economist > Спасибо что прочитали. Критику принял, надеюсь такой вариант модификации ленты не повлияет на уровень безработицы :)
Гость
3 - 04.03.2013 - 11:48
FuzzySense - Уровень безработицы не так страшен, как низкая конкурентоспособность отечественных белых воротничков, вчетверо(!) более низкая производительность офисного труда. Это официально, пруфы могу поискать.

Да и личного опыта достаточно, чтобы подтвердить это. Сравните, средняя скорость печати в образ. учреждениях земли западная Саксония (Германия) - 161 char/min, в одном южном городе России (замерено мною в департаменте образования на 20 кроликах) - 40.

И так почти во всем. Плюс дисциплина, вдвое большее число курильщиков, на 45% больше употребление алкоголя - и выяснится что отечественный работник - форменный раззвиздяй... С т. зр. экономики - дешевле сократить бездельника и платить ему пособие, чем он будет просто занимать место (так нужное выпускнику для получения пресловутого "опыта"), накладывать на работодателя социалку, превращая его в приют для немощных итд.
Гость
4 - 10.03.2013 - 17:48
Как то ковырял все это дело, даже находил что то на подобии утилитки чтобы рибон меню делать. Потом плюнул и все реализую программно, по старинке...
Гость
5 - 14.03.2013 - 07:44
мда бывыет
Гость
6 - 14.03.2013 - 15:38
В продолжение – как добавить кнопку на контекстное меню.
К моему сожалению, получилось только через VBA. Собственно sub:
Sub add2ContextMenuButton()
'Добавляем кнопку в контекстное меню
Dim ContextMenu As CommandBar
Set ContextMenu = Application.CommandBars("Cell") '- Контекстное меню вызываемое по нажатию ПКМ на ячейке.
'Сброс контекстного меню до стандартного, если этот SUB выполняется при каждом открытии книги. Попробуйте закомментить эту строку и раз десять запустить макрос :)
Application.CommandBars("Cell").Reset
'Простейшая конструкция добавления кнопки :)
With ContextMenu.Controls.Add(Type:=msoControlButton, before:=2)
.OnAction = "'" & ThisWorkbook.Name & "'!" & "YourSUBname" '- Здесь YourSUBname - имя вашей процедуры,которая должна выполняться при нажатии кнопки, в данной книге. Не забудьте написать.
.FaceId = 59 '- Картинка веселая рожица
.Caption = "MyButtonName" '- Название кнопки
.Tag = "MyCaption Tag" '- Подсказка к кнопке.
End With
End Sub


К списку вопросов
Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск




Copyright ©, Все права защищены