Comments 42
Странно. Кто-то минуснул пост, но объяснять причину в каментах не захотел... Я, например, люблю использовать VBA для снижения объёма рутины при работе с Excel. Думаю, пост поможет начинающим (и не только) скриптописателям!
Спасибо 🙏
Я бы тоже минуснул, но мне нельзя.
Помимо фейспальмово начальной темы, вопросы по которой на Stack Overflow закрывают как дубликаты в течение пары минут, здесь куча всякой другой фигни.
Используется FSO, значит код не запустится на Office for Mac.
Делается вид, что все переменные культурно объявлены. На самом деле все переменные объявлены внутри ReplaceTextInFiles(), где они не используются. А используются они в ProcessFiles(), где они не объявлены. Если поставить Option Explicit наверх, где оно всегда должно быть, всё это перестанет компилироваться, и поделом.
Используется странный способ выявления файлов xlsx, который будет срабатывать на любых файлах, у которых .xlsx встречается где угодно в имени, вместо правильного fs.GetExtensionName.
Используется конструкция If LCase(file.Name Like "*.xlsx*"), которая не имеет смысла. Оператор Like возвращает True или False, LCase неявно преобразует это в строку "True" или "False", возвращая строку "true" или "false", после чего эта строка анализируется через If, для чего она неявно парсится обратно в True или False.
Используется простейшая форма Replace, которая заменит не только номер договора, но и всё остальное, что случайно содержит что-то вроде номера договора где-то в середине - концептуально та же история, что с выявлением файлов xlsx. А выявление даты, к тому же, имеет шанс выдать не только ложное срабатывание, но и ложное несрабатывание, если оная дата где-то отформатирована иначе.
Отсутствует обработка ошибок, а Excel очень сильно любит выдавать бессмысленные ошибки при попытке массовой обработки кучи файлов - даже если перед началом отключить обработку событий и перерисовку экрана, что здесь не делается, так что первый встреченный интересный файл закончит обработку всех файлов.
Мене чем за минуту с Workbooks.Open мне не удавалось обработать несколько сот файлов в реальном сценарии, файлы обрабатывались по 4 секунды, все что можно отключить, отключал. Думал статья посвещена обходу этой проблемы.
Да, вы правы - "менее чем за минуту" скорее для красного словца - это выражение в сравнении с тем как если бы делать описанные замены вручную...
Недавно пришлось скрипт написать, который текст даты форматировал в дату, чтобы в фильтре эти самые даты группироваться могли. Сделал 4 варианта, и каждый из них имел свои плюсы/минусы и время выполнения. Это я к тому, что время выполнения при переборах в vba проблема известная. Нужно искать оптимальное решение в каждом частном случае.
Вот конкретно проблему поиска и замены в сотнях файлов XLSX как раз можно было решить за секунды плюс несколько минут на написание PowerShell скрипта, если воспользоваться тем фактом, что XLSX - это zip-архив с XML-ками. Макросы я бы оставил для каких-нибудь более сложных манипуляций.
Эх, что только не творил на VBA. Было время конечно. Сейчас ушёл на nodejs + exceljs, если нужно что-то более менее объёмное реализовать, потому что удобнее. Для мелочей, да, VBA прям выручает.
Если в начале скрипта отключить автопересчет формул и (не помню точно) перерисовку окна, то дело пойдет намного быстрее. Только не забыть вернуть обратно.
Ну, еще можно все оформить в виде vbs скрипта. Тогда интерфейс экселя не будет отображаться, что еще больше ускорит процесс (вместе с отключением пересчета формул). В таком случае скрипту можно передавать параметром с какой папки начинать копать. Хотя это потребует навыков работы с консолью.
Спасибо за детальное описание, подходит даже для тех, кто "не в теме".
Вопрос, а можно ли избежать открытия каждого файла, просто подключаясь к нему через VBA, или вообще рассматривая книгу как архив?
я не проверял, но первое что гуглится:
Dim app as New Excel.Application
app.Visible = False 'Visible is False by default, so this isn't necessary
Dim book As Excel.Workbook
Set book = app.Workbooks.Add(fileName)
'
' Do what you have to do
'
book.Close SaveChanges:=False
app.Quit
Set app = Nothingто есть попробуйте создать отдельный инстанс application, и уже в него можно будет добавлять workbooks сколько потребуется.
но вообще я бы присмотрелся к другому ЯП например c# + npoi
Для общего развития и сравнения: как это делается в свободном офисном пакете OpenOffice|LibreOffice, "неизбежно приходящем" на смену MSO (но и в MSO это можно, просто не так изящно):
Номер-Дату вычисляем с =СМЕЩ()+1 и =СЕГОДНЯ() по журналу регистрации или берем готовую из системы EDM (документооборота), ежли на такую уже разорились
Вставка любых авто-обновляемых полей из любых источников в OpenOffice|LibreOffice (Word/Writer-форматы и Excel/Calc-форматы) делается единообразно, по нажатию Ctrl+Shift+F4. Серое поле обновляется на выбор: при открытии, по таймауту, по событию, по кнопке, вручную.
Большим плюсом является "Рассылка/Слияние" для нескольких документов (ведь шеф приехал и подписал сразу десяток договоров, не будем же мы 40 раз менять VBA-код, это тупо тяжело). Рассылка также доступна по нажатию Ctrl+Shift+F4, но вдобавок к имеющимся интерактивным фильтрам дает всю мощь SQL-отбора (потому что Ctrl+Shift+F4 ведет в "витрину данных" в OpenOffice|LibreOffice, в которой отображаются данные разных SQL БД, 1С, TXT/CSV/HTML-файлов и вообще чего угодно. Рассылка производит один сводный или много офисных или PDF-файлов, или email-писем с полность.готовым текстом письма, остается нажать кнопку Отправить в почтовике по умолчанию.
SQL-отбор позволяет, скажем, убрать те "мелкие, до 100 тыс. руб." счета/договоры, которые подписывает "Первый зам", еще не вернувшийся с Мальдив. Но на практике SQL удобен для мелких правок налету, когда в сами Excel-таблицы лезть лень или некому.
CTRL+F найти, заменить на...Не благодарите.
Может быть, вам нужно организовать работу с документами так, чтобы реквизиты договора были только в одном (ключевом) файле, а все остальные файлы по этому договору имели ссылку на ячейку(и) с реквизитами договора в этом ключевом файле?
К сожалению не всегда всё так прозрачно
Действительно, в некоторых случаях очень важно максимально усложнить и запутать ситуацию. Например, пусть ежегодно все N договоров компании будут c №1 от 01 января. Это просто чудесно и вот почему: если брать откаты и попасться - суд не сможет доказать N-1 эпизодов, поскольку такова судебная практика. Меньше срок - больше времени потратить нечестно нажитое, профит.
Система работы с документами должна исключать саму необходимость "перебивать" номера и даты. Если взять за пример цифровизацию госуслуг - то это уже работает. Вы, если работали с госорганами, наверняка уже сталкивались с документами (госэкспертизой итп), на которых просто нет даты и иногда нет даже номера, но зато есть ЭП (УКЭП) автора, которая все что нужно (и дату с номером) - прекрасно и универсально заменяет. Точное время (+хэш) вообще способно заменить всё.
Кто-то скажет: но ведь неудобно! Да, но скорее просто непривычно. И как только вы введете документ в свою систему EDM-документооборота - у вас появится № и текущая дата, причем юридически значимые, а не эта бутафория в шапке с "Утверждаю...". Например, если вы получили заключение после январских праздников, то все праздники "прав" у вас не было, какая бы дата на нем не стояла.
Как в этом примере правильно оформить документ после январских праздников, чтобы он был подписан и получен ранее январских праздников?
До подписания второй подписью (последней) - у договора нет даты. Да и номер "проекту договора" ни к чему.
Нет протухших реквизитов - нет нужды их менять. А потом, после подписей, вставить их - надо. Техн аспект я выше написал.
Вы совершенно правы. Дата ставится после подписи. Например, первая сторона подписала 12 января, вторая сторона подписала 6 февраля, а дата начала действия договора — 11 декабря предыдущего года. Всё это время стороны работали совместно по этому договору, однако не имели никакой необходимости подписывать его.
И тут возникает очень непростой вопрос к электронным средствам подписи документов. Как правильно подписать документ задним числом, если этот документ начал действовать задолго до его подписания?
В тексте документа указать "дата начала действия договора на основании ранее подписанных документов...."?
То есть надо будет потом ещё какие-то документы подписать, «ранее подписанные»? А как их подписать?
Повторю. 11 декабря две стороны пришли к согласию и начали работать. 12 января первая сторона подписала договор, потому что раньше это никому не было нужно. 6 февраля вторая сторона подписала договор, потому что наконец-то нашла время на это. Всё это время, два месяца, стороны успешно работали по договору, который им вообще не было нужды подписывать.
ЭП ставится быстро, гораздо быстрее бумажной. Законодатель это подчеркнул особо: никаких ЭП подписей задним числом делать не надо и невозможно. Подписывайте ЭП сразу или при первой возможности.
стороны работали совместно по этому договору, однако не имели никакой необходимости подписывать его
А как вы будете в случае чего судиться с такими договорами?
Если пройти несколько итераций таких костылестроений, то можно изобрести СЭД.
ты еще предложи базу данных завести
Если надо быстро, чтобы уложиться в отведённую автором минуту или около того:
Предполагаю что дата и номер находится примерно в известном месте excel листа.
Ловкость рук и никакого шаманства:)
Пишем скрипт на bash+ Awk, который открывает xlsx как zip файл.
Далее просто парусим xml из которого он состоит.
Преимущество в том, что в отличие от excel + vba можно запустить параллельную обработку. Да и файлы с помощью find искать проще будет.
Придётся правда позаморачиваться с обработкой строк, поскольку excel как правило хранит их а отдельной коллекции а в данных использует ссылку.
Но проблема решаемая :)
Месье знает толк в извращениях :)
Я так Парсинг данных делал чтобы пользователь мог спокойно загружать формы с excel. На серваке excel не запустишь :)
Я оперировал экселевскими файлами через openpyxl
Да, на Python с этой либой это займет 10 строк кода и примерно 10 минут на написание с нуля. Но особых плюсов от VBA нет. Они в EDM, базе данных (реестре) договоров, а это м.б. даже Excel/Calc-файл (таблица).
Красивый договор в Excel не сделать, нужен Word, а лучше Writer из LibreOffice. Там все решается по Ctrl+Shift+F4
Chatgpt и python, т не нужны никакие vba-скрипты
Это можно сделать на любом удобном для тебя языке программирования с модулем доступа к офису. В своё время, я делал всю автоматизацию по отчётам на java. Искал бы, конечно, через регулярку.
Как исправить изменившийся номер договора в нескольких сотнях Эксель файлов менее чем за минуту