Страницы: 1 2 След.
RSS
Power query и настройка линка между папками и таблицами через DropBox
 

Добрый день,

Через Power Query в экселе настроены линки в папки и файлы.

Тк сервера привычного нет, все папки с файлы хранятся в DropBox, при этом все работают в папках и файлах в основном через PC.

Пример ссылки на файл, который соединен с другими файлами  -

C:\Users\имя сотрудника\Dropbox (название компании)\название папки\сам файл

Пример -  C:\Users\Ivan Ivanov\Dropbox (lys)\LYS - Operations\- Knowledge Management\Pricelist

Эту ссылка используется в графе source в power query и через этот линк происходит обновление данных.

Проблема в том, что тк  сервера нет, то адрес ссылки у всех сотрудников не одинаковый и отличается только username. Те по сути ссылка таже, но отличие только в имени и фамилии.

Пример - C:\Users\Petr Petrov\Dropbox (lys)\LYS - Operations\- Knowledge Management\Pricelist

Соответственно, файлы с отчетами обновляются только если сотрудник, которые настроил линк, открыл файл. Остальные могут работать в файлах, но отчеты не обновятся.

Как решить эту проблему, как избежать имени и сделать беспроблемный доступ?

Пыталась загрузить ссылку через WEB, там нет имени, но выдает Power Query выдает ошибку

Пример того же линка через WEB https://www.dropbox.com/home/LYS%20-%20Operations/-%20Knowledge%20Management/Pricelist

Не могу найти решение проблемы.

Если кто знает и может помочь, было бы здорово

Заранее благодарю

Катя

Изменено: bonjour3 - 24.08.2018 09:00:00
 
Цитата
bonjour3 написал:
Пыталась загрузить ссылку через WEB, там нет имени, но выдает Power Query выдает ошибку
Покажите код как пытались?
Что за ошибка?
Вот горшок пустой, он предмет простой...
 
Доброе время суток.
Варианты.
1. Сделать отчёт с udf-функцией
Код
Public Function getUserHomeFolder()
    getUserHomeFolder = Environ$("HomeDrive") & Environ$("HomePath")
End Function
которая в именованную ячейку листа возвращает путь к домашней папке текущего пользователя. В Power Query остаётся забрать этот путь и на его базе собирать полный путь к отчётным данным.
2. Сделать vbscript со следующим кодом
Код
Option Explicit
OnUserLogon
Public Sub OnUserLogon()
    Dim fso, pShell, pStream, itr
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set pShell = CreateObject("WScript.Shell")
    Set pStream = fso.CreateTextFile("C:\ProgramData\CurrentUserHome.txt", True)
    pStream.Write pShell.ExpandEnvironmentStrings("%HomeDrive%") & pShell.ExpandEnvironmentStrings("%HomePath%")
    pStream.Close
    Set pStream = Nothing: Set pShell = Nothing: Set fso = Nothing
End Sub
Скрипт при входе в систему будет создавать в общей папке всех пользователей ПК файл C:\ProgramData\CurrentUserHome.txt, в котором будет записан путь к домашней папке. В Power Query нужно будет прочитать строку пути и собрать полный путь к отчётным данным.
 
Можно сделать проще средствами самого PQ:
Сделать запрос к папке C:\Users\
По полю Folder Path отсечь все после третьего слеша
убрать дубликаты
Учитывая что разрешение на просмотр файлов у текущего юзверя будет только к своей папке, то он там увидит список вида
Folder Path
C:\Users\
C:\Users\All Users
C:\Users\Default
C:\Users\Public
C:\Users\UserName
Соответственно зачищаем все эти паблики, дефолты и т.д. и получаем нужное нам имя.
Изменено: PooHkrd - 24.08.2018 10:46:40
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
у текущего юзверя будет только к своей папке
тогда придётся проверять каждую папку (кроме исключаемых по имени) на предмет доступа. Так как если работали на этом ПК несколько человек, то узнать свою можно только по ошибке просмотра содержимого папки. Увы, нет пока в PQ получения данных о пользователе.
 
