none
Do Not Allow Workbook to Open from Dropbox RRS feed

  • Question

  • I have a macro-enabled workbook in dropbox. I have my users copy it to their computer (preferably to their desktop). When I update the workbook I let them know to overwrite their copy with the one in dropbox. 

    I don't want users to open or use the workbook from within dropbox. I think the simple solution is to just code the Workbook Open event this this code:

    Private Sub Workbook_Open()
        If Len(Replace(ThisWorkbook.Path, "Dropbox", "")) < Len(ThisWorkbook.Path) Then
            MsgBox "This workbook will not function properly when run from a dropbox folder. Please copy the workbook to your computer before using it.", vbokay + vbInformation
            ThisWorkbook.Close
            Application.Quit
        End If
    End Sub

    The code checks the path of the workbook. If the path contains the word "Dropbox" (because the actual path could be different for each user, but every path to a dropbox folder will contain the word dropbox). If the statement is true then it msgboxes the user with a message and closes the workbook.

    A couple of questions? Is this how it's done? Is there a smoother way? Secondly, if I use this code if will not close the instance of Excel. I thought Application.Quit would work, but it doesn't. 

    tod

    Tuesday, November 1, 2016 6:30 PM

All replies

  • Re: close file opened from Dropbox

    Never done that, but I always have opinions...

      1.  Use Instr to check for "Dropbox" in the file path. (your novel method should work just fine)
      2.  Turn off "DisplayAlerts" (a message you don't know about might be displayed).
      3.  ThisWorkbook.Close needs "SaveChanges:=False" added to it.
      4.  All workbooks need to be closed before quitting the Application.
      5.  Why would you want to close the users Excel.Application?
      6.  Turn "DisplayAlerts" back on before closing or exiting the application.

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)


    Tuesday, November 1, 2016 8:01 PM
  •   5.  Why would you want to close the users Excel.Application?

    Because after my code runs the instance of Excel stays open.
    Friday, November 4, 2016 1:41 PM
  • I'm not sure, that you should do it in that way!
    Remember, that the user can decide for himself, the path to his local Dropbox folder - and, I myself does not have the word 'Dropbox' in the path to my Dropbox folder (I use a folder called 'Cloud').
    And, the user can also copy the file to a folder on the Desktop, that actually contains the word 'Dropbox', like a folder called 'Files from Dropbox'.

    The best way, is to actually check if the first part of the Workbook path is also the path to the actual Dropbox folder.

    Using the 'GetSpecialFolder' function, you can do it with something like this:

    If Left$(ThisWorkbook.Path, Len(GetSpecialFolder(vbDirDropbox))) = GetspecialFolder(vbDirDropbox) Then
        If Workbooks.Count > 1 Then
            ThisWorkbook.Close False
        Else
            Application.Quit
        End If
    End If

    The 'GetSpecialFolder' function can be found here:
    http://www.EXCELGAARD.dk/Lib/GetSpecialFolder/

    Thursday, December 15, 2016 8:52 PM