Как сохранить ссылки на ячейки при изменении столбцов с запросами

Question

В Excel у меня есть таблица запросов, связанная с таблицей SQL. Формулы внутри моей электронной таблицы содержат ссылки на ячейки внутри таблицы с использованием абсолютных ссылок, таких как Z9 .

Я использую VBA, чтобы обновить запрос SQL для таблицы. Проблема начинается, когда я меняю столбец, который извлекаю из SQL. Ссылки на ячейки прерываются, хотя интересующие меня данные находятся в той же ячейке.

Очевидно, я столкнулся с той же проблемой, если использовал методологию =Table1[Column1] потому что имя столбца изменилось. Есть ли способ изменить столбец в таблице запросов, не нарушая ссылки на формулы листа для этого столбца?

Answer 1

Я столкнулся с аналогичной проблемой при загрузке данных с веб-сайта, используя QueryTables.Добавить метод. Ссылки на лист, который содержал данные запроса, менялись всякий раз, когда я повторял запрос. Я изменил подход к копированию и вставке данных из листа запроса в другой лист. Сначала это тоже не работало, так как значения from меняли столбцы с последующими запросами. Чтобы решить эту проблему, я сделал следующее:

1. Очистил рабочий лист запроса перед его выполнением.
2. Сокращенный запрос для получения только важных данных, примерно в 20 раз.

3. Использовал следующий код для копирования / вставки:

   Sheets("Data").Cells(FromRow, FromCol).Copy
   Destination:=Sheets("Port").Cells(ToRow, ToCol)
   

Это похоже на работу. Последующие запросы, которые заполняли лист "Данные", были правильно вставлены в лист "Данные". Я подозреваю, что при большом запросе возникла проблема с синхронизацией, когда ссылки на другом листе выполнялись до завершения запроса.

Answer 2

Я не смог найти способ предотвратить разрывы #REF когда мой код VBA изменил SQL-запрос. Я был в состоянии обойти ошибку, хотя! Вместо того чтобы изменять запрос на основе пользовательских данных, я вытащил весь набор данных за один раз, а затем использовал поиск совпадений индексов в этом универсальном наборе, чтобы получить нужный столбец.

Типичное решение Excel: функциональное, но не элегантное. Выберу свой ответ, если у вас есть что-то лучше!