Ответ 1
WIP:
Так как написать это, чтобы быть достаточным? Суть в том, что вы должны настроить свой запрос, используя встроенные инструменты, а не VBA. Вы загружаете свои данные с помощью соответствующего метода, который может быть из файла, зацикливая файлы в папке, сети, базе данных... список можно продолжить. Вы можете импортировать из внешних источников, а также загружать из внутренних. Посмотрите здесь для получения дополнительной информации о загрузке из внешних источников.
Как только вы защитите свой источник и загрузите его, вам будет представлен редактор запросов, в котором вы сможете выполнить свои шаги преобразования.
Дело в том, что когда вы выполняете свои действия с использованием пользовательского интерфейса, М-код пишется в фоновом режиме и формирует основу для повторного использования запроса, если вы не меняете исходный формат или местоположение.
В вашем случае, когда вы выполнили свои шаги и получили запрос, который хотите, затем закройте и загрузите на sheet2.
На этом шаге при первой настройке вы выберете лист 2 в качестве места закрытия и загрузите пункт назначения:
NB. Когда вы выбираете существующий лист, убедитесь, что лист 2 уже существует, и вы можете вручную редактировать лист 2! перед предложенным диапазоном.
У вас возникают проблемы, потому что вы продолжаете пытаться воссоздать все это с помощью кода.
Не. Установите его с помощью пользовательского интерфейса и загрузите в sheet2. С этого момента либо откройте редактор запросов, чтобы отредактировать шаги и/или обновить запрос, чтобы загрузить существующий лист2 с новыми/обновленными данными.
Некоторые из доступных методов обновления вашего запроса:
Запрос будет обновляться с помощью VBA/Обновление вручную на листе, в котором он находится (Sheet2), или на саму книгу, например, Sheet2.Calculate, ThisWorkbook.RefreshAll, вручную нажимая кнопку обновления книги на вкладке данных (все это на самом деле перебор )
Более целенаправленные методы:
VBA для таблицы запросов на листе 2:
ThisWorkbook.Worksheets("Sheet2").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
Измените вышеуказанное на соответствующую таблицу и т.д.
Щелкните правой кнопкой мыши на самой таблице запросов и выберите "Обновить":
Нажмите кнопку обновления в окне запросов к рабочей книге справа для рассматриваемого запроса (значок с зелеными кружевными стрелками).
Кен Тянет путь VBA (незначительное редактирование от меня)
Option Explicit
Public Sub UpdatePowerQueries()
' Macro to update my Power Query script(s)
Dim lTest As Long, cn As WorkbookConnection
On Error Resume Next
For Each cn In ThisWorkbook.Connections
lTest = InStr(1, cn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1", vbTextCompare)
If Err.Number <> 0 Then
Err.Clear
Exit For
End If
If lTest > 0 Then cn.Refresh
Next cn
On Error GoTo 0
End Sub
Не должно быть никакой реальной необходимости выполнять всю эту работу через VBA. У вас могут быть некоторые хитрые манипуляции с данными, которые вам удобнее делать с VBA, а затем иметь PowerQuery для доступа к обработанным данным в качестве источника. Вы можете запустить весь лот, запустив подпрограмму, которая вызывает процедуру обработки, а затем использует один из перечисленных выше методов команд VBA. Есть больше методов, и я добавлю их, когда у меня будет больше времени.
Расчеты:
Если у вас есть расчеты, которые зависят от вывода PowerQuery, у вас есть 4 очевидных немедленных варианта:
- Добавьте эти вычисления, где это возможно, в PowerQuery. Он поддерживает вычисляемые столбцы, пользовательские функции и многое другое.
- Добавьте выходные данные PowerQuery в модель данных и используйте модель данных для выполнения вычислений, включая вычисляемые поля. Это также даст вам доступ к функциям временной разведки.
- Используйте VBA, чтобы добавить вычисления в соответствующие области на листе 2, если диапазон изменяется при обновлении.
- Если диапазон не меняется при обновлении, просто уберите ваши формулы.