1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: hyperlink in excel to a specific file in a zip file

Discussion in 'Business Applications' started by higginsbl, Apr 19, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. higginsbl

    higginsbl Thread Starter

    Joined:
    Aug 17, 2001
    Messages:
    132
    Is it possible to create a hyperlink in an excel spreadsheet that goes to a SPECIFIC FILE within a zipped file? If so, how do you do it?

    Thanks!

    BL
     
  2. Sponsor

  3. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hi there,

    Not as far as I know it is not. The compressed folder is more like a file than a folder, or a folder with items not accessible by normal means. You can open the zip folder though. Here is an example...

    Code:
    Sub OpenZipFolder()
        Dim oShell As Object, strPath As String
        Set oShell = CreateObject("WScript.Shell")
        strPath = "C:\Users\Zack\Desktop\testdata.zip"
        If Dir(strPath) = "" Then
            MsgBox "Invalid path!", vbExclamation, "ERROR!"
        Else
            Shell "explorer.exe " & strPath, vbNormalFocus
        End If
    End Sub
    HTH
     
  4. higginsbl

    higginsbl Thread Starter

    Joined:
    Aug 17, 2001
    Messages:
    132
    Thank you for your reply, but I do not really understand what you are telling me.

    I can actually get the hyperlink to go directly to the ziped file (testexcel.zip) and it will open winzip with the window showing the list of files within that zipped file, but I cannot get it to open a SPECIFIC file within the zipped file via the hyperlink. (ie: I typed a hyperlink like this:
    c:\winzip\testexcel.zip\brkbrdg.rtf and it gets as far as opening the winzip window showing the list of files inside that zipped file, but it won't open that rtf file directly.)

    I tried to copy and paste your sample into the macro window substituting my path where you had references to files, but it kept giving me error messages that I didn't really understand how to fix.

    Please explain. Thanks again.

    BL
     
  5. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,911
    You can use VBA code to open the ZIP file and automatically extract all its contents to a local directory on your PC. You can then use additional VBA code to delete the unwanted files from the directory and open the single file that you are interested in. Will this work for you?

    Regards,
    Rollin
     
  6. higginsbl

    higginsbl Thread Starter

    Joined:
    Aug 17, 2001
    Messages:
    132
    Sort of. I understand WHAT you're saying. I just have no idea how to do it. Sorry.
     
  7. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    I'm saying I don't think you can directly access the files (through a hyperlink via VBA) in a Zip folder. You don't really need to know the how, just the why (generally we do the how :) ). Rollin should be able to hook you up with some sufficient code for the workaround. Seems like a pain to me though. I wonder why you would want to leave the file in a zip folder anyway?
     
  8. higginsbl

    higginsbl Thread Starter

    Joined:
    Aug 17, 2001
    Messages:
    132
    Here's the story -- I'm teaching an introduction to excel class and one of the students has a bunch of VERY large graphics files of blueprints for trains or planes or something for some type of collection. He wants to catalog the blue prints in excel and be able to link directly to the specific blue print from within excel so that it opens the correct picture when he needs to see it. He is obviously thinking BEYOND the introduction level, but I'm always happy to try to track down the answers to "weird" questions!

    Who is Rollin with a "work around"? What's the best thing for me to do at this point? Thanks!

    BL
     
  9. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Rollin, short for Rollin_Again, the gentleman who posted illustrious post #4 on this very thread. ;)

    A "work around" is a solution that is not what was intended, but 'works around' the original problem with a suitable or fairly similar solution at hand.

    The best thing to do? Tell your student to use Excel for what it was intended for. I can teach a monkey to fight fire, but I don't... ;)
     
  10. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,911
    Rollin = Rollin_Again (me)

    As far as I know there is no way to use VBA to extract a single file from a ZIP. As I stated before you can extract ALL the files in the zip to a directory and then open the single file he needs and delete the others. If there are lots of large files in the ZIP this may cause performance issues.

    Zack..........Why won't you train monkeys to fight fires? I hear they will work for bananas and don't demand health benefits :D

    Regards,
    Rollin
     
  11. higginsbl

    higginsbl Thread Starter

    Joined:
    Aug 17, 2001
    Messages:
    132
    Fair Enough! Thanks anyway.
     
  12. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Yes, but they always monkey-around. ;)
     
  13. illmode

    illmode

    Joined:
    Apr 22, 2008
    Messages:
    1
    Hi Guys, Good thread but I need to add another dimension to it which has nothing to do with Excel or Firefighting Monkeys!

    I am looking for a program that will let me send a presentation (format pref powerpoint) that has links to other documents i.e. doc's, pdf's xls's. The presentation must include a resource library where all these varoius documents can be accessible AND amendable....BUT..... heres the tricky bit... I want to send this presentation as one file.... but wait... it gets harder.... and after the recipient has watched the presentation and/or amended the linked documents or the presentation itself, I want it to automatically rezip everything up back into one file for re-sending to another colleague. So in effect, the presentation itself will transform and evolve as it gets sent on everytime. Are you following?

    I basically want to send a promotional presentation out to my colleagues which they can amend themselves. But I only want to send one file and once they have finished amending any linked files, i want them to be able to send one file too without having to manually zip all the folders.

    Am i going to need a programmer to write me a bespoke program?

    kind regards

    illmode
     
  14. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Short answer: I think so, yeah.

    Why not just have *one* copy of all the files that everyone works with??? You know what you need here? It was a new solution that I was just provided with, but it fits this so well, down to the last letter. One word: Groove. It is the best collaboration software I've ever seen. Leave the files on one workspace, everyone can come in, at the same time or different times, make changes, it's all there. Trust me, read into it, this would work perfectly with what you said.

    HTH
     
  15. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/705321

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice