+ Reply to Thread
Results 1 to 23 of 23

Detect if workbook is opened from within a zip file?

  1. #1
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Detect if workbook is opened from within a zip file?

    Is there any way to detect whether a workbook has been opened from within a zip file?

    I have a XLSM workbook which is distributed inside a zip. I need the end users to extract the zip file before starting a calculation in the workbook. However I currently have no way of enforcing this. If I had a function that could return whether the file is inside a zip (or not) then I could display a MsgBox and force the workbook to close.

    I have two ideas on how this might be done:

    1. 'Read Only status'? I don't know much about this. Is an Excel workbook treated as read-only if opened from a Zip file?

    2. Workbook file path? Check to see if the workbook path = a temporary folder path on Windows? (The end users would all be using Windows).

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Detect if workbook is opened from within a zip file?

    Hello mc84excel,

    When a program launches another program, it does not keep a record of programs it launched. Running a Excel file that was recently downloaded might be in your Temp folder. A workbook can be set as read only to prevent the user from making and saving changes to the workbook. So, neither method is a reliable way to determine if the Excel file was launched from a zip program or an Explorer window. It would help to know more detail about what you want to accomplish.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Detect if workbook is opened from within a zip file?

    The XLSM is a workbook designed to run a certain calculation. It is mainly distributed by email inside a zip. I wish to prevent end users from opening the workbook straight from the zip attachment and starting a calculation. I want to encourage them to save the attachment, extract the zip and then start a new calculation.

    Simply telling them to do this in the body of the email has little effect (habits are hard to break). Which is why I am hoping someone has a solution to this.

    One solution that would work (but is too restrictive for what I am after) - I could insist the workbook be saved & used within a set folder path (e.g. C:/TOOLFOLDERPATHNAME/) and have the workbook close if the WB file path does not equal the hardcoded path.

    I am aware that "When a program launches another program, it does not keep a record of programs it launched", however would it be possible to check part of the workbook path of the open workbook and see if it matches one of the Windows temp folders? (If a file is run from a zipped email attachment, I understand it extracts it to a temporary folder inside the C drive).
    Last edited by mc84excel; 12-17-2012 at 07:14 PM.

  4. #4
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Detect if workbook is opened from within a zip file?

    Is it possible to detect whether the ActiveWorkbook.Path contains a certain text string? (e.g. "AppData\Local\")

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Detect if workbook is opened from within a zip file?

    Please Login or Register  to view this content.
    Entia non sunt multiplicanda sine necessitate

  6. #6
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Detect if workbook is opened from within a zip file?

    Thank you shg.

    I now have some code which appears to work on Windows 7. It will display a message and close the workbook if it is opened from inside a zip.

    Please Login or Register  to view this content.
    I don't have access to a Windows XP right now however once I do, I just need to determine where Windows XP stores the temporary files caused by opening an item from within a zip (or any other compressed archive for that matter) and adding part of that path to the above code.
    Last edited by mc84excel; 01-06-2013 at 10:21 PM. Reason: Improving the code posted. (Neater close system and also should be Auto_Open not Workbook_Open)

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Detect if workbook is opened from within a zip file?

    (Neater close system and also should be Auto_Open not Workbook_Open)
    Actually it should be Workbook_Open. Auto_Open is long since deprecated.

  8. #8
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Detect if workbook is opened from within a zip file?

    Quote Originally Posted by shg View Post
    Actually it should be Workbook_Open. Auto_Open is long since deprecated.
    Workbook_Open is a superior alternative to Auto_Open, I agree. I'm not sure now why I changed the code to Auto_Open?

    I will correct this when I post the next update of the code (once I get round to including a Windows XP temp path).

  9. #9
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Detect if workbook is opened from within a zip file?

    Why not just

    Please Login or Register  to view this content.

  10. #10
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Detect if workbook is opened from within a zip file?

    Quote Originally Posted by shg View Post
    Why not just

    Please Login or Register  to view this content.
    True. I agree that this is the most logical solution to the scenario posted in my initial question.

    However I would like to expand the scope of this code to prevent opening within any archive file types (.rar & .7z for example), not just .zip files. I think it would be much easier to exclude the temporary file paths for Windows rather than trying to exclude all known archive types.

  11. #11
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Detect if workbook is opened from within a zip file?

    I am not displeased with the code so far. I have tested it successfully on Windows 7 and on a Windows Server. (I assume that the temporary file paths will be the same for Windows Vista & Windows 8 due to the similar nature of the Windows OS from Vista onwards).

    I just need to work out (& include) the temporary file path for Windows XP before marking this thread as solved. (I am assuming that the temporary file paths for earlier Windows OS would be the same as XP).

    If anybody reading this knows the temporary file path for Mac, please post it on this thread. It would be nice to include it for sake of completeness.
    Last edited by mc84excel; 01-08-2013 at 09:19 PM. Reason: Clarify that file paths referred to (in third sentence) are temporary file paths.

  12. #12
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Detect if workbook is opened from within a zip file?

    People could have a zip file in any directory.

  13. #13
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Detect if workbook is opened from within a zip file?

    Quote Originally Posted by shg View Post
    People could have a zip file in any directory.
    Exactly. Which is why I am excluding the temporary file paths. That way it will not matter where the zip/rar/7z etc is saved.

    To see this in action, try this:
    1. Create a new workbook. Copy into it the code I have posted. Save & close the workbook (saving it as a .XLSM). Compress the workbook into an archive file (e.g. .zip).
    2. Open the workbook from inside the archive without extracting it. Enable macros on the workbook and the workbook should display a msgbox and close.
    3. Now copy the archive to another directory and repeat the last line. The same result should occur. This means that the code is not dependent on the directory location of the zip.

  14. #14
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Detect if workbook is opened from within a zip file?

    Ah, gotcha.

  15. #15
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Detect if workbook is opened from within a zip file?

    I have corrected the code to include Windows XP. See code below from ThisWorkbook.

    Please Login or Register  to view this content.
    I have tested the code and it appears to work. It DOES rely on the user enabling macros (In my situation that is not a problem as the tool requires macros enabled to work); however if that is a problem then I recommend using this along with code to force users to enable macros.

    I am marking this thread as solved.

    P.S. If anyone reading this uses Excel on Mac then I would be interested if you could please me with provide the temp file path for Mac so that it can be included in the code.

  16. #16
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Detect if workbook is opened from within a zip file?

    BTW if you uncomment the "If ExcelVersionPre2007 = True" line in the Workbook_Open sub then you can prevent end users from opening the file in Excel 2003 or earlier versions.
    *******************************************************

    HELP WANTED! (Links to Forum threads)
    Trying to create reusable code for Custom Events at Workbook (not Application) level

    *******************************************************

  17. #17
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Detect if workbook is opened from within a zip file?

    I've found an issue with this code when used in the real world. If you have more code lines in Workbook_Open and they are placed under the "If" checks, VBA will still try to process these lines even though the code is trying to close the workbook.

    So. Alter Workbook_Open to:
    Please Login or Register  to view this content.
    Also this is a matter of preference but I prefer to have all the functions & subs in this code (other than Workbook_Open) in another module. (I prefer to avoid cluttering up the ThisWorkbook - and using it for Event macros only).

  18. #18
    Registered User
    Join Date
    02-23-2012
    Location
    Almaty, Kazakhstan
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Detect if workbook is opened from within a zip file?

    Thanks a lot, this is exactly what I searched for.

  19. #19
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Detect if workbook is opened from within a zip file?

    No worries. Please to see that I am not the only one who wanted to achieve this

  20. #20
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Detect if workbook is opened from within a zip file?

    I was thinking of improving this code some day.

    Instead of relying on part of the directory path as the detection as to whether the file has been opened within a zip - maybe one of the Environ values could be used to detect the users temp path instead. This would have the advantage of making the code more universal (e.g. allow for future version of Windows, possibly allow code to work on Macs?)

  21. #21
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Detect if workbook is opened from within a zip file?

    Hello mc84excel,

    Please post your questions in new threads instead of your signature.

  22. #22
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Detect if workbook is opened from within a zip file?

    Fixed. Thanks.

  23. #23
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Detect if workbook is opened from within a zip file?

    See attached. I think this will be the final version. It should be compatible with any OS.

    UPDATE: Ignore this post & Zip. Go by the code in post #15 (Realised that some long user names are shortened in the Environ("Temp") path).
    Last edited by mc84excel; 06-27-2013 at 03:52 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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