Comments 10
Упорство и труд все перетрут! Но, справедливости ради, на VBA задача решается примерно в 80 строк кода (меньше, чем у вас ушло только на GUI):
1) перебор worksheets в файле с поиском SpecialCells(xlCellTypeFormulas) и проверкой на внешнюю ссылку, добавление результатов в коллекцию ~30 строк
2) рекурсивный обход папок - 20 строк
3) Вывод результата в новый XL файл, с hyperlinks, фильтрами, и power query - максимум 20 строк.
4) Добавление кнопки в Ribbons - тут уж как получится.
Из минусов - вряд ли получится сделать параллельно, и, возможны просадки по перформансу, если файлы большие (больше 10 мегабайт).
Из плюсов - раз уж ищем ссылки в XL, лучшего UI, чем предоставляет XL, придумать сложно.
Я не утверждаю, что ваше решение чем-то плохо. Просто исходя из задачи, есть решение проще и элегантнее.
Кстати о элегантности. Помимо ячеек, внешние ссылки XL могут также находится внутри имен, поименованных формул, объектов, а также внутри чартов. У вас эти кейсы, насколько я вижу по по листингам, не учтены.
При чем тут упорство и тем более труд?
Весь код сгенерен с помощью ИИ.
Самый писк - диалог построенный в скрипте на PowerShell с жесткими неадаптивными размерами.
VBA не был использован скорее всего по тому, что там надо гораздо больше двигать мышкой для создаия и вызова таких модулей.
А Python не был выбран потому, что отладка автора не волновала, не он же кодирует.
Словом предпочтения и рейтинг языков в скором времени причудливо будут меняться.
Спасибо что так подробно ответил. Действительно на VBA можно это сделать оптимальнее, но я не понял каким образом т.к. постоянно открывать все файлы и проверять ссылки внутри мне показалось слишком долго, а как ковыряться в XML в архиве из VBA я не знаю пока еще :-)
Powershell выбрал т.к. он есть на каждом компьютере, разве только с политиками внутри офисов может быть затык из-за запретов от сисдаминов на скрипты... И в powershell работает многопоточно тоже.
Ну кстати да, не учел имена, объекты и т.д., надо будет обратить внимание...
постоянно открывать все файлы и проверять ссылки внутри
Так в вашем листинге первые 10 строк в блоке try именно это и делают - открывают файл XL. Уверяю вас, XL откроет свой файл в несколько раз быстрее. Одной командой. Поиск ссылок внутри файла - это элементарная задача, которую НЕ имеет никакого практического смысла решать на уровне XML.
Я буквально на днях 7500 файлов прошёл за час своим скриптом из этой статьи... думаю сам excel будет это делать больше 10ти часов... Да и вопрос еще, есть ли рекурсивный способ обхода папок, вроде нет. И многопточность, но это не точно.
Если вы пытаетесь меня убедить, что VBA где-то упрется в проблемы с производительностью, в сравнении с PowerShell, то, уверяю вас, для вашей задачи - абсолютно точно нет.
Давайте оценим (зависит от размера файла, конечно). Если все ваши 7500 файлов по мегабайту, то, условно возьмем время открытия ~1 секунду. Получится примерно два часа на все. Временем на выполнение кода можно пренебречь. Но у вас же не по мегабайту файлы? C "обычными" офисные файлами, думаю минут в 15-30 можно уложиться.
Приведу пример: у меня есть проект, где файл на 28 мегабайт с рекурсивным перемножением матриц 100 тысяч на тысячу обрабатывается 4 секунды на процессоре пятилетней давности. Это с выводом результатов, валидацией данных, и прочими делами. Да, для этого пришлось немного пооптимизировать алгоритмы, но ИМО это гораздо более интересное времяпрепровождение. Другой пример - делал проект для экспорта папки из примерно 1000 XL файлов в JSON. Около 30 секунд.
И, кстати, что значит "есть ли рекурсивный обход папок"? Есть полноценный доступ к файловой системе. Only the sky is the limit.
Ну, мне кажется, даже если и ИИ писал, то заставить его такое написать, тем более, если оно работает, тоже много стоит. Правда, я так и не понял, по каковвки там тарабарщина. Power Shell, это же новомодная виндовая консолька, не? Причём тут язык программирования?..
Как я приручал Excel: автоматизация поиска внешних ссылок с помощью PowerShell