2

Dropbox for Teams are doing an upgrade on December 10, 2022

As part of the upgrade, the directory of Dropbox will be changed from Dropbox (me) to me (Dropbox).

This is part of the email we received.

The Dropbox folder name will change from “Dropbox [team name]” to “[Team name] Dropbox.” You may need to modify any third-party automation file paths that are using the old Dropbox folder name for them to work properly after the upgrade.

At the moment we hard-code the dropbox file path in our VBA code

fromPath = "C:\Dropbox (me)\Development\" + aDir + "\"

Is there are a way of replacing this with code that will work both before and after the change.

For instance

fromPath = getDropBoxPath()+"\Development\" + aDir + "\"
gordon613
  • 2,770
  • 12
  • 52
  • 81
  • 2
    You can try to check if dropbox stores the path it uses in the registry. To do that, open the Registry Editor, press Ctrl+f and in the search field, enter the path it currently uses. I don't have dropbox on my machine, so I can not tell you where to look exactly, but it might be `Computer\HKEY_CURRENT_USER\Software\SyncEngines\Providers\...`. Once you have found where the path is stored, you can use VBA to read that information from the registry. For a good example of how this can be done for example for OneDrive, look [here](https://stackoverflow.com/a/72736924/12287457). – GWD Dec 01 '22 at 11:52
  • 1
    Alternatively, you can check if Dropbox adds its working directory to your environment variables, which can be accessed using VBA. Use the following one-liner to print your environment variables to the immediate window: `Dim i As Long: For i = 1 To 50: Debug.Print Environ(i): Next i`. This will print a list of `%Key%=%Val%` to your immediate window. If the DropBox path appears there, you can access it using `Environ(%key%)`. – GWD Dec 01 '22 at 11:59
  • Thanks @GWD for your great ideas. I checked the environment variables, both on my computer and a colleague's, and it appears Dropbox does not add its working directory to the environment variables. I also looked in the registry - I found OneDrive in the place you mention, but I could not find Dropbox anywhere – gordon613 Dec 01 '22 at 15:53

1 Answers1

3

The following code will do what I want. It is based upon @Reinaldo's answer in How do I programmatically locate my Dropbox folder using C#?.

Function getDropboxPath()
  jsonPath = Environ$("LocalAppData") + "\Dropbox\info.json"
  Open jsonPath For Input As #1
    Input #1, firstLine
    aSplit = Split(firstLine, Chr(34) + "path" + Chr(34) + ":")
  Close #1
  getDropboxPath = aSplit(1)
End Function

I know the code can be improved but this is probably enough for me at the moment.

It uses the info.json file provided by dropbox and relies on the first line of the .json file being something like this.

{"business": {"path": "C:\\Dropbox (me)"
gordon613
  • 2,770
  • 12
  • 52
  • 81
  • 1
    Very nice solution! I was thinking about also suggesting looking into the `LocalAppData` folder but decided not to since I don't have Dropbox and couldn't check where it dumps it's settings files. Regarding your solution: be careful when hard coding the file number as `#1`, if the calling code is already doing file IO with `#1` this will throw an error. Better would be to use the [`FreeFile`](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/freefile-function) function to find a free file number. – GWD Dec 01 '22 at 16:05