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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
I'm not sure how  the  %APPDATA% part would be used in VBA
0
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

Dale FyeOwner, Developing Solutions LLCCommented:
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 FyeOwner, Developing Solutions LLCCommented:
"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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.