Access VBA - Get the DropBox folder

Hi

In my Access VBA code I used the answer in the following question to try and get the DropBox folder on any machine. It didn't work. It just returned a blank
as it did not find the file Environ("AppData") & "\DropBox\host.db"

https://www.experts-exchange.com/questions/28292626/Get-path-to-dropbox-folder-on-different-computer.html

Is there a better way to find the DropBox folder?
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?
 
ste5anSenior DeveloperCommented:
By parsing JSON...

Option Compare Database
Option Explicit

Public Sub DropBoxFolders()

  Dim FileSystemObject As Scripting.FileSystemObject
  Dim ScriptControl As MSScriptControl.ScriptControl
    
  Dim JsObject As Object
  Dim Json As String
    
  Set FileSystemObject = New Scripting.FileSystemObject
  Json = FileSystemObject.OpenTextFile(DropBoxInfoJsonPath, ForReading).ReadAll
  Set FileSystemObject = Nothing
  
  Set ScriptControl = New MSScriptControl.scriptControl
  ScriptControl.Language = "JScript"
  Set JsObject = ScriptControl.Eval("(" + Json + ")")
  Set ScriptControl = Nothing
  
  On Local Error Resume Next
  
  Debug.Print "business: ", VBA.CallByName(VBA.CallByName(JsObject, "business", VbGet), "path", VbGet)
  Debug.Print "personal: ", VBA.CallByName(VBA.CallByName(JsObject, "personal", VbGet), "path", VbGet)
    
End Sub

Public Function DropBoxInfoJsonPath() As String

  Const PATH_APPDATA As String = "APPDATA"
  Const PATH_LOCALAPPDATA As String = "LOCALAPPDATA"
  Const PATH_STATIC As String = "\Dropbox\info.json"

  On Local Error GoTo LocalError
  
  DropBoxInfoJsonPath = Environ(PATH_APPDATA) & PATH_STATIC
  If Len(Dir(DropBoxInfoJsonPath & "")) > 0 Then
    Exit Function
  End If

  DropBoxInfoJsonPath = Environ(PATH_LOCALAPPDATA) & PATH_STATIC
  If Len(Dir(DropBoxInfoJsonPath & "")) > 0 Then
    Exit Function
  End If
    
LocalError:
  DropBoxInfoJsonPath = ""

End Function

Open in new window

0
 
ste5anSenior DeveloperCommented:
You need to look for the DropBox config files.
The JSON file is named info.json and can be found at one of these two paths:

- %APPDATA%\Dropbox\info.json
- %LOCALAPPDATA%\Dropbox\info.json
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Hi. Thanks. What VBA code would I use?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
I'm not sure how  the  %APPDATA% part would be used in VBA
0
 
Dale FyeCommented:
You should be able to call the API found on this page to get the path to the users \AppData\ folder.

Something like:

if dir(fGetSpecialFolderLocation(CSIDL_APPDATA) & "\Dropbox\info.json") <> "" then
    msgbox "found file at: " & fGetSpecialFolderLocation(CSIDL_APPDATA) & "\Dropbox\info.json"
elseif dir(fGetSpecialFolderLocation(CSIDL_LOCAL_APPDATA) & "\Dropbox\info.json") <> "" then
    msgbox "found file at: " & fGetSpecialFolderLocation(CSIDL_LOCAL_APPDATA) & "\Dropbox\info.json"
Else
    msgbox "File not found"
end if
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
I get the error shown in the attachment. Is there more code that function?
0
 
ste5anSenior DeveloperCommented:
%APPDATA% and %LOCALAPPDATA% are  environment variables. Thus use Environ("APPDATA") and Environ("LOCALAPPDATA"). Thus a simple Dir() on these files and then read them. Or use the File System Object.

E.g.
Option Compare Database
Option Explicit

Public Sub Test()

  Dim FileSystemObject As Scripting.FileSystemObject
    
  Dim Json As String
  
  Set FileSystemObject = New Scripting.FileSystemObject
  Json = FileSystemObject.OpenTextFile(DropBoxInfoJsonPath, ForReading).ReadAll
  Debug.Print Json
  Set FileSystemObject = Nothing

End Sub

Public Function DropBoxInfoJsonPath() As String

  Const PATH_APPDATA As String = "APPDATA"
  Const PATH_LOCALAPPDATA As String = "LOCALAPPDATA"
  Const PATH_STATIC As String = "\Dropbox\info.json"

  On Local Error GoTo LocalError
  
  DropBoxInfoJsonPath = Environ(PATH_APPDATA) & PATH_STATIC
  If Len(Dir(DropBoxInfoJsonPath & "")) > 0 Then
    Exit Function
  End If

  DropBoxInfoJsonPath = Environ(PATH_LOCALAPPDATA) & PATH_STATIC
  If Len(Dir(DropBoxInfoJsonPath & "")) > 0 Then
    Exit Function
  End If
    
LocalError:
  DropBoxInfoJsonPath = ""

End Function

Open in new window


Reference Microsoft.Scripting or use late binding.
0
 
Dale FyeCommented:
"is there more code"

Yes, you have to go to the hyperlink and copy the function on that page.  Copy everything on the page which has a blue background.

Then create a new code module in your application, not associated with a form, and paste that code into that new code module.
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks very much Ste5an. I got the following result. How do I just get the file path portion o that?

1
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks. What VBE Reference should I use for ScriptControl
0
 
ste5anSenior DeveloperCommented:
Use

Capture.PNG
or switch to late binding:

Option Compare Database
Option Explicit

Public Sub DropBoxFolders()

  Dim FileSystemObject As Object 'Scripting.FileSystemObject
  Dim ScriptControl As Object 'MSScriptControl.ScriptControl
    
  Dim JsObject As Object
  Dim Json As String
    
  Set FileSystemObject = CreateObject("Scripting.FileSystemObject") 'New Scripting.FileSystemObject
  Json = FileSystemObject.OpenTextFile(DropBoxInfoJsonPath, ForReading).ReadAll
  Set FileSystemObject = Nothing
  
  Set ScriptControl = CreateObject("MSScriptControl.ScriptControl") 'New MSScriptControl.ScriptControl
  ScriptControl.Language = "JScript"
  Set JsObject = ScriptControl.Eval("(" + Json + ")")
  Set ScriptControl = Nothing
  
  On Local Error Resume Next
  
  Debug.Print "business: ", VBA.CallByName(VBA.CallByName(JsObject, "business", VbGet), "path", VbGet)
  Debug.Print "personal: ", VBA.CallByName(VBA.CallByName(JsObject, "personal", VbGet), "path", VbGet)
    
End Sub

Open in new window

0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks very much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.