need workaround? RRS feed

  • General discussion

  • Hi.

    I'm looking to accomplish something specific which is now appearing more involved than I'd initially considered.

    I want to create a hyper link to a pdf file, in excel. But when I went looking for the files which are pdf, I found that they were all in:

    1- different directories on a single drive-- 4 directories in from the drive. 1 main, 3 subdirectories.... DriveLtr:/DirA/A/A1/A1a/

    and then there are multiple primary directories....





    each with its respective subdirectories, similar to the first.

    2- within a zipped file ....... DriveLtr:/DirA/A/A1/A1a/

    So, it appears that my hyperlink idea is either way over the top, or too involved. For me anyway......

    I've read an article which says there are work arounds for this,

    I'd still like to have the hyperlink, but have found that it'll require opening the zipped file to access it. 

    How would I accomplish this? 

    Would I need to have a special command (link, a UDF maybe?) to a macro, which would open the zip file, and then allow me to open the pdf? 

    I won't need the other files which are part of the zip file, so I guess they'd need to be deleted, once extracted, while retaining the zip file within its parent directory. 

    Thank you, in advance, for your responses. 



    • Changed type SteveDB1 Wednesday, August 26, 2015 11:21 PM solved a portion of the issue which brought me here for this item.
    Monday, August 24, 2015 7:42 PM

All replies

  • Ok, it's been a few days, and it's clear I've made this too comprehensive. Please forgive me. 

    So, please allow me to break it down into parts.

    First, I've found Ron Debruin's zip macro which allows for file extraction from a zip file. It does not however work with extracting anything except text files. I did change the file format to pdf, but never got a positive result.

    I've tried both examples 1 and 2. Example 2 does more of what I'm wanting-- extract a single file.

    From Ron's code:

    For Each fileNameInZip In oApp.Namespace(Fname).items
                If LCase(fileNameInZip) Like LCase("*.txt") Then
                    oApp.Namespace(FileNameFolder).CopyHere _
                End If
            MsgBox "You find the files here: " & FileNameFolder
            On Error Resume Next
            Set FSO = CreateObject("scripting.filesystemobject")
            FSO.deletefolder Environ("Temp") & "\Temporary Directory*", True

    Within the line:

     If LCase(fileNameInZip) Like LCase("*.txt")

    I changed the *.txt to *.pdf. I did not obtain the results that I'd wanted. 

    What would I need to extract a single pdf, or rtf file from the zip file? 


    Wednesday, August 26, 2015 6:23 PM
  • >>>I changed the *.txt to *.pdf. I did not obtain the results that I'd wanted.

    This forum is for discussing about Excel for Developers, because your issue is about VBA that is outside the scope of this forum.
    So I will suggest you confirming this issue on the VBA forum.

    But according to your description, I have used Ron Debruin's zip macro which allows for file extraction from a zip file, and I can obtain the *.pdf files.

    You need pay attention to below code:

    'Root folder for the new folder.
    'You can also use DefPath = "C:\Users\Ron\test\"
    DefPath = Application.DefaultFilePath
    If Right(DefPath, 1) <> "\" Then
       DefPath = DefPath & "\"
    End If
    'Create the folder name
    strDate = Format(Now, " dd-mm-yy h-mm-ss")
    FileNameFolder = DefPath & "MyUnzipFolder " & strDate & "\"
    'Make the normal folder in DefPath
    MkDir FileNameFolder

    These codes will create a new folder then copy *.pdf file into it when you extracted zip file, so you could find *.pdf file in the location of "FileNameFolder".

    Otherwise you could refer to below codes that change hyperlink's address to open the pdf.

    Selection.Hyperlinks(1).Address="change your addresss"    
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True

    For more information, click here to refer about Hyperlink Object (Excel)

    Thursday, August 27, 2015 6:02 AM
  • wow.

    That's a new forum.

    Thank you.

    It's been a few years since I'd been here last, posting on a regular basis. 

    I'll post there on my vba issues from now on. 

    Thursday, August 27, 2015 4:43 PM