Log In
Register
Home
New Member
November 28, 2019
Offline
Hi. First Post!
I use Dropbox for all my files, and across all my team.
I recently set up a spreadsheet using Power Queries on my laptop at home with all the necessary files stored in one folder.
However the location of the files at work is a bit different because Dropbox resides on a different drive. So the power query links are broken.
Is there a way to link files that are stored in Dropbox so that they will work in multiple locations?
Thanks in advance!
Raj
See Answer
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Rajeev,
When dropbox is installed, a json file is created in AppData folder, you can extract the dropbox path from that file using a simple code that can be found online.
The json looks like :
{"personal": {"path": "E:\\Dropbox", "host": xxxxxx, "is_team": false, "subscription_type": "Basic"}}
For dropbox business, the json has also the business details.
Dim RegEx As Object, MatchColl As Object, DataLine As String
Dim FileNum As Integer: FileNum = FreeFile
Set RegEx = CreateObject("VBScript.RegExp")
RegEx.Global = True
RegEx.IgnoreCase = False
Open Environ("LOCALAPPDATA") & "\Dropbox\info.json" For Input As #FileNum
DataLine = Input(LOF(FileNum), #FileNum)
Close #FileNum
RegEx.Pattern = "^.*""path"": ""([^""]*).*"
DropboxPath = Replace(RegEx.Replace(DataLine, "$1"), "\\", "\")
End Function
You can even get data from that simple json using text functions.
With this function, you can put the put in a table that is used by your query.
Answers Post
New Member
November 28, 2019
Offline
Brilliant! Thank you!
February 20, 2019
Offline
Catalin,
Sorry that I don't quite understand. I have the same scenarios as I saved files/folders under the Dropbox folder with different drives, at home and atand work.
How can insert my folder path or file path into the code and pale it as the source of Power Query? Assuming that
the folder path is: dropbox/a../b../c../XXXXXX (XXXXXis the folder name);
the file path is: dropbox/x../y../z../BBBBB (BBBBB is the file name);
I hope that I make my questions clear to you.
Thanks,
Brent
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Jim/Brent,
You should have a table with 1 column and 1 row, the table name will be SourceFolder:
| Source Folder |
| E:\Dropbox |
The value in this cell From Source Folder column should be written by a vb code called at workbook_open event.
The following code should be placed into ThisWorkbook vb module:
Private Sub Workbook_Open()
ThisWorkbook.Worksheets("Settings").ListObjects("SourceFolder").Range.Cells(1).Offset(1, 0).Value = DropBoxPath
End Sub
The function provided above should be in a normal vb module.
In your query, you can refer to this cell like this:
Folder= Excel.CurrentWorkbook(){[Name="SourceFolder"]}[Content]{0}[Source Folder],
Source = Folder.Files(Folder),
1 Guest(s)