Андрей, я же сначала попробовал.  8)
PQ видит все файлы во вложенных папках, поэтому при способе указанном мною выше он увидите все вообще файлы кроме тех, к которым запрещен доступ, и пути к доступным отобразит в поле Folder Path. А дальше этим фактом можно гнусно воспользоваться.  :D
Код
let
    Источник = Folder.Files("C:\Users"),
    #"Извлеченный текст перед разделителем" = Table.TransformColumns(Источник, {{"Folder Path", each Text.BeforeDelimiter(Text.From(_, "ru-RU"), "\", 2), type text}}),
    #"Удаленные дубликаты" = Table.Distinct(#"Извлеченный текст перед разделителем", {"Folder Path"})
in
    #"Удаленные дубликаты"
Изменено: PooHkrd - 24.08.2018 10:57:56
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
Цитата bonjour3  написал:Пыталась загрузить ссылку через WEB, там нет имени, но выдает Power Query выдает ошибкуПокажите код как пытались?Что за ошибка?
Выдает ошибку DataFormat.Error  the supplier file path must be a valid absolute path.

Я поменяла в настройках на другие типы файла, тк у меня было open as excel workbook. Начал вроде бы работать тип HTML page, но так и не загрузился, выдает ошибку the web.page function did not finish within timeout  of 100 seconds.
Странно, что ограничивается на 100 секундах.

Ни один из других выбранных типов не сработал.

PS: подготовила скрины, чтоб наглядно показать, а тут не получается отправить
 
Цитата
PooHkrd написал:
я же сначала попробовал.
Я тоже. У меня, например, доступна такая папка "836D~1!"
 
Цитата
PooHkrd написал:
Можно сделать проще средствами самого PQ:Сделать запрос к папке C:\Users\ По полю Folder Path отсечь все после третьего слешаубрать дубликатыУчитывая что разрешение на просмотр файлов у текущего юзверя будет только к своей папке, то он там увидит список видаFolder PathC:\Users\C:\Users\All UsersC:\Users\DefaultC:\Users\PublicC:\Users\UserNameСоответственно зачищаем все эти паблики, дефолты и т.д. и получаем нужное нам имя
У меня сейчас линк такой в source

= Excel.Workbook(File.Contents("C:\Users\Ekaterina Churilova\Dropbox (lys energy solutions)\LYSES - Operations\- Knowledge Management\Pricelist\PRICELIST_LYS.xlsx"), null, true)

Те мне надо вот эту часть C:\Users\Ekaterina Churilova\Dropbox (lys energy solutions)\LYSES - Operations\- Knowledge Management\Pricelist\PRICELIST_LYS.xlsx
поменять, так?

Те будет C:\Users\All Users\Dropbox (lys energy solutions)\LYSES - Operations\- Knowledge Management\Pricelist\PRICELIST_LYS.xlsx

PS: Не уверена, что поняла верно, что именно надо мне сделать. Никогда не делала это раньше, поэтому торможу.
 
Андрей VG, странно, у меня на трех компах вываливаются только стандартные папки и моя. Ваша папка вообще похожа на какие-то пережитки старого DOS-овского прошлого.
Изменено: PooHkrd - 24.08.2018 11:46:39
Вот горшок пустой, он предмет простой...
 
