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
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") & "Dropboxinfo.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.
Brilliant! Thank you!
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
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),
