Спасибо, Тимур! Интересно, конечно, и подобные задачи возникают постоянно , но VBA это отдельная тема. Не сомневаюсь, что это вещь нужная, для многих. Но делать это по обезьяньи, просто копируя текст макроса не хочется, а знаний по VBA нет..... Хотя простейшие макросы с элементами управления я освоил и активно использую. Надеюсь, что Вы не забросите и другие модули.. А там , глядишь, и к VBA придем.....P.S. Поскольку, Вы теперь реже выпускаете видео, с удовольствием пересматриваю старые, но уже многому научившись с Вашей же помощью. Это тоже очень интересно! Огромное Вам спасибо за Ваш труд и знания, с которыми Вы так щедро делитесь!
Спасибо! Это не урок по VBA, а по применению VBA для более эффективной работы в Power Query. Человеку это может быть нужно здесь и сейчас. Применять можно сразу. Не будем же мы изучать курс VBA на 800 страниц ради нескольких приемов.
Тимур, добрый день. Подскажите, пожалуйста, три вопроса. 1) в файле существует 4 запроса в PQ. как правильно настроить их обновление поочередно, так как при REFRESHall сразу обновляются все запросы, а необходимо чтобы они поочередно обновлялись и ожидали обновления предыдущего. технология представленная ниже не срабатывает. постановка тайм аута - также не получается.(For Each wks In Worksheets
For Each lo In wks.ListObjects ' On Error Resume Next lo.QueryTable.Refresh BackgroundQuery:=False Do Until Application.CalculationState = xlDone DoEvents Loop 'Application.RefreshAll Next lo Application.CalculateUntilAsyncQueriesDone Next wks). 2) Помимо того, что в файле существуют 4 запроса PQ, так же есть 4 соответствующие сводные таблицы. в случае обновления как PQ, так и Сводных необходимо нажимать на обновление два раза (так как один раз не срабатывает). для сводных приходится два раза прописывать следующий код:(For Each PT In wks.PivotTables
PT.PivotCache.Refresh PT.RefreshTable DoEvents Next PT) и когда данных для обновления накапливается много - то при PT.RefreshTable слетают параметры группировки по датам (год, месяц, день) и все четыре таблицы расползаются. В этом вопросе - какой правильный код для обновления Сводных вы можете посоветовать в VBA + возможно ли сделать обновление поочередное. 3) Больше теоретический вопрос - указанные в файле 4 запроса PQ в себе содержат по 500тыс записей в трех запросах, и 2,5 млн записей в последнем. Первые три приходится обновлять вручную и ожидать пока пройдет их обновление. После этого приходится чистить КЕШ в параметре (Power Query-Настройки-Параметры-Параметры управления кэшем данных), после предыдущих обновлений он равен около 140 мб., сохранять файл, закрывать полностью Эксель. ожидать несколько минут, пока завершатся все MASHUP-ы и только после этого опять загружать файл и уже проводить обновление 2,5 млн записей (при этом время обработки данного потока занимает около 10 мин. в совокупности он берет данные из трех запросов PQ + порядка 200 файлов (xlsx) общим объемом около 1Gb). В случае непроделывания данной операции обновление зависает и не проходит (ждал до 2-3 часов. признаков жизни Эксель не показывал). Причем 2013 с надстройкой работает быстрее чем 2016 (где то раза в 1,5 быстрее) Сталкивались ли с такой проблемой? возможна ли оптимизация помимо Table.Buffer? Как по-правильному обновлять большие данные? поделитесь своими наблюдениями/опытом. Спасибо. p/s. Извините, но более грамотного учителя на просторах Интернета не нашел. Спрашиваешь на сайтах, там не то чтобы направление правильной мысли указать СПЕЦЫ только пыжатся от своих знаний и никакой пользу от них не имеется, а на иностранческих сайтах - гугл переводчик что-то барахлит и я не понимать, что пиндосы отвечают, а с бразильскими сайтами что то вообще туговато.... Подмогните, если время будет...
@@comrade_excel так вот как раз и не получается дождаться процесса окончательного обновления почему то ни 1) Do Until Application.CalculationState = xlDone 2) ни DoEvents 3) ни Application.CalculateUntilAsyncQueriesDone во всех различных вариантах не помогуют. Прописывал сначала по именам запросов, но код получился громоздкий и почему то очень медленный, но проблемы очередности запусков и очередности окончания процессов обновления, что еще было хуже (когда были прописаны по именам) - то обновления сводных таблиц было еще быстрее (почему то на этоEXCEL делал первый упор то ли на это тратилось меньше ресурсов, то ли загрузка задачи обновления сбивалась каким либо приоритетом выполнения - не знаю). Думал, что в рамках темы VBA и PQ можно задать такой вопрос.
@@nikbo6863 Если просто для каждой таблицы прописать строку ActiveWorkbook.Connections("Название подключения").Refresh то не сработает? А для сводных в конце написать PivotTables("Название сводной").PivotCache.Refresh
Тимур, у меня к тебе, как гению, вопрос: а как в Excel вытягивать данные из вложенного закрытого файла Excel? В Excel можно вложить другой файл Excel и вытаскивать из него данные, но только когда он открыт. А как вытащить из закрытого файла? Помоги, пожалуйста
@@comrade_excel Ну данные в Power Query берутся по адресу, указанному в отдельной строке с помощью обзора, а не с умной таблицы в экселе, а Вы в видео показываете как с помощью кнопочки менять актуальный адрес именно в умной таблице. Соответственно как мне указать путь на эту строку в умной таблице?
Файлы для спонсоров Boosty: boosty.to/comrade-xl/posts/fbba6ee1-766e-4c9c-a13d-fab1608c393b?share=post_link
Файлы для спонсоров ВК: vk.com/comrade.excel?w=wall-185123800_251
Файлы для спонсоров UA-cam: ua-cam.com/users/postUgziL8zr5MdAlIiH5hB4AaABCQ
Индивидуальное обучение: comrade-xl.ru/pq-training/
Страница: comrade-xl.ru/2019/12/04/vba-filepath/
Спасибо, Тимур! Интересно, конечно, и подобные задачи возникают постоянно , но VBA это отдельная тема. Не сомневаюсь, что это вещь нужная, для многих. Но делать это по обезьяньи, просто копируя текст макроса не хочется, а знаний по VBA нет..... Хотя простейшие макросы с элементами управления я освоил и активно использую. Надеюсь, что Вы не забросите и другие модули.. А там , глядишь, и к VBA придем.....P.S. Поскольку, Вы теперь реже выпускаете видео, с удовольствием пересматриваю старые, но уже многому научившись с Вашей же помощью. Это тоже очень интересно! Огромное Вам спасибо за Ваш труд и знания, с которыми Вы так щедро делитесь!
Спасибо! Это не урок по VBA, а по применению VBA для более эффективной работы в Power Query. Человеку это может быть нужно здесь и сейчас. Применять можно сразу. Не будем же мы изучать курс VBA на 800 страниц ради нескольких приемов.
@@comrade_excelСогласен, Тимур! Даже уже придумал, где смогу использовать!
Тимур, добрый день. Подскажите, пожалуйста, три вопроса.
1) в файле существует 4 запроса в PQ. как правильно настроить их обновление поочередно, так как при REFRESHall сразу обновляются все запросы, а необходимо чтобы они поочередно обновлялись и ожидали обновления предыдущего. технология представленная ниже не срабатывает. постановка тайм аута - также не получается.(For Each wks In Worksheets
For Each lo In wks.ListObjects
' On Error Resume Next
lo.QueryTable.Refresh BackgroundQuery:=False
Do Until Application.CalculationState = xlDone
DoEvents
Loop
'Application.RefreshAll
Next lo
Application.CalculateUntilAsyncQueriesDone
Next wks).
2) Помимо того, что в файле существуют 4 запроса PQ, так же есть 4 соответствующие сводные таблицы. в случае обновления как PQ, так и Сводных необходимо нажимать на обновление два раза (так как один раз не срабатывает). для сводных приходится два раза прописывать следующий код:(For Each PT In wks.PivotTables
PT.PivotCache.Refresh
PT.RefreshTable
DoEvents Next PT) и когда данных для обновления накапливается много - то при PT.RefreshTable слетают параметры группировки по датам (год, месяц, день) и все четыре таблицы расползаются. В этом вопросе - какой правильный код для обновления Сводных вы можете посоветовать в VBA + возможно ли сделать обновление поочередное.
3) Больше теоретический вопрос - указанные в файле 4 запроса PQ в себе содержат по 500тыс записей в трех запросах, и 2,5 млн записей в последнем. Первые три приходится обновлять вручную и ожидать пока пройдет их обновление. После этого приходится чистить КЕШ в параметре (Power Query-Настройки-Параметры-Параметры управления кэшем данных), после предыдущих обновлений он равен около 140 мб., сохранять файл, закрывать полностью Эксель. ожидать несколько минут, пока завершатся все MASHUP-ы и только после этого опять загружать файл и уже проводить обновление 2,5 млн записей (при этом время обработки данного потока занимает около 10 мин. в совокупности он берет данные из трех запросов PQ + порядка 200 файлов (xlsx) общим объемом около 1Gb). В случае непроделывания данной операции обновление зависает и не проходит (ждал до 2-3 часов. признаков жизни Эксель не показывал). Причем 2013 с надстройкой работает быстрее чем 2016 (где то раза в 1,5 быстрее) Сталкивались ли с такой проблемой? возможна ли оптимизация помимо Table.Buffer? Как по-правильному обновлять большие данные? поделитесь своими наблюдениями/опытом.
Спасибо.
p/s. Извините, но более грамотного учителя на просторах Интернета не нашел. Спрашиваешь на сайтах, там не то чтобы направление правильной мысли указать СПЕЦЫ только пыжатся от своих знаний и никакой пользу от них не имеется, а на иностранческих сайтах - гугл переводчик что-то барахлит и я не понимать, что пиндосы отвечают, а с бразильскими сайтами что то вообще туговато....
Подмогните, если время будет...
Самое и первое в оптимизации - это XLSX сохранить в CSV.
@@comrade_excel Про CSV это понятно. А по поводу других вопросов можете небольшие комментарии?
Можно же просто строку кода для обновления применить к каждой таблице.
@@comrade_excel так вот как раз и не получается дождаться процесса окончательного обновления почему то ни 1) Do Until Application.CalculationState = xlDone 2) ни DoEvents 3) ни Application.CalculateUntilAsyncQueriesDone
во всех различных вариантах не помогуют. Прописывал сначала по именам запросов, но код получился громоздкий и почему то очень медленный, но проблемы очередности запусков и очередности окончания процессов обновления, что еще было хуже (когда были прописаны по именам) - то обновления сводных таблиц было еще быстрее (почему то на этоEXCEL делал первый упор то ли на это тратилось меньше ресурсов, то ли загрузка задачи обновления сбивалась каким либо приоритетом выполнения - не знаю). Думал, что в рамках темы VBA и PQ можно задать такой вопрос.
@@nikbo6863 Если просто для каждой таблицы прописать строку ActiveWorkbook.Connections("Название подключения").Refresh
то не сработает?
А для сводных в конце написать PivotTables("Название сводной").PivotCache.Refresh
кстати, у Chris Webb-а сейчас тоже такая же постоянная проблема с MERGE :))))
Тимур, у меня к тебе, как гению, вопрос: а как в Excel вытягивать данные из вложенного закрытого файла Excel? В Excel можно вложить другой файл Excel и вытаскивать из него данные, но только когда он открыт. А как вытащить из закрытого файла? Помоги, пожалуйста
Скажите пожалуйста, *как сделать всё то же самое* , только не в power pivot, а *в power query* ?
Это и есть Power Query.
А как запрос прикрепить к адресу в умной таблице?
Я не понимаю вашего вопроса.
@@comrade_excel Ну данные в Power Query берутся по адресу, указанному в отдельной строке с помощью обзора, а не с умной таблицы в экселе, а Вы в видео показываете как с помощью кнопочки менять актуальный адрес именно в умной таблице. Соответственно как мне указать путь на эту строку в умной таблице?
Посмотрите это видео ua-cam.com/video/lhubUKHEAm0/v-deo.html
Вы это имеете в виду?
@@comrade_excel Огромное спасибо за видео!! Сейчас буду пробовать.
@@SensualSuare Уверен, что у вас все получится.