bonjour3, сделайте отдельный запрос, с именем, например, UserName. Поместите туда мой код, он вам выдаст список папок, файлы в которых видит PQ.
Далее вам этот список уже своими силами нужно очистить от мусора, чтобы осталась единственная строка которая определяет имя юзера. Из этой строки вытаскиваете текстовое значение, чтобы именно оно было результатом запроса.
После уже втыкаете имя этого запроса в путь, которым вы считываете данные из файла. Типа того:
Код
= Excel.Workbook(File.Contents("C:\Users\" & UserName & "\Dropbox (lys energy solutions)\LYSES - Operations\- Knowledge Management\Pricelist\PRICELIST_LYS.xlsx"), null, true)

Но обратите внимание на замечания Андрея, ибо это не универсальный способ, на некоторых компьютерах могут быть проблемы.
И есть еще один момент, у себя обнаружил: если используете в работе PowerPivot, то он может оставлять во временных папках после себя файлы-хвосты с очень длинными именами, которые PQ определяет как ошибочные, вот эти файлики тоже могут вам напакостить в таком виде решения проблемы.
Изменено: PooHkrd - 24.08.2018 11:58:55
Вот горшок пустой, он предмет простой...
 
Аналогичную задачу решил с помощью подсказки Дмитрия Щербакова
https://www.excel-vba.ru/chto-umeet-excel/otnositelnyj-put-k-dannym-powerquery/
OfficeProPlus 365x64
Win64forWorkstation
 
vector1, и каким образом имя юзера самостоятельно появится в таблице? Вопрос ТСа как определить название папки, относящейся к юзеру, который открыл файл со скриптом PQ/
Вот горшок пустой, он предмет простой...
 
ТС хочет, чтобы запросы в дропе обновлялись у любого пользователя этого файла а не только у того, кто этот запрос написал.
Решение - по ссылке "относительный путь" (вариант 2)
OfficeProPlus 365x64
Win64forWorkstation
 
пускай топик стартер скажет, помог ли решить его задачу такой вариант
OfficeProPlus 365x64
Win64forWorkstation
 
Для срабатывания варианта 2 требуется, чтобы файл со скриптом лежал в документах или еще где. Тогда сработает формула ЯЧЕЙКА. А где он по факту будет лежать это большой вопрос. Например, у меня в компании есть требование, что все рабочие файлы должны располагаться на сетевом диске.
Изменено: PooHkrd - 24.08.2018 13:28:58
Вот горшок пустой, он предмет простой...
 
у меня работает так.
есть файл с запросом на дропе
в файле с запросом есть дополнительный лист с таблицей (все как и у Щербакова Дмитрия описано)
запрос ссылается на другие файлы или папки на том же дропе.
к дропу имеют доступ еще пять человек и каждый может обновлять файл с запросом
по-моему, это и есть решение той задачи, которую поднял ТС
OfficeProPlus 365x64
Win64forWorkstation
 
Короче, вот вариант, который отлично подходит для локальной машины:
Код
let
    Users = Folder.Contents("C:/Users"),
    CurrentUser = Table.FirstN(Table.Sort(Users,{{"Date accessed", Order.Descending}}),1)[Name]{0}
in
    CurrentUser

При работе на удаленном рабочем столе такая фишка может и не помочь. Источник мудроты.
Вот горшок пустой, он предмет простой...
 
Цитата
vector1 написал:
пускай топик стартер скажет, помог ли решить его задачу такой вариант
ТС уже своё мнение высказал
Цитата
bonjour3 написал:
Не уверена, что поняла верно, что именно надо мне сделать. Никогда не делала это раньше, поэтому торможу.
Так что ждём отклика, что пошло, а что нет.
 
Андрей VG, в последнем коде, который я запостил в #18 если не смотреть на дату последнего доступа а попробовать перебрать таблицы на возможность доступа к ним. Какой функцией можно к ним обратиться, чтобы редактор запросов не просил меня авторизоваться, а просто выдал бы ошибку?
Думаю, что тогда можно было бы однозначно определять имя юзверя даже на Удаленках при одновременной работе нескольких пользователей.
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
Какой функцией можно к ним обратиться, чтобы редактор запросов не просил меня авторизоваться, а просто выдал бы ошибку?
Увы, Алексей. Потыкал несколько вариантов - результат один "Не удалось пройти проверку подлинности с указанными учётными данными. Повторите попытку" :(
 
На трех компьютерах в которых я работаю прописал в настройках самого Dropbox одинаковый путь хранения файлов и забыл про проблемы с источниками

 
 
Цитата
Андрей VG написал:
Так что ждём отклика, что пошло, а что нет.
Понедельник  - день тяжелый....
Пытаюсь разбираться в том, что Вы написали и посоветовали.

Пока ничего не вышло, но я еще не до конца вникла.

Отпишусь. Надеюсь, смогу сделать.
 
Еще вариант... создать в файле имя с формулой =ПОЛУЧИТЬ.РАБОЧЕЕ.ПРОСТРАНСТВО(26)
Вывести это куда-нибудь на лист и забирать оттуда.

Недостатки: сохранять надо в книге с поддержкой макросов (xlsm, например). Скорее всего, понадобится пересчёт при открытии.
Но раз уж книга с поддержкой макросов, то проще то, что написал Андрей VG в сообщении #3

PooHkrd, нет надежного варианта. Проверил сейчас на рабочем компьютере: к моей папке последняя дата доступа 16.08, а самая поздняя - у папки All Users, 22.08
При этом в глубинах своей папки сегодня 100% что-то добавлял и изменял
F1 творит чудеса
 
Максим Зеленский, так я ж специально указывал, что все папки с типовыми именами All users, TEMP и иже с ними можно сразу отфильтровывать, про них же точно известно, что это не оно.
Вот горшок пустой, он предмет простой...
 
PooHkrd, ну это так, только у меня еще есть учётка "Администратор", например, а также учётки других пользователей, входивших в свои аккаунты на этом компьютере. Не всё так просто, увы. На другом компе будет другой набор юзверей.
Через список доступных файлов - это, конечно, другой разговор, там можно порыться, но уж очень это.... долго :)
Ну это ж Excel, можно использовать весь комплект имеющихся возможностей. Так что я за макрос - прописать на Workbook_Open и всех делов.
F1 творит чудеса
 
Добрый день,

ПОЛУЧИЛОСЬ, спустя сутки мучений, толи я их, толи они меня :-)

С макросами вообще никак. Попыталась через power query и формул =LEFT(CELL("filename"),FIND("[",CELL("filename"))-1)&"source.xlsx") и последующим преобразованием и указанием в source.

Попыталась воспользоваться способом, который был описан Дмитрием Щербаковым.  При этом нашла похожий способ, но с более пошаговым описанием. тк все же по ссылке на описание Д.Щербакова не удалось сделать с первых попыток.

- https://www.youtube.com/watch?v=kRB6O2qxsIc&index=25&list=PL-ZLPvlhqeYH6HBrP2gB8ef_vwVmgDayV


Большое всем спасибо за помощь и советы!
 
Ребятушки,

Опять проблема.

Вчера решила задачу, но как оказалось, все работает, если оба файла держать вместе в одной папке или если оба файла перемещаются в другие папки одновременно.

Или может я все же что-то не так сделала, поэтому моя проблема до конца не решена.

Попытаюсь объяснить по-другому.

Power query настроен в файле с отчетами и в него тянутся данные из файла PriceList.

Файл PriceList, откуда тянутся данные в файл с отчетами по проектам будет лежать ВСЕГДА в одной папке.

А проектые файлы будут перемещаться и переименовываться.

Вчера я не пыталась ничего перемещать, попробовала обновить отчетный файлс с нескольких PC в одной папке  и поэтому я подумала, что все работает. В итоге опять нет обновлений файла через Power query у остальных сотрудников если отчетные файлы перемещены и переименованы.

Как быть?

Может как еще можно настроить относительный путь в папке?
 
Вроде бы все варианты какие только возможно, как получить имя пользователя и через него сформировать путь к папке выше были указаны. Я насчитал аж 4 штуки. Какой из вариантов у вас не работает?
Вот горшок пустой, он предмет простой...
 
 Я пробовала использовать вот этот способ. Похожий на тот, что был рекомендован Вами (решение, описанное Д.Щербаковым)

https://www.excel-vba.ru/chto-umeet-excel/otnositelnyj-put-k-dannym-powerquery/

https://www.youtube.com/watch?v=kRB6O2qxsIc&index=25&list=PL-ZLPvlhqeYH6HBrP2gB8ef_vwVmgDayV

Может я что-то упустила при использовании формул и функций, но при перемещении файла В который тянутся данные(там где и расположен запрос power query), связь теряется.

Не пойму, что не так.
Страницы: 1 2 След.
Наверх