Есть две базы, одна размещена на SQL-сервере, а выгрузки из второй базы пользователь сохраняет в виде CSV-файлов на своем компьютере. Создал в PowerQuery табличку, которая объединяет по определенным параметрам обе базы. Параметры подключения к SQL-серверу статичны, а вот путь к CSV-файлу, естественно, у каждого пользователя свой.
Суть вопроса: как в PowerQuery макросом изменить путь к источнику(CSV)
Подскажите как сделать или дайте ссылку где почитать.
Здравствуйте, форумчане,
Есть две базы, одна размещена на SQL-сервере, а выгрузки из второй базы пользователь сохраняет в виде CSV-файлов на своем компьютере. Создал в PowerQuery табличку, которая объединяет по определенным параметрам обе базы. Параметры подключения к SQL-серверу статичны, а вот путь к CSV-файлу, естественно, у каждого пользователя свой.
Суть вопроса: как в PowerQuery макросом изменить путь к источнику(CSV)
Подскажите как сделать или дайте ссылку где почитать.boa
Я пишу (там макрос для выбора пользователем нужного файла) источник в умную таблицу "ТабФайл" в файле с запросом. Получаю табличку
Файл для обработки - это шапка Полный путь - это единственная ячейка
Затем уже в запросе Квери вот так
[vba]
Код
let Источник0 = Excel.CurrentWorkbook(){[Name="ТабФайл"]}[Content], #"Измененный тип1" = Table.TransformColumnTypes(Источник0,{{"Файл для обработки", type text}}), Файл= #"Измененный тип1"{0}[Файл для обработки], Источник = Excel.Workbook(File.Contents(Файл), null, true), ...
[/vba]
Я пишу (там макрос для выбора пользователем нужного файла) источник в умную таблицу "ТабФайл" в файле с запросом. Получаю табличку
Файл для обработки - это шапка Полный путь - это единственная ячейка
Затем уже в запросе Квери вот так
[vba]
Код
let Источник0 = Excel.CurrentWorkbook(){[Name="ТабФайл"]}[Content], #"Измененный тип1" = Table.TransformColumnTypes(Источник0,{{"Файл для обработки", type text}}), Файл= #"Измененный тип1"{0}[Файл для обработки], Источник = Excel.Workbook(File.Contents(Файл), null, true), ...
_Boroda_, Спасибо за ответ, я уже думал сделать доп.файл Excel или даже лучше Access, т.к. не потребуется открытия для обновления данных, и распространять их парой в одном Кверя, а второй линкуется к CSV тогда без явного открытия второго файла его можно всегда перелинковать, а в файле с кверей через относительный путь прописать линк. Но это опять же "танцы с бубном" Надеялся, что где-то в свойствах доступных из VBA с Кверей можно такое сделать, как, например, с коннектами.
_Boroda_, Спасибо за ответ, я уже думал сделать доп.файл Excel или даже лучше Access, т.к. не потребуется открытия для обновления данных, и распространять их парой в одном Кверя, а второй линкуется к CSV тогда без явного открытия второго файла его можно всегда перелинковать, а в файле с кверей через относительный путь прописать линк. Но это опять же "танцы с бубном" Надеялся, что где-то в свойствах доступных из VBA с Кверей можно такое сделать, как, например, с коннектами. boa
И снова, здравствуйте, форумчане. Докопался я до сути и для своего примера написал следующий код:
[vba]
Код
Private Sub UpdatePathSource() ' '' Author: boa '' Written: 22.11.2018 ' Description: Меняет путь к источнику .csv в строке подключения Query Dim sFormula$, sSource$, sFolder$ On Error Resume Next With Application.FileDialog(msoFileDialogFolderPicker) .Title = "Выберите папку, в которой сохранены CSV отчеты." .InitialFileName = ThisWorkbook.Path .Show sFolder = .SelectedItems(1) ' получем путь к выбранной пользователем папке If Err.Number <> 0 Then MsgBox "Вы ничего не выбрали!" Exit Sub End If End With
sFormula = Application.ThisWorkbook.Queries.Item("PeTra").Formula ' PeTra - условное имя изменяемой таблицы, может быть представлено индексом Debug.Print sFormula 'листинг запроса
Sheet1.UsedRange.ListObject.QueryTable.Refresh ' обновляем данные
If Err.Number = 0 Then 'если все ОК, то MsgBox "Путь к файлу успешно изменен." & vbCrLf, vbInformation, "" End If End Sub
[/vba]
Но, имейте ввиду, что он работает только в Excel 2016 и новее, где PowerQuery уже вшит. Кстати, через коллекцию WorkbookQuery можно переписать/создать запрос полностью. Правда, когда я пытался в окне Watches просмотреть для Queries свойство Parent, у меня Excel "ложился" полностю, при том, что рессурсы у компа, как у космического корабля. Думаю, это еще майкрософтовская сырость.
Еще раз спасибо Александру за отзывчивость. Ваше авторитетное мнение
В Excel 2016 (в 2019 наверное тоже) Квери вшит в Excel и им можно управлять макросами
заставило меня не бросать "лопату" и "копать" дальше.
З.Ы. для Excel 2013 с установленной надстройкой PowerQuery, аналогичного решения пока не нашел, но мне пока и не особо надо.
И снова, здравствуйте, форумчане. Докопался я до сути и для своего примера написал следующий код:
[vba]
Код
Private Sub UpdatePathSource() ' '' Author: boa '' Written: 22.11.2018 ' Description: Меняет путь к источнику .csv в строке подключения Query Dim sFormula$, sSource$, sFolder$ On Error Resume Next With Application.FileDialog(msoFileDialogFolderPicker) .Title = "Выберите папку, в которой сохранены CSV отчеты." .InitialFileName = ThisWorkbook.Path .Show sFolder = .SelectedItems(1) ' получем путь к выбранной пользователем папке If Err.Number <> 0 Then MsgBox "Вы ничего не выбрали!" Exit Sub End If End With
sFormula = Application.ThisWorkbook.Queries.Item("PeTra").Formula ' PeTra - условное имя изменяемой таблицы, может быть представлено индексом Debug.Print sFormula 'листинг запроса
Sheet1.UsedRange.ListObject.QueryTable.Refresh ' обновляем данные
If Err.Number = 0 Then 'если все ОК, то MsgBox "Путь к файлу успешно изменен." & vbCrLf, vbInformation, "" End If End Sub
[/vba]
Но, имейте ввиду, что он работает только в Excel 2016 и новее, где PowerQuery уже вшит. Кстати, через коллекцию WorkbookQuery можно переписать/создать запрос полностью. Правда, когда я пытался в окне Watches просмотреть для Queries свойство Parent, у меня Excel "ложился" полностю, при том, что рессурсы у компа, как у космического корабля. Думаю, это еще майкрософтовская сырость.
Еще раз спасибо Александру за отзывчивость. Ваше авторитетное мнение
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
