Как в 1С 7.7 обработать ошибку Excel #Н/Д при загрузке данных через OLE?

← На главную

При автоматизации загрузки данных из внешних таблиц поставщиков (например, прайс-листов или штрихкодов номенклатуры в конфигурации «1С:Торговля и Склад 7.7») разработчики часто сталкиваются с ситуацией, когда ячейки листа содержат формулы с ошибками, такими как #Н/Д (в английской версии #N/A), #ЗНАЧ! или #ССЫЛКА!. Прямое считывание таких ячеек через OLE-объект в среде 1С:Предприятие 7.7 приводит к сбою программы.

Разберем природу возникновения этой ошибки, выясним, почему стандартная конструкция перехвата исключений не помогает, и рассмотрим надежные практические варианты решения задачи.

В чем причина ошибки и почему не работает Попытка — Исключение?

Проанализируем техническую сторону взаимодействия платформы 1С 7.7 с приложением Excel через Automation (COM/OLE). Когда в ячейке находится корректное число или строка, свойство Value возвращает стандартный тип Variant. Однако при наличии ошибки формулы (например, функция поиска ВПР не нашла соответствие) ячейка возвращает внутренний тип VT_ERROR.

Среда исполнения 1С 7.7 не умеет корректно маршалировать этот низкоуровневый тип COM во внутренние типы встроенного языка. В результате обращение к свойству Cell.Value вызывает системное исключение OLE («Несовпадение типов» или аварийный сбой). Конструкция Попытка ... Исключение ... КонецПопытки в 1С 7.7 часто не способна перехватить такое низкоуровневое исключение ядра OLE, что приводит к прерыванию работы обработки или аварийному закрытию программы.

Способ 1. Проверка строкового представления ячейки через свойство Text

Самый простой и быстрый способ обойти сбой — не обращаться к Value до тех пор, пока мы не убедимся, что ячейка не содержит текст ошибки. Для этого воспользуемся свойством Text объекта ячейки, которое возвращает видимое строковое отображение, а не внутреннее значение.

Посмотрим на практический пример кода обхода строк таблицы:


ПровШК = СокрЛП(Excel.Cells(Сч, 6).Text);
Если (ПровШК = "#Н/Д") Или (ПровШК = "#N/A") Тогда
    ТЗНом.Штрихкод = "";
Иначе
    // Если ошибки нет, можно безопасно считывать Value
    ТЗНом.Штрихкод = СокрЛП(Excel.Cells(Сч, 6).Value);
    Состояние("" + Сч + " , " + Артикул);
КонецЕсли;

Обратите внимание на важные особенности свойства Text:

Зависимость от локализации: текст ошибки формируется исходя из языка установленного пакета Microsoft Office. В русскоязычной версии это #Н/Д, в англоязычной — #N/A. Рекомендуется проверять оба варианта.
Ширина столбца: если столбец в файле слишком узкий для отображения текста, свойство Text может вернуть цепочку символов решетки ###.

Способ 2. Комплексная проверка типов ошибок и коллекция Errors

Если в файле могут встретиться другие виды ошибок вычислений (например, деление на ноль или неверные ссылки), расширим проверку. Рассмотрим пример, где анализируются различные варианты некорректных данных с обращением к коллекции Errors объекта ячейки:


фОшибка = 0;
ТекстЯчейки = СокрЛП(cell.Text);

Если (ТекстЯчейки = "#ЗНАЧ!") Или (ТекстЯчейки = "#ССЫЛКА!") Или (ТекстЯчейки = "#Н/Д") Или (ТекстЯчейки = "#N/A") Тогда
    фОшибка = 1;
Иначе
    // Дополнительная проверка через COM-коллекцию Errors объекта Range
    xlEvaluateToError = 1;
    xlInconsistentFormula = 4;
    
    Попытка
        ОшибкиЯчейки = cell.Errors;
        Если ОшибкиЯчейки.Item(xlEvaluateToError).Value = True Тогда
            фОшибка = 1;
        КонецЕсли;
        Если ОшибкиЯчейки.Item(xlInconsistentFormula).Value = True Тогда
            фОшибка = 1;
        КонецЕсли;
    Исключение
        // Если коллекция недоступна в данной версии Excel
    КонецПопытки;
КонецЕсли;

Если фОшибка = 1 Тогда
    ЗначениеЯчейки = "";
Иначе
    ЗначениеЯчейки = СокрЛП(cell.Value);
КонецЕсли;

Способ 3. Проверка через функции Excel WorksheetFunction

Чтобы избежать привязки к языку интерфейса Excel (русский или английский), передадим ячейку как объект диапазона Range во встроенную функцию проверки листа. Метод IsError или IsNA возвращает логическое значение и не падает при наличии ошибки:


ТекущаяЯчейка = Лист.Cells(Сч, 6);

// IsError проверяет наличие любой ошибки формулы
Если ExcelApp.WorksheetFunction.IsError(ТекущаяЯчейка) Тогда
    ТЗНом.Штрихкод = "";
Иначе
    ТЗНом.Штрихкод = СокрЛП(ТекущаяЯчейка.Value);
КонецЕсли;

Способ 4. Массовая предварительная очистка ячеек с ошибками

Поэлементное чтение ячеек в цикле через OLE выполняется медленно, особенно при анализе тысяч строк. Вместо того чтобы проверять каждую строчку, очистим все ячейки с ошибками на листе за один вызов метода SpecialCells перед стартом цикла:


xlCellTypeFormulas = -4123;
xlErrors = 16;

Попытка
    // Находим все ошибочные формулы в используемой области и очищаем их значения
    Лист.UsedRange.SpecialCells(xlCellTypeFormulas, xlErrors).Value = "";
Исключение
    // Исключение возникает, если ячеек с ошибками на листе вообще нет
КонецПопытки;

// Теперь можно спокойно читать ячейки через Value без риска аварийного завершения
Для Сч = 2 По ВсегоСтрок Цикл
    ТЗНом.Штрихкод = СокрЛП(Лист.Cells(Сч, 6).Value);
КонецЦикла;

Также для очистки конкретной колонки можно использовать метод замены диапазона:


xlPart = 2;
Лист.Columns(6).Replace("#Н/Д", "", xlPart);
Лист.Columns(6).Replace("#N/A", "", xlPart);

Выбор конкретного решения зависит от структуры файла: для небольших файлов достаточно проверки свойства .Text, а для объемных таблиц оптимально подходит пакетная очистка через SpecialCells.