Сначала сохраните копию книги и возьмите десять строк, среди которых есть явный повтор. Запустите импорт дважды. Если второй запуск добавил данные, макрос не идемпотентен: он либо не проверяет существование ключа, либо проверяет поле, которое меняется или сравнивается непоследовательно.
Удаление дублей после импорта — последняя страховка, а не основная логика. Оно может оставить устаревшую строку и удалить обновлённую. Надёжнее решить до вставки, является ли запись новой, обновлением или конфликтом.
Ключ не зависит от позиции строки
| Кандидат | Подходит | Почему |
|---|---|---|
| Номер строки | нет | меняется после сортировки и вставки |
| Название | редко | регистр, пробелы и переименования |
| Source ID | да | стабилен в исходной системе |
| Составной ключ | иногда | нужно явное правило и разделитель |
| Случайный GUID при импорте | нет для дедупликации | каждый запуск создаст новый |
Если source ID отсутствует, согласуйте естественный составной ключ, например код поставщика плюс номер документа. Не склеивайте части без разделителя: пары `12|34` и `1|234` иначе могут стать одинаковыми.
Одна функция нормализует обе стороны
Private Function KeyOf(ByVal sourceId As Variant) As String
KeyOf = LCase$(Trim$(CStr(sourceId)))
End Function
Dim seen As Object
Set seen = CreateObject("Scripting.Dictionary")
seen.CompareMode = vbTextCompare
For r = 2 To lastDstRow
k = KeyOf(wsDst.Cells(r, 1).Value2)
If Len(k) > 0 Then seen(k) = True
Next rПример предполагает, что source ID лежит в первом столбце. Для чисел, дат и ведущих нулей задайте контракт явно: `0012` и `12` могут быть разными бизнес-ключами. Не применяйте агрессивную нормализацию, которая стирает значимые символы.
Словарь обновляется сразу после вставки
For r = 2 To lastSrcRow
k = KeyOf(wsSrc.Cells(r, 1).Value2)
If Len(k) = 0 Then
' Записать ошибку: пустой ключ
ElseIf Not seen.Exists(k) Then
Set newRow = tbl.ListRows.Add
newRow.Range.Cells(1, 1).Value2 = wsSrc.Cells(r, 1).Value2
newRow.Range.Cells(1, 2).Value2 = wsSrc.Cells(r, 2).Value2
seen.Add k, True
End If
Next rДобавление ключа в `seen` сразу после строки защищает и от повторов внутри самого исходного файла. Для режима update вместо молчаливого пропуска храните в Dictionary номер строки назначения и сравнивайте разрешённые поля. Конфликт лучше записать на отдельный лист, чем случайно перетереть данные.
Range.Find требует всех параметров
Если существующий макрос использует `Range.Find`, указывайте `LookIn`, `LookAt`, `SearchOrder`, `SearchDirection` и `MatchCase` явно. Excel сохраняет часть настроек поиска между вызовами и диалогом Find, поэтому код может работать у автора и давать другое совпадение у коллеги. Для массовой проверки точных ключей Dictionary обычно понятнее и быстрее.
- Не использовать `LookAt:=xlPart` для точного ID.
- Не считать первое совпадение доказательством уникальности всего источника.
- Не смешивать отображаемый Text и исходный Value2 без причины.
- Проверять пустые и ошибочные значения до преобразования в строку.
- Собирать дубли источника в отдельный отчёт.
Частичный сбой должен допускать повтор
Excel не даёт полноценной транзакции для произвольных изменений книги. Практичный подход — сначала проверить и подготовить staging-массив, затем записать данные одним контролируемым блоком. Для длинного импорта храните `run_id`, имя/хеш источника, время, число inserted/skipped/errors и ключ последней подтверждённой записи. Перед запуском всё равно нужна резервная копия.
| Состояние после сбоя | Повтор | Защита |
|---|---|---|
| ничего не записано | полный | staging до commit-блока |
| часть строк добавлена | полный безопасный | Dictionary из destination |
| строка добавлена без ID | ручная проверка | ключ обязателен до вставки |
| событие запустило макрос снова | не допускать | EnableEvents и флаг запуска |
Настройки Excel восстанавливаются даже при ошибке
On Error GoTo CleanFail
Application.ScreenUpdating = False
Application.EnableEvents = False
' validate, stage, import
CleanExit:
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub
CleanFail:
' записать Err.Number и безопасное описание
Resume CleanExitЕсли макрос меняет Calculation или DisplayAlerts, сохраните прежние значения и восстановите их в том же cleanup. Иначе после ошибки пользователь останется с отключёнными событиями или ручным пересчётом и получит новые, уже неочевидные проблемы.
Проверка начинается с повторного запуска
- Создать копию destination и маленький источник с внутренним дублем.
- Запустить импорт и записать inserted/skipped/errors.
- Запустить тот же файл второй раз: inserted должно стать нулём.
- Поменять регистр и пробелы ключа согласно контракту и повторить.
- Вызвать ошибку посередине и проверить безопасный restart.
- Отсортировать таблицу назначения и убедиться, что ключ не зависит от строки.
Исправленный импорт можно запускать повторно без роста строк, а каждый пропуск или конфликт объясняется журналом. Для разбора достаточно обезличенного файла из десяти строк, кода макроса, определения ключа и отчёта запуска; рабочую книгу с персональными или финансовыми данными передавать не нужно.