Here’s a function to get the location of the Dropbox folder:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
Public Function DropBox() As String Dim DropboxHostFile As String Dim DropboxHostFileNumber As Long Dim Base64EncodedPath As String Dim TempXMLDocument As MSXML2.DOMDocument60 Dim Base64XMLNode As MSXML2.IXMLDOMElement DropboxHostFile = Environ("appdata") & "\Dropbox\host.db" DropboxHostFileNumber = FreeFile Open DropboxHostFile For Input As DropboxHostFileNumber Base64EncodedPath = Input$(LOF(DropboxHostFileNumber), DropboxHostFileNumber) Close DropboxHostFileNumber Set TempXMLDocument = New MSXML2.DOMDocument60 Set Base64XMLNode = TempXMLDocument.createElement("b64") Base64XMLNode.DataType = "bin.base64" Base64XMLNode.Text = Split(Base64EncodedPath, vbLf)(1) DropBox = StrConv(Base64XMLNode.nodeTypedValue, vbUnicode) End Function |
According to Reinaldo there is a host.db file in the Roaming directory that stores this information. The Environ("appdata")
function returns the Roaming directory in Win7. I open host.db and read in the text. Then I create a new XML document, thanks to Tim Hastings, to write in the Base64 text and read out the byte data that is converted to Unicode. There’s two lines in my host.db file, so I split on vbLf and only use the second line.
Here’s what it would look like if I wrote this function and didn’t want to poke my eyes out afterward.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
Public Function DropBox() As String Dim sFile As String Dim lFile As Long Dim sPath As String Dim xDoc As MSXML2.DOMDocument60 Dim xNode As MSXML2.IXMLDOMElement sFile = Environ("appdata") & "\Dropbox\host.db" lFile = FreeFile Open sFile For Input As lFile sPath = Input$(LOF(lFile), lFile) Close lFile Set xDoc = New MSXML2.DOMDocument60 Set xNode = xDoc.createElement("b64") xNode.DataType = "bin.base64" xNode.Text = Split(sPath, vbLf)(1) DropBox = StrConv(xNode.nodeTypedValue, vbUnicode) End Function |
another anti poking measure:
Sub M_snb()
With CreateObject("Msxml2.DOMDocument.6.0")
With .createElement("b")
.DataType = "bin.base64"
.Text = Split(CreateObject("scripting.filesystemobject").opentextfile(Environ("appdata") & "\DropBox\host.db").readall, vbLf)(1)
MsgBox StrConv(.nodeTypedValue, 64)
End With
End With
End Sub
Code for finding virtual any folder on your system, can be found here:
http://www.EXCELGAARD.dk/Lib/GetSpecialFolder/
Update: The host.db has been removed by DropBox. The new file is info.json
Thanks Lawrence. I’ve never used MS ScriptControl 1.0 before and this was a good opportunity to do so.
Updated Sep 2019:
It’s in
C:\Users\username\AppData\Local\Dropbox\info.json
contains
{“personal”: {“path”: “C:\\Path to my\\Documents\\Dropbox”, “host”: 1234567890 “is_team”: false, “subscription_type”: “Basic”}}
Open VBA.Interaction.Environ(“USERPROFILE”) & “\AppData\Local\Dropbox\info.json” For Input As #intFile
and parse it any way you like. Code in SO link:
Thanks to
https://stackoverflow.com/questions/35297728/excel-vba-open-a-workbook-from-dropbox-then-save-close-current-workbook