| Спасибо за ответы!
V3
1. Решил пойти всё же первым варинатом – открыть и оттуда скопировать.
2. 
Хотел только ещё указать каталог в котором открывать через ChDir, но неполучилось
3. как вариант добавить макрос на открытие книги чтобы искал и точку заменял на запятую (ИМХО может есть проще)
Источник: forum.ixbt.com
Несколько советов по работе с VBA в Excel
Добрый день!
Некоторое время назад меня попросили «помочь с Экселем», а потом и работа подвернулась такая, так что за последние пару месяцев я узнал много полезного, чем и хочу поделиться в догонку к недавней статье.
Предполагается, что вы знаете основы Visual Basic. Я не буду рассказывать, как создавать формы или модули, здесь только примеры кода.
Visual Basic
Опции
Во-первых, в VB массивы могут начинаться с индекса 1, что для многих странно, поэтому в начале модулей можно прописывать:
Так же рекомендуется прописать:
В этом случае интерпретатор потребует заблаговременного объявления всех переменных. Переменные объявлять нужно потому, что:
— VB запомнит их нАпиСание и не будет исправлять во всём коде на последний введенный вариант;
— иногда возникают ошибки с передачей переменных byRef, если они не объявлены (то есть надо или объявить переменную, или приписать в функции/процедуре перед ней byVal).
Ещё одним важным оператором является ON ERROR. Привожу варианты:
Возможности языка
Хотя VB довольно прост, полезно почитать документацию по его синтаксису. Я, например, с удивлением узнал, что можно прописывать сложные усолвия в SELECT’ах (аналог switch):
Ускорение работы макросов
Часто макросы требуют долгого времени выполнения, которое можно значительно сократить. В начале и в конце каждой ресурсоёмкой функции вызвать Prepare и Ended.
По порядку:
1. Отключить перерисовку объектов на экране, чтобы ничего не мигало.
2. Выключить расчет. Внимание, если макрос прерваляс посреди работы, то расчет так и останется в ручном режиме!
3. Не обрабатывать события.
4. Отображение границ страниц, тоже почему-то помогает.
5. В статусной строке выводятся различные данные, что замедляет работу, отключаем.
6. Это если нужно. Выключает сообщения Экселя. Например, мы делаем Workbook.Close, Эксель хочет спросить сохранить ли изменения. При выключении этого параметра все ответы будут даны автоматически (изменения не сохранятся).
Важно понимать, что VBA выполняет все действия так же, как и пользователь. Поэтому для того, чтобы установить параметры страницы, он каждый раз открывает и закрывает окно параметров. У меня выставлялись параметры для 10 листов, это реально не быстро. Поэтому делаем так:
Далее, часто нужно просмотреть различные диапазоны ячеек и что-то с ними сделать. Тут важно не использовать циклы for с перебором индексов, они медленные. Можно использовать встроенные функции Экселя, но удобнее всего такой вариант:
Данный код просматривает указанный диапазон, выбирает в нем «специальные ячейки», в данном случае все, в которых есть формулы (т.е. начинаются со знака равно). Для каждой ячейки смотрится, если она не закрашена, то её надо защитить (см. далее) и покрасить. Такой код работает очень быстро.
Для любых переменных, которым вы собираетесь присвоить книгу, лист, диапазон (ячейку) нужно предварительно объявить как Variant.
Естественно, что если вам нужны однотипные значения в ячейках, нужно использовать автозаполнение, всё равно как «растягивание» ячеек пользователем.
Второй диапазон должен включать первый, а второй необязательный параметр указывает тип автозаполнения.
Загрузка книги и события
При открытии книги каждый раз срабатывает процедура.
В данном случае настройки печати (поля, ориентация) сбрасываются на дефолтные. Можно и другую инициализацию выполнять. Важно, что если макросы отключены, то и не выполнится ничего. Если в Экселе вылезла вверху панелька с предупреждением о макросах и пользователь нажал «Включить», то именно в этот момент выполнится процедура Workbook_open().
Список доступных событий можно посмотреть вверху редактора VB. Например, я делал на событие Change проверку, где лежит ячейка, в которой было изменения, и если это нужный диапазон, то делалась запись в лог со старым и новым значением.
Защита
Во-первых сразу отмечу, что MS Office не исполняет макросы на компьютерах, где он не нашел антивируса, если книга зашифрована. Сталкивался на компьютерах, где антивирус был, но видимо Windows XP об этом не знала.
Ещё антивирус может странным образом мешать работе, вызывать ошибки, не совсем объяснимые. Показал айтишникам, сказали ок, что-то сделали, не знаю.
Итак, нам надо защитить книгу, чтобы ввод был разрешен только в нужные ячейки (формулы и заголовки поменять нельзя). Во-первых, нужно сделать соответствующие ячейки «не защищенными». Для этого делаем одно из:
— выделяем диапазон, формат ячеек, снять галочку «Блокировать ячейку»;
— выводим кнопку «Блокировать ячейку» в быстрый доступ и нажимаем её, очень удобно смотреть на неё чтобы понять, защищена ячейка или же нет;
— а это пригодится, чтобы проверить третий вариант — написать макрос, который снимает защиту с нужных ячеек сам.
Далее нужно защитить лист. На вкладке Рецензирование есть такая кнопка. Окошко просит ввести пароль и установить исключения (что можно будет делать пользователю). К сожалению, список исключений маловат. Самое обидное, что нельзя разрешить сворачивать/разворачивать группы столбцов/строк. Поэтому действуем так, на загрузку книги прописываем:
Знак подчеркивания продолжает логическую строку на следующей физической строке. Итак, здесь мы:
1. Сняли защиту.
2. Включили группировку.
3. Поставили защиту, при этом:
— защита только от юзера, макросы продолжают иметь полный доступ (!), крайне важно;
— разрешили сортировку, фильтрацию и форматирование строк/столбцов (высота/ширина);
— DrawingObject в данном случае снимает защиту с примечаний к ячейкам, может и ещё с чего.
Тут мы сталкиваемся с парой сюрпризов. Во-первых, не все макросы будут работать даже так. Известный баг, ничего не сделаешь. Нельзя вставить строку, например. Приходится снимать и тут же ставить защиту. Если «злоумышленник» в этот момент нажмет ctrl+break, то защита слетит.
Во-вторых, скажем никаким способом нельзя удалять строки (AllowDeletingRows), в которых есть защищенные ячейки, хоть одна. Подробнее вот тут.
Решением (костылем) является добавление кнопки или сочетания клавиш для удаления. Заодно можно проверить, чтобы пользователь не удалил чего не надо. В Workbook_open добавляем:
Теперь процедура будет вызываться при нажатии shift+delete.
Знаю, код некрасивый, простите. Здесь я пытался проверить, что выделена строка, то есть строк там 1, а ячеек не меньше тысячи. Чтобы удалить не то, придется выделить тысячу ячеек начиная не с первого столбца. Далее проверяется имя листа и номера строк. Вместо 50 был расчет последенй строки (ведь их число меняется, если мы их удаляем и добавляем).
Заключение
VBA — весьма глючная вещь, которая позволяет сворачивать горы в MS Office. Многие предприятяи используют модели на Excel годами, и если они сделаны хорошо, то всё работает.
Для изучения VBA подходит он сам, во-первых там хорошая справка. Например, чтобы узнать все варианты что можно разрешить в методе Protect, нажимаем F1, Protect, ввод. И вуаля.
Во-вторых, можно проделать требуемые действия вручную, записав макрос, а потом просмотрев его код. Код будет ужасен (например, при изменении параметров страницы, макрос запишет значения всех параметров и полей, а не только измененного вами), но ответы найдутся. Хотя, например, .AutoFit, который записывается при изменении высоты ячейки по содержимому (двойной клик на границе слева), на самом деле не работает.
Предлагаю знатокам поделиться своим опытом, дать советы в комментариях. Спасибо за внимание, удачных разработок вам.
Источник: habr.com
vba – Обновите внешние данные в Excel 2010, пока файл открыт в фоновом режиме, но не используется
Я использую Excel 2010 для запроса баз данных MS SQL и других файлов Excel для импорта данных. Я установил разные интервалы для каждого соединения данных (10 соединений). В excel эти данные разрезаны и нарезаны кубиками для создания значимых диаграмм, которые представлены в PowerPoint.
Я смог найти рекламный блок VB для PowerPoint для автоматического обновления данных из excel во время цикла представления.
Я сохраняю файл excel открытым в фоновом режиме (Behind PowerPoint), так как PowerPoint пытается открыть лист excel каждый раз, когда обновляется диаграмма. Моя проблема в Excel, так как она не активно используется, соединения данных не обновляются автоматически.
Я пробовал различные решения, но они требуют, чтобы vb/macro запускался вручную, однако мне нужно это, чтобы обновлять данные автоматически на основе таймингов, предоставленных для соединений данных.
Есть ли код VB, который я могу использовать в Excel для обновления соединений данных, в то время как рабочая книга excel открывается, но не активно используется?
Цените, если вы можете указать мне на соответствующую должность или вести меня.
12 3
- 26 апр 2019 2019-04-26 01:25:27
- hos______
3 ответа
Похоже, что у вас есть это уже включено, но когда вы перейдете к Data. Connections и выберите одно из ваших соединений/запросов, нажмите “Свойства. ” на вкладке “Использование”, вы выбрали “Включить обновление фона” и “Обновить” каждые X минут “?
Excel должен продолжать обновлять свои запросы, даже если программа не имеет фокуса. Когда я пишу этот ответ, я могу видеть Excel в фоновом режиме, обновляя тестовый запрос один раз в минуту.
Вы пробовали другие сценарии. если вы не активно используете лист Excel, но компьютер простаивает, обновляется ли он? Если вы просматриваете Интернет с помощью IE? С Chrome? т.е. пытается определить, изолирована ли проблема от PowerPoint.
Это не должно требовать, чтобы VBA работал.
- 26 апр 2019 2019-04-26 01:25:29
- ExactaBox
tdlr, используйте http://metacpan.org/pod/Win32::Excel::Refresh Он поставляется с Windows EXE, если вы не хотите использовать perl. Это позволяет запускать Excel update/refresh/macro из командной строки, и вы можете использовать Task Scheduler для его запуска.
- 26 апр 2019 2019-04-26 01:25:28
- Alex
Я нашел решение для обновления кеша Pivot, если данные на листе меняются. Я обновляю этот пост, если кому-то еще нужно решение. Большое спасибо за Вашу помощь.
Источник: codeindex.ru
Tech&Biz Insights
VBA и Python для автоматизации Excel и MS Office
Поводом для заметки послужила статья на Хабре, в которой автор описывал, как он решал на Python задачу сбора и анализа метаданных из файлов Excel.
Эта заметка более подробно раскрывает всем известный тезис: Под конкретную задачу надо выбирать наиболее подходящий инструмент применимо к офисной автоматизации.
VBA и Python
VBA (Visual Basic for Applications), де-факто, самый популярный язык для автоматизации Microsoft Office. Доступен из коробки, помимо Excel, работает в PowerPoint, Outlook, Access, Project и других приложениях.
Если задать вопрос: «Какой язык программирования выбрать первым», то где-то в 90% всех случаев будет предложен Python. На практике здесь может быть и любой другой язык, но, исходя из популярности языка и своего опыта, буду сравнивать с ним.
В общем виде можно описать ситуацию через подобный график:
Детального сравнения не будет, рассмотрим основные killer-фичи, в ситуации, когда junior-программист/офисный сотрудник хочет автоматизировать что-либо, связанное с MS Office, и у него есть возможность выбора между языками.
Если в силу разных причин возможности выбора нет, то и сравнивать нечего.
В пользу VBA
- Отличная работа с объектной моделью Excel и других приложений MS Office. Написание кода на VBA для большинства внутренних операций тривиально. У Python, в сравнении с VBA, поддержка объектной модели Office очень слабая.
- Поддержка разных форматов MS Office. Самая большая проблема для внешних языков – это работа с разными форматами файлов MS Office. Например, xls, xlsx, xlsm файлы могут требовать разных библиотек, так как каждая хорошо работает только со своим форматом файла. Для VBA – это все “файл Excel”, работа с которыми в целом одинаково хороша.
- Работа с MS Exchange. Если необходимо обеспечить работу с корпоративной почтой/календарем на Exchange, то далеко не каждом языке есть нормальная библиотека для работы протоколом Exchange. В VBA это решается относительно просто через использование в макросе объектной модели MS Outlook.
- Легкая установка и дистрибуция. К уже установленному офису не надо ничего устанавливать. Чтобы коллега мог воспользоваться программой, достаточно передать ему файл с макросом. Легко сделать надстройку, которая позволит “установить” модель макроса в фон офиса.
- Интерактивность внутри приложений MS Office. Внутри офисных программ можно как просто поставить кнопки запуска макросов, так и (чуть сложнее) сделать целый отдельный UI. Сюда же относится написание своих формул в Excel и то, что макросы могут воздействовать на объекты внутри документов Office в реальном времени.
- Запись макросов. Удобный инструмент, который позволяет записать действия человека в готовый код, для последующего редактирования использования.
В пользу Python (и других внешних языков программирования)
- Приятный синтаксис и синтаксический сахар. Если коротко, то VBA не отличается выразительностью и удобством. Это вопрос личного вкуса, но для меня Python намного удобнее.
- Богатая экосистема библиотек. Огромный выбор готовых библиотек для работы с внешним миром. Пытаться сделать на VBA программу, взаимодействующую с каким-нибудь внешним API, та еще боль. Занимательно, что как раз для работы с файлами Office библиотеки того же Python – откровенно “на троечку”.
- Хорошие средства разработки. Можно выбрать из огромного выбор программ, которые облегчают процесс разработки. Стандартный редактор VBA из Office предлагает очень бедный функционал и, в сравнении с альтернативами из мира Python, откровенно неудобен. Писать код VBA в внешнем редакторе, а потом копировать внутрь офиса для отладки – тоже неудобно.
- Скорость работы. Не проверял скорость однопоточной работы, но, предположу, что в случае однопоточной работы преимущество будет за Python. В любом случае, достаточно тривиально организуется многопоточная обработка данных/файлов, что позволяет говорить в большей достижимой скорости.
Кейсы
Далее приведены конкретные задачи, которые я сам решал или автоматизировал, и когда мне надо было выбрать стек: VBA или Python. Для каждой задачи указан выбранный стек и даны пояснения почему:
- Задача: Программа для проверки всех файлов Excel в директории на предмет наличия скрытых листов
- Мой выбор: VBA. Причины: простота работы с разными форматами файлов Excel и отсутствие внешних взаимодействий.
- Задача: Сервис, который должен был позволить пользователю с мобильного устройства конвертировать файлы PowerPoint в PDF для просмотра
- Сервис был реализован как почтовый бот, на адрес которого пользователь может переслать файлы Office, а в ответ пользователю по почте приходит ответ с файлами PDF.
- Мой выбор: Логика VBA + Python для мониторинга
- Во-первых, внутренние функции гарантированно сохраняли PDF, аутентичный файлу PowerPoint (внешние библиотеки плохо справляются с рендером PowerPoint).
- Во-вторых, реализация почтового бота, как макроса в MS Outlook решала проблемы работы с корпоративной Exchange почтой. Так, в Python нет нормальной библиотеки для работы с MS Exchange.
- Python использовался для организации мониторинга работы сервиса и нотификации о возможных проблемах
- Задача: Программа для объединения файлов Powerpoint с “подстрочником” (текстом для докладчика) в файл для печати
- Мой выбор: VBA. Задача решалась через конвертацию двух файлов в PDF и их объединением с Riffle Shuffle. Так как важно качество конвертации в PDF, то использовались встроенные функции офиса для экспорта в PDF.
- Задача: Дана база данных по предприятиям отрасли в виде карточек-отдельных файлов html, которые надо отфильтровать и объединить в 1 файл Excel для расчета ряда показателей.
- Мой выбор: Python.
- Чтобы вытаскивать из html карточек данные пригодилась библиотека для парсинга html BeautifulSoup.
- Excel-файл создает программа, поэтому мы сами можем решать, какую аналитику рассчитывать уже в нем, а какую еще на стадии подготовки данных в Python.
- Задача: Перевести весь текст в презентации PowerPoint на другой язык машинным переводчиком
- Мой выбор: VBA. Важно было аккуратно работать с текстом во внутренних объектах файла PowerPoint. Для перевода использовался API от Яндекса, так как он бесплатен для небольших объемов и прост в подклюении. Например, API переводчика Bing я так и не смог заставить работать в VBA, так как там для работы нужен OAuth со своими заморочками. Если бы пришлось работать с Bing, то, наверное, я бы делал сервис-посредник на Python.
- Задача: По заданным биржевым тикерам брать данные из API с финансовыми показателями (API выдает сформированный по запросу CSV файл) и считать на их основе ряд бенчмарков для анализа
- Мой выбор: Python. Хотя API – простой (не требует какой-либо подписи запросов или авторизации) и выдает данные в CSV, выбран Python, так как нет причин выбирать VBA, а на Python писать удобнее.
- Задача: Ведение базы поручений, рассылка уведомлений исполнителям, генерация отчета для печати
- Здесь я выбирал очень долго, так как есть много альтернатив:
- Сторонняя готовая система поручений
- База данных с каким-то обработчиком
- Access
- Excel
- Мой выбор: VBA
- Во-первых, Excel сам по себе является готовым UI для работы
- Во-вторых, VBA решает задачу работы с корпоративным Exchange через подключение MS Outlook
- В-третьих, это решение делалось с прицелом на коллег, которым Excel был более понятен, чем что-либо совсем новое
Заключение
Надеюсь, для кого-то заметка будет полезна и позволит сэкономить время на выборе стека для решения своих задач.
Источник: pyatakov.com
Adblock
detector
|
Загрузка .
)
ругается на FileDialog – user-defined type not defined. Надо какие то библиотеки подключать? Или прототипы библиотечных функций писать?
