Итак, имеем жалобы на работу ПК при открытии файлов Excel, как правило от финансистов и бухгалтеров. В файле Excel всего 100 строк, но нормально работать с ним невозможно. Ячейки выделяются с ощутимой задержкой. При открытии файла процесс Excel съедает в районе 500-800 и более мегабайт оперативной памяти и ряд других симптомов, не позволяющих нормально работать с документом. При этом файл, который должен весить несколько килобайт, весит многие мегабайты.
Это побочный эффект копирования, возникает при вставке данных целыми листами или столбцами/строками из файлов, созданных 1С и подобных (ERP) систем: они генерируют в файл «пустые» ячейки (забитые пробелами), объекты типа “Надпись” и прочий непонятный мусор. В итоге со временем файл становится очень большим.
Стандартный способ посмотреть это (как делать НЕ НАДО, “подвесите” машину еще больше):
В Excel2003: меню Правка → Перейти → Выделить → Объекты.
В Excel2007: вкладка Главная → Найти и выделить → Выделение группы ячеек → Объекты.
¶ Способ решения №1 (умный и познавательный)
Итак, имеем проблемный файл Excel. Заходим в Макросы и создаем следующий (кто не умеет - Гугл в помощь):
Sub Shapes_Count()
MsgBox "Автофигур: " & ActiveSheet.Shapes.Count
End Sub
Выполняем, и помним, что макрос действует не на всю книгу, а на активный лист, так что нужно прогонять на каждом листе. В моём случае оказался оооочень тяжелый документ, один из листов дал такой результат :
И видим тот самый мусор, который жрёт оперативку. Снова идем в макросы и создаем следующий:
Sub DeleteAllTextBox()
Dim oSh As Shape
For Each oSh In ActiveSheet.Shapes
oSh.Delete
Next oSh
End Sub
Запускаем, ждём (в моём случае минут 5 ушло), проверяем первым макросом и результат:
Закрываем файл - сохраняем, открывается без ошибок, нагрузка на ОЗУ падает в десять раз.
Чудеса, не правда ли?!
Перечитали пару, поумнели, попробовали, получили оргазм и благодарности от пользователей и забыли!
¶ Способ второй! Простой и не требует носить с собой макросы.
Если кто не знал (я не знал): Стандартный формат рабочих книг Excel 2007 в действительности это сжатый ZIP-архив каталога XML-документов. Является заменой бывшего бинарного формата .xls, хотя и не поддерживает макросы по соображениям безопасности. Так что, берем файл и открываем его с помощью WinRAR, предварительно сделав копию (вдруг что-то пойдет не так). Ищем папку drawings (лежит в папке xl):
и удаляем её!
Запускаем файл, он ругнется пару раз, со всем соглашаемся и пересохраняем! Всё, все счастливы!
¶ P.S. Странно, что Microsoft знают об этом более 8 лет и ничего не исправляют….
¶ Способ, которому необходимо научить пользователей!
в продолжении темы… все молодцы, почистили документ, но как же дальше жить юзверям?
Как правило, на основе этих документов, создаются новые, к примеру казначей пересохраняет каждый день с новой датой и продолжает его наполнять данными за сегодняшний день, и мусор снова копиться в каждом новом файле ежедневно. Что делать? макросы не сохраняются, да и научить всех этому не возможно!
Учим подчищать за собой! Опять же, мне пригодился первый макрос, чтоб найти лист в документе где мусора не так много:
В этом случае можно воспользоваться стандартным способом. Жмём F5, Выделить, Объекты, ОК:
И ждем… Ничего не нажимаем, ожидаем появление крокозяблей слева/справа/где угодно, скролом проверяем ожил файл или нет:
и просто жмём кнопочку DELETE. Повторяем процедуру с F5 и получаем:
Сохраняем документ и можно дальше работать! Объяснить пользователям, кто ежедневно наполняет документ данными с документов 1С, Элмы и тд., чтоб старались каждый раз за собой подчищать во избежание обрастания документа мусором, этот способ совсем не сложный!