+ Reply to Thread
Results 1 to 4 of 4

vba to only allow file to be opened from folder in dropbox, not on local cpu

  1. #1
    Forum Contributor
    Join Date
    08-22-2009
    Location
    Manitoba
    MS-Off Ver
    Office 2010
    Posts
    524

    vba to only allow file to be opened from folder in dropbox, not on local cpu

    I have a spreadsheet which has a calculator and table of inventory that is updated when closed. I do not want others to make a copy on their local cpu, thus I have disabled "Save and SaveAs" functions. But, they can still drag and drop the file from Dropbox into a folder on their local computer. I want vba to stop this local copy from opening and a message saying they need to open the copy on Dropbox. I can get it to work on my computer, but if I put it in a Google Drive folder, it opens a copy (saved as a template for other reasons) on my local cpu, but the code does not seem to see this. I am wondering if there is a simpler/better way of doing this than what I have been trying? Code below works on my local cpu, but not if I rightclick and open from Googledrive. Thanks.

    Please Login or Register  to view this content.

  2. #2
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: vba to only allow file to be opened from folder in dropbox, not on local cpu

    Hi thecdnmole,

    The following technique may work for you:
    a. Add a file of any type (file can be empty and/or Hidden) in the same folder as the Excel file in Dropbox.
    b. Exit if the file does not exist.

    Sample code follows (tested and working):
    Please Login or Register  to view this content.
    It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:
    Please Login or Register  to view this content.
    This option requires all variables to be declared and will give a compiler error for undeclared variables.

    I hope this helps.

    Lewis

  3. #3
    Forum Contributor
    Join Date
    08-22-2009
    Location
    Manitoba
    MS-Off Ver
    Office 2010
    Posts
    524

    Re: vba to only allow file to be opened from folder in dropbox, not on local cpu

    Thanks Lewis for the reply! I gave it a try and it's not picking up the path and I think it's because the spreadsheet is a template. Works great as a regular macro spreadsheet, although the message is reversed to what I want, but that's a simple change. Darn template! Actually the more I try it the more I see it's not quite how I envisioned. I want it to allow being opened on Dropbox, but not anywhere else. The way the code is, it will flag it if I open the file at the same location it is in. But, it's something I can build on when I have the time, so I appreciate that!
    Last edited by thecdnmole; 10-11-2016 at 11:03 PM.

  4. #4
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: vba to only allow file to be opened from folder in dropbox, not on local cpu

    I want it to allow being opened on Dropbox, but not anywhere else.
    I think that is the way my logic works if you put the 'Canary' file in the DropBox folder.

    Lewis

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Check if file exists from same folder spreadsheet was opened
    By TimmerSuds in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-08-2016, 04:37 AM
  2. Replies: 2
    Last Post: 10-26-2015, 10:31 AM
  3. Macro with consolidation function not working when using the file in Dropbox folder
    By jmlachance in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-22-2015, 03:20 PM
  4. [SOLVED] Code for making new file in same folder as opened file exist.
    By Hydyne in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-22-2015, 05:41 AM
  5. Macro works in every folder except dropbox folder
    By luismarq in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-02-2015, 08:31 AM
  6. Replies: 1
    Last Post: 10-24-2014, 12:32 AM
  7. Excel VBA Code to upload a file to Dropbox using the Dropbox API
    By gb# in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-02-2014, 03:41 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1