Find Your Dropbox Folder in VBA

Here’s a function to get the location of the Dropbox folder:

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.

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

2 Comments

  1. snb says:

    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
  2. Pistolprinsen says:

    Code for finding virtual any folder on your system, can be found here:
    http://www.EXCELGAARD.dk/Lib/GetSpecialFolder/

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: