VBA Code to copy a file on shared Dropbox and paste in another area on a shared Dropbox

Subtotalatom

Hello, 

i have a spreadsheet that allows user to click a button and add information about the member. this will then add the details to a new row under the subheadings provide. 

However the main thing the macros does is it copies a folder template on a shared dropbox, renames it and puts in the designated folder. 

Private Sub btnSubmit_Click()
    Dim NxtRow As Long

    If Trim(tbSchoolName) <> "" Then
        With ThisWorkbook.Sheets("Members")
            NxtRow = .Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
            .Cells(NxtRow, 1) = tbSchoolName
            .Cells(NxtRow, 2) = tbMembershipPackage
            .Cells(NxtRow, 3) = tbArea
            .Cells(NxtRow, 4) = tbContacts
            .Cells(NxtRow, 5) = tbPosition
            .Cells(NxtRow, 6) = tbLandline
            .Cells(NxtRow, 7) = tbMobile
            .Cells(NxtRow, 8) = tbEmail

        End With

        Dim FSO As Object
        Dim FromPath As String
        Dim ToPath As String

        FromPath = "C:\Users\kurti\Dropbox\3. Company X\Name\Folder Template"
        ToPath = "C:\Users\kurti\Dropbox\3. Company x\Contacts\2. Members\" & tbSchoolName

        If Right(FromPath, 1) = "\" Then
            FromPath = Left(FromPath, Len(FromPath) - 1)
        End If

        If Right(ToPath, 1) = "\" Then
            ToPath = Left(ToPath, Len(ToPath) - 1)
        End If

        Set FSO = CreateObject("Scripting.FileSystemObject")

        If FSO.FolderExists(FromPath) = False Then
            MsgBox FromPath & " doesn't exist"
            Exit Sub
        End If

        FSO.CopyFolder Source:=FromPath, Destination:=ToPath

        MsgBox "All done.", , ""
    Else
        MsgBox "You must enter a Supplier Name.", , ""
        tbSchoolName.SetFocus
    End If
End Sub

as you see by the code. it is going through my c drive and to access the dropbox. so another user can not execute the macro as the file path mine and no relevant to theres. 

is there a way to make so. the excel spreadsheet can access the files on the dropbox for everyuser.  

Здравко

Hi @Subtotalatom,

There are different ways. If you plan use your code only on default location for Dropbox folder, then just replace initial part "C:\Users\kurti" with result from Environ("HOMEPATH"). This is the simplest way, but wouldn't work in case of moved Dropbox folder or in case of combined Business and Private account (the folder gets renamed). To work in all cases (doesn't matter place and name of Dropbox folder), you have to parse JSON file describing exact folder(s) place. Follow the notes in the according topic to do this.

Hope this gives right direction.