Второй запуск обычно ломает книгу не из-за «усталости Excel», а потому что макрос снова выполняет разрушительный шаг: очищает исходный диапазон, удаляет уже перенесённые строки или перезаписывает файл до проверки нового импорта. До изменений сохраните копию книги и воспроизводите только на ней.
Цель — идемпотентный запуск: одинаковый вход дважды оставляет один и тот же набор строк. Для этого чтение, проверка и запись разделяются. Пустой либо частично загруженный staging не получает права очищать рабочую таблицу.
Повтор показывает, где спрятано неверное предположение
| После первого запуска | После второго | Что искать в коде |
|---|---|---|
| Исходник пуст, отчёт заполнен | Отчёт стал пустым | ClearContents до проверки источника |
| Строки перенесены | Часть строк пропала | Delete по изменившимся номерам строк |
| Один набор данных | Появились дубли | Append без business key |
| Формулы сохранены | Формулы исчезли | Очистка всего UsedRange |
| Файл создан | Файл перезаписан пустым | SaveAs при DisplayAlerts=False |
Снимите четыре значения в тестовой копии: число исходных строк, число staging-строк, число целевых строк и количество уникальных ключей. Если staging уже пуст, целевую таблицу не трогают. Если ключи не уникальны, импорт останавливают до выбора правила объединения.
Копия создаётся до первого изменения
Sub RefreshReportSafely()
Dim backupPath As String
backupPath = ThisWorkbook.Path & "\backup_before_refresh_" & Format(Now, "yyyymmdd_hhnnss") & ".xlsm"
ThisWorkbook.SaveCopyAs backupPath
LoadSourceIntoStaging
If StagingRowCount = 0 Then Err.Raise 1001, , "Staging is empty"
If StagingKeyCount <> StagingRowCount Then Err.Raise 1002, , "Duplicate keys"
MergeStagingIntoReport
ValidateReportTotals
End SubWorkbook.SaveCopyAs сохраняет отдельную копию и не меняет открытую книгу в памяти. Имя резервного файла должно быть уникальным для запуска или подтверждённо несуществующим: тихая перезапись одного backup лишает возможности вернуться к предыдущему состоянию.
ClearContents получает узкую границу
Range.ClearContents удаляет значения и формулы, но оставляет форматирование и условное форматирование. Это удобно для выделенного output-body, но опасно для всего UsedRange: там могут находиться формулы, ручные примечания и служебные ключи. Диапазон строят от объекта таблицы или именованной области, а не от активного листа.
- Каждый Range и Cells квалифицируется конкретным Worksheet.
- Заголовок таблицы не входит в очищаемое DataBodyRange.
- Формульные столбцы восстанавливаются из шаблона, а не из случайной первой строки.
- Delete выполняется снизу вверх либо по устойчивым ключам, но не по старым номерам строк.
- Пустой источник считается ошибкой, если пустой отчёт не является допустимым результатом.
Append заменяется слиянием по ключу
Для строки нужен business key: номер заказа, составной ключ документа или другой устойчивый идентификатор. При повторе существующая строка обновляется, новая добавляется, исчезнувшая помечается по заранее выбранной политике. Номер строки и текущая сортировка ключом не являются.
Если макрос переносит данные между листами, отметка processed записывается только после успешной записи и проверки цели. Удаление источника до этого момента превращает любую ошибку формулы или нехватку места в потерю данных.
Отключённые предупреждения требуют cleanup
Application.DisplayAlerts=False заставляет Excel выбирать ответы по умолчанию; при SaveAs это может привести к перезаписи существующего файла. Если отключение действительно нужно, прежнее значение сохраняют и восстанавливают в единой секции выхода даже после ошибки. Диалог не заменяют молчаливым согласием на разрушение.
Тест проходит три запуска, а не один
- Запустить макрос на тестовой книге и сохранить контрольные суммы.
- Запустить повторно без изменения источника и получить те же суммы.
- Добавить одну строку и увидеть ровно одно изменение.
- Подать пустой или повреждённый источник и оставить отчёт неизменным.
- Прервать выполнение после staging и восстановиться из копии.
- Отсортировать лист иначе и подтвердить, что удаление не зависит от номера строки.
Результат можно объяснить по журналу
В отдельный лист журнала запишите run_id, время, имя входного файла без полного пути, counts inserted/updated/skipped и текст ошибки. Не копируйте содержимое рабочих строк в журнал. Для разбора подготовьте тестовую книгу, процедуру запуска и список разрушительных методов — оригинал с персональными данными не нужен.