How do you find what folder a hyperlink is residing in?

[malik641]

Hey guys

What I'm trying to do is create a list of pdf files using certain text with wildcards...but I recently realized that Application.FileSearch only looks for office type filtypes so I'm trying to create some sort of method to find pdf files and store them into an array, and the method of finding them that I'm using is by using the address of a hyperlink....but I'm trying to get only the folder it's in...because filesearch doesn't seem to be working by the hyperlink address because it references the file exactly....

Any methods?

TIA

[acw]

Hi

You could use the filesearch along these lines

bbb
Set fs = Application.FileSearch
With fs
    .LookIn = "C:\temp"
    .Filename = "*.pdf"
    If .Execute > 0 Then
        For Each fn In .FoundFiles
            MsgBox fn
        Next fn
    End If
End With
End Sub

Tony

[malik641]

Hey Tony

Maybe I wasn't clear on this. The .LookIn portion of the code is where I need to know how to get the folder that a specific hyperlink is pointing to. The .LookIn won't be the same everytime I run the code, it will be in the same place the hyperlink's file's folder is in. That's what I'm looking to find.

Thanks for the response, though

[acw]

Joseph

Assuming that you have the hyperlink in a cell, and you have selected this cell, then the below snip will give you the path of the hyperlink.

Dim HoldADD1 As String, HoldADD2 As String, LookIN As String
 HoldADD1 = ActiveCell.Hyperlinks(1).Address
HoldADD2 = WorksheetFunction.Substitute(HoldADD1, "\", "|", Len(HoldADD1) - Len(WorksheetFunction.Substitute(HoldADD1, "\", "")))
 LookIN = Left(HoldADD2, InStr(1, HoldADD2, "|") - 1)
 MsgBox LookIN

HTH

Tony

[malik641]

Joseph

Assuming that you have the hyperlink in a cell, and you have selected this cell, then the below snip will give you the path of the hyperlink.

Dim HoldADD1 As String, HoldADD2 As String, LookIN As String
 HoldADD1 = ActiveCell.Hyperlinks(1).Address
HoldADD2 = WorksheetFunction.Substitute(HoldADD1, "\", "|", Len(HoldADD1) - Len(WorksheetFunction.Substitute(HoldADD1, "\", "")))
 LookIN = Left(HoldADD2, InStr(1, HoldADD2, "|") - 1)
 MsgBox LookIN

HTH

Tony

Nice
Thanks Tony, I'll let you know how this goes (when I test it at my job). It worked for little testing and it should for my needs at my job.

I'm surprised there's no property for this in the hyperlink object. Oh well, this works and is good enough


Again thanks and I'll mark it solved when I'm sure it works on my project. In the meantime if anyone knows a property or method different than this feel free to post.

[malik641]

Tony,

Problem solved Thanks a bunch.

But I've run into another, maybe bigger, problem. I forgot that the .FileSearch object only searches for MS documents. I'm looking to make an array of pdf files and filter out the one's I want then place them into a new message through outlook....and I had everything done until I realized it wasn't finding the pdf files because it's not meant to look for them

I'll mark this one solved and create a new thread about my next problem.

Again thanks.