Microsoft Excel 2007 – Как написать динамический Table_Array для Vlookup

Я не уверен, что мне нужно опубликовать этот вопрос в определенном месте, но здесь.

ПРЕДУПРЕЖДЕНИЕ: Стена текста. Мне нужно полностью объяснить мою ситуацию, поскольку она представляет собой уникальную проблему с небольшим количеством решений.

TL; DR – электронная таблица Master захватывает данные из «еженедельной» таблицы через vlookups. Каждую неделю выпускается новая электронная таблица. Как я могу автоматизировать vlookup для захвата данных из новой электронной таблицы каждую неделю?

Теперь вот длинная версия …

У меня есть главная таблица, в которой используются vlookups, указывающие на другую таблицу. Пример кода ниже:

=VLOOKUP(A2,'...OTC\2016\Reports Sent\[SALES BY SKU STORE wk 3 (retail) (2).xls]SKU'!$D$1:$G$65536,4,FALSE)

Отлично. Очень просто.

В настоящее время главная электронная таблица указывает на данные для «Недели 3». Следовательно, [ПРОДАЖИ С МАГАЗИНЫМ SKU wk3 (розничная торговля) (2) .xls]. В настоящее время в каталоге файлов, где находится таблица, таблица 3-го дня является самой современной. Каждую неделю новая электронная таблица будет сохранена в той же папке, но называется [SALES BY SKU STORE wk4 (розничная торговля) (2). Xls]. Получать идею сейчас ..?

Я хочу, чтобы главная электронная таблица могла указывать на новые еженедельные данные, каждый раз, когда новая электронная таблица существует. Таким образом, можно было бы подумать, как-то сделать vlookup Table_Array динамическим. Как это сделать?

Дополнительное примечание: – Я написал сценарий PowerShell для вывода всех имен файлов и расширений файлов в CSV-файл. Затем я создал макрос для импорта данных .csv в отдельный лист в главном файле. Я сделал это как потенциальную таблицу поиска. Пример данных ниже, мы назовем его «Список имен файлов»:

[SALES BY SKU STORE wk1 (retail)(2).xls]

[SALES BY SKU STORE wk2 (retail)(2).xls]

[SALES BY SKU STORE wk3 (retail)(2).xls]

Решения были попытки, но не удалось, потому что мои знания excel / macro / VBA очень ограничены:

  • Пункт назначения файла не изменяется. Единственная часть измененного имени файла – это номер недели. Я рассмотрел идею + 1 на номер недели каждый раз, когда новый файл был добавлен в список имен файлов. Я не знаю, как это сделать.

  • Я пробовал идею CONTCATENATE с помощью Table_Array и списка Filename. Опять же, я не знаю, как это сделать. Просмотр в Интернете заставил меня поверить, что INDIRECT – это путь вперед.

  • Пришла в голову мысль о написании операторов IF через код VBA. Потенциально hardcoding имена файлов в vlookup, так или иначе. Опять же, я не знаю, как это сделать.

Пожалуйста, может кто-нибудь пролить свет на то, как я это делаю? Если вы можете ответить с идеей, я постараюсь изо всех сил подражать ей. В этот момент я по достоинству оценю все.

Спасибо.

Что-то вроде этого – я предположил, что у вас никогда не будет более 53 финансовых недель:

 Sub updateWorkbook() Dim n As Long Const csFOLDER_PATH As String = "C:\your path\" For n = 53 To 1 Step -1 If Dir(csFOLDER_PATH & "SALES BY SKU STORE wk" & n & " (retail)(2).xls") <> vbNullString Then FileCopy csFOLDER_PATH & "SALES BY SKU STORE wk" & n & " (retail)(2).xls", csFOLDER_PATH & "SALES BY SKU STORE CURRENT (retail)(2).xls" Exit For End If Next n End Sub 

Вы можете вызвать это из Открытого события основной книги. В модуле ThisWorkbook добавьте:

 Private Sub workbook_open updateWorkbook End Sub 

Вы можете использовать

 =VLOOKUP(A2,INDIRECT("'...OTC\2016\Reports Sent\[SALES BY SKU STORE wk " & A1 & " (retail) (2).xls]SKU'!$D$1:$G$65536"),4,FALSE) 

где A1 – номер недели, с которого вы хотите импортировать данные?

  • Установить значение vlookup ячейки, основанное на изменении значения Combobox
  • Макрокодирование в Excel
  • VBA Vlookup для заполнения значений от листа1 до листа2
  • Добавление на основе текстовой строки в ячейке таблицы
  • Сравнение двух столбцов и слияния данных
  • Название ячейки FInd Defined Range
  • Проверьте, не имеют ли значения одного столбца любые значения из другого столбца
  • Передать значения строк в массив в excel
  • Vlookup в макропроводе в таблице
  • VLookup в Excel дает странные результаты
  • Vlookup с колонкой как переменная
  • Interesting Posts

    Как установить поле даты в excel с помощью java apache poi library?

    Сумма столбцов – VBA

    Несоответствие в полях при экспорте хрустального отчета в Excel

    Равная формула распределения, основанная на заданных значениях. превосходить

    Создание списка флажков Multi Selection в бок о бок ячейке в строке в Excel

    Генератор календаря Excel VBA не возвращается в апреле / ​​июне?

    Изменение списка валидации

    Как найти длину столбца в VBA с переменной последней строкой

    Как сохранить комментарии ячейки в переменной и применить ее к другой ячейке

    Файл xls с IsAddin установлен в true, не открывается из гиперссылки excel

    Обновление силы «последней» ячейки рабочего листа

    Макрос VBA для экспорта XML, который избегает строки, если определенные ячейки являются значениями «ВЫКЛ»

    Заполнить в VBA

    Добавление pandas.DataFrame в существующий файл Excel

    Ошибка компиляции VBA (ошибка автоматизации 1004)

    Давайте будем гением компьютера.