Using VBA with Dropbox API

I finally figured out how to use VBA with Dropbox’s APIs. This was probably at least 3 days’ worth of trying to figure it out, with about 3 weeks interspersed between the first 2 days of failure and the last day of eventual success. It all began when I needed a list of users in Dropbox so I could make an email distribution list for an email warning. I started out by manually copying and pasting the shared user information from the Dropbox on the website. Then, once copied and pasted, I found I needed to pull out only the email addresses and throw out the rest, to create a list of email addresses. Two macros were created: a macro to get rid of any pictures that came along and another macro to capture only the email addresses and to place them in a list for use in an email.

That worked beautifully except the last time I used the programs, I found I still had to manually copy the data from the Dropbox website to the Excel file and I found that tedious, especially when I needed to get an email out fast. Thus, this desire to programmatically copy data from Dropbox to Excel led me to APIs.

I have to do this a couple of times with different websites before I begin to understand how APIs supposed to work but my current understanding is that the APIs act like a window into the website’s data for use. The website has to give you access and it probably gives you access to only certain things. The API is that window of access. On your end, you create the program that calls out to the API and gain access to do things that the website has allowed you to do.

Starting Point: Creating your APP

To start, you go to the website Dropbox and log in if you have to. If you access the website Dropbox using the icon usually found in the lower right hand side of your screen, you probably won’t need to login. When you are logged in, you are probably in www.Dropbox.com/home or something similar. Instead of home, type in developers to get to the developers’ area. At this early stage, there are two things you will use the most: the “create your app” area and the API Explorer. “Create your app” is kind of confusing because when you go through the instructions, I don’t think you are really creating an app at this point, I think you are creating a shell that will give your program permission to call on Dropbox’s API.

Next, there are three steps to “creating your app”: choosing your API, choosing type of access, and naming your app.

Once you have named it, I believe Dropbox will lead you to a page where showing information about your shell app or API (I’m not sure if it is a shell app or a shell API that has been created – I’m confused about this point). Here, there are three things I think you should take note of in case you need to use them in the future: App key, App secret, and the access token. For the access token, you will need to hit the “Generate” button and write down the access token. For now, I know where we need to use the access token; the other two, I haven’t come across of the need for them but it advisable to take note of them, just in case.

Next, Using the API Explorer to See What You Need for Coding

The API Explorer is a great tool for determining what you are going to need in your code. Off to the left is a list of actions you might want to take with your program, so go down the list to see what you can do.

If you click on show code, you will find a set of codes for what you want to do. There are 4 program choices as to what type of code to use, 2 of which seems most useful: Curl or html. These codes are useful for writing your VBA. They tell you what you need and how to code.

Now it was here that I realized that I needed the “shared_folder_id” which appears to be accessed only programmatically, so I think I used get_metadata, but you also can use get_folder_metadata or list_folders. In using these api programs, you will need to get the “path” and that will involve some experimentation to get the format you need. As best as I can describe it, it will be a path without “Dropbox” at the front and will start with “/”.

Once you set up your code in the API explorer, you can “submit call” and a response will come back showing you the results. This is how you test whether the code is going to give you what you need. When you have found what you wanted, then you are ready to begin the VBA code using the code offered by the API explorer.

VBA Code

Finally, the last piece, the code itself that will pull the information from Dropbox.

The code below is just the basics you need to get some kind of response. Compare the HTTP request code against the VBA: http’s POST line dovetails with VBA’s xmlhttp.Open POST line, the http Host line is folded within the VBA’s myurl line, the http Authorization line is the VBA’s xmlhttp.SetRequestHeader “Authorization”, http’s Content-Type becomes VBA’s xmlhttp.SetRequestHeader “Content-Type” line, and the shared folder id is set by VBA’s variable argumentString. The xmlhttp.ResponseText gives you the response from Dropbox.

Remember, this code is just minimal – all it does it pull in data for just one folder – but it’s a start. I now have the code for accessing the Dropbox. The rest, like pulling in more than one folders and sorting and picking the details I actually want, will be further developed but at least I have the key!

The VBA Code

Dim xmlhttp As Object
Dim myurl As String

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.EnableEvents = False

Set xmlhttp = CreateObject(“WinHttp.WinHttpRequest.5.1”)
myurl = “https://api.dropboxapi.com/2/sharing/list_folder_members”
    
argumentString = “{ ” & Chr(34) & “shared_folder_id” & Chr(34) & “: ” & Chr(34) & “775793860” & Chr(34) & ” }”
    
xmlhttp.Open “POST”, myurl, False
xmlhttp.SetRequestHeader “Authorization”, “Bearer your-access-token”
xmlhttp.SetRequestHeader “Content-Type”, “application/json”
    
xmlhttp.Send argumentString
    
MsgBox (xmlhttp.ResponseText)

Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.EnableEvents = True
    
MsgBox (“Program done!”)