Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Oct 2011
    Location
    Auckland. New Zealand
    Posts
    55
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Accessing documents in Dropbox using VBA

    And today's little challenge....

    Some brightspark has indicated he wants to put my Word template and the Excel spreadsheets it accesses onto Dropbox.

    I know little about Dropbox - I've had a look around the website and it's easy enough to use to save files, etc. but I can't find anything that tells me how I can use VBA to access files in it.

    So I have a couple of questions:
    1. If I save a template in Dropbox, will Word check for macro security? (i.e. at the moment I save my templates in a trusted location. How does this work in Dropbox?)
    2. I currently hard-code the location of the spreadsheets I want to access in my template. How do I hardcode a Dropbox location?

    Any help or links to helpful websites would be appreciated.

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    New Lounger
    Join Date
    Dec 2009
    Location
    Melbourne, Australia
    Posts
    15
    Thanks
    1
    Thanked 0 Times in 0 Posts
    In terms of the hard-coded links:
    Think of the position if you were emailing the documents. The relevant folder structure for the recipient would need to be identical to that on your workstation for the links to work.

    Although I have not used Dropbox extensively, it is just a storage medium allowing designated people to download the file(s). In this case the same applies as with email.

    If the links have relative addressing, things would be a little simpler as the recipient would only need to set up the same relative structure (the simplest would be to have all files in the same folder, but this is not really necessary).

    I hope that makes it a little clearer, DropBox is not magic.

  4. #3
    Star Lounger
    Join Date
    Oct 2011
    Location
    Auckland. New Zealand
    Posts
    55
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Thanks for your reply Warwickw, but unfortunately it doesn't really help me. I'll do some research on relative addressing to see if this helps. My challenge has been that when my colleague emails me the dropbox link, I'm having problems accessing it on my PC (which is a reasonably new laptop running Windows 7). Any other help would be appreciated as I'm guessing this is something that others do.

  5. #4
    New Lounger
    Join Date
    Oct 2012
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Accessing documents in Dropbox using VBA

    Dropbox simply occupies a folder (designated by you at install and usually something like C:\Users\user1\Desktop\Dropbox) on your hard drive. Any other computer you use for this dropbox simply replicates this folder to the install folder on that machine which is a waste of space but does mean you always have a local copy. If you want to hard code this location and receive files from other users just give them a share in your dropbox and get them to dump any files there. If they are sending links you can simply keep a list of the shares that you allocate and from the senders info parse out their location and replace it with yours. Alternatively get a share from their dropbox into yours and do the same.
    Regards,
    John

  6. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,243
    Thanks
    202
    Thanked 796 Times in 729 Posts
    John,

    Welcome to the Lounge as a new poster!
    Thanks for helping out on this one.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  7. #6
    New Lounger
    Join Date
    Nov 2013
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find Dropbox folder programatically in VBA

    I had the same issue and wrote this code to solve it. You can access the DropBox folder with the Excel Function =DropboxFolder(). I hope this helps you. I cache the folder after finding it the first time so it is quite fast. Just cut and paste from below

    'THIS REQUIRES A REFERENCE TO MICROSOFT XML, V2.6 OR HIGHER
    'In VBA, go to Tools, References, then select the Microsoft XML from the list

    'store the folder as a variable so you don't have to recheck it each time
    Public strDropBoxFolder As String

    'This locates and returns the full path of the DropBox folder
    Public Function DropBoxFolder() As String

    Dim DBhost As String
    Dim strInput As String
    Dim DBPath As String
    On Error GoTo Error_Handler

    'If the folder has already been found, don't recheck it, just use the stored variable
    If strDropBoxFolder <> "" Then
    DropBoxFolder = strDropBoxFolder
    Exit Function
    End If
    'otherwise, go find it.
    ' find the host.db file
    DBhost = CStr(Environ("USERPROFILE") & "\AppData\Roaming\Dropbox\host.db")

    If Dir(DBhost) <> "" Then
    Open DBhost For Input Access Read As #1
    'read the host.db file
    Line Input #1, strInput
    Close #1
    'decode from Base64
    strInput = StrConv(DecodeBase64(strInput), vbUnicode)
    'ignore the first line and extract the file path by looking drive name such as C:\
    DBPath = Mid(strInput, InStr(strInput, ":\") - 1)
    'return the result
    DropBoxFolder = DBPath
    'store the result for future use
    strDropBoxFolder = DBPath
    Else
    'file not found and error are handled the same way
    Error_Handler:
    On Error Resume Next

    strDropBoxFolder = ""
    End If

    End Function


    Private Function DecodeBase64(ByVal strData As String) As Byte()

    Dim objXML As MSXML2.DOMDocument
    Dim objNode As MSXML2.IXMLDOMElement

    ' help from MSXML
    Set objXML = New MSXML2.DOMDocument
    Set objNode = objXML.createElement("b64")
    objNode.DataType = "bin.base64"
    objNode.Text = strData
    DecodeBase64 = objNode.nodeTypedValue

    ' thanks, bye
    Set objNode = Nothing
    Set objXML = Nothing

    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •