• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Listing files names and hyperlinks in a spreadsheet.

A

ankuun

New Member
Hi all,

I need a macro to look into a folder (i.e. C:\Documents) and list all the files (*.pdf) in a spreadsheet and hyperlink them.

Thank you very much!
 
I found this online and it looks like it would work but how do I hyperlink them?
Also, thanks for those links SirJB7

Code: 
Sub ListAllFiles()
    Dim fs As FileSearch, ws As Worksheet, i As Long
    Set fs = Application.FileSearch
    With fs
        .SearchSubFolders = False ' set to true if you want sub-folders included
        .FileType = msoFileTypeAllFiles 'can modify to just Excel files eg with msoFileTypeExcelWorkbooks
        .LookIn = "C:\" 'modify this to where you want to serach
        If .Execute > 0 Then
            Set ws = Worksheets.Add
            For i = 1 To .FoundFiles.Count
                ws.Cells(i, 1) = Mid$(.FoundFiles(i), InStrRev(.FoundFiles(i), "\") + 1)
            Next
        Else
            MsgBox "No files found"
        End If
    End With
End Sub
 
Hi, ankuun!
Try adding this line in the For...Next loop, just below the existing one:
ws.Cells(i, 2).Formula = "=HYPERLINK(" & .FoundFiles(i) & ")"
However, the FileSearch object has been hidden from normal view since Office 2007 version, being 2003 the last one which referenced it in help files. So test it carefully to see if it works as expected.
Regards!
 
SirJB7 is right. Application.FileSearch won't function in 2007+ versions.

Here's one way. See if it helps.
Code: 
Option Explicit
Public Sub ListAndHyperlink()
Const cstrPath As String = "E:\Knowledge Base\Books" 'Change this to suit
Dim varList As Variant
Dim i As Long
varList = Split(CreateObject("WScript.Shell").exec("cmd /c dir " & Chr(34) & cstrPath & Chr(34) & " /b").stdout.readall, vbCrLf)
For i = LBound(varList) To UBound(varList)
    If Len(varList(i)) > 0 Then
        Cells(i + 1, 1).Value = varList(i)
        Cells(i + 1, 2).Formula = "=HYPERLINK(" & Chr(34) & cstrPath & Application.PathSeparator & varList(i) & Chr(34) & ",""Click Here"")"
    End If
Next i
End Sub
 
Hi shrivallabha !​
​
Why don't simply use internal VBA Dir function ? Any special reason ?​
 
Hi Marc,

There's no special reason. Just exploring options.
Code: 
Option Explicit
Public Sub ListAndHyperlink2()
Const cstrPath As String = "E:\Knowledge Base\Books" 'Change this to suit
Dim i As Long
Dim strDir As String
strDir = Dir(cstrPath & Application.PathSeparator & "*.pdf")
i = 1
Do While strDir <> ""
    Cells(i, 1).Value = strDir
    Cells(i, 2).Formula = "=HYPERLINK(" & Chr(34) & cstrPath & Application.PathSeparator & strDir & Chr(34) & ",""Click Here"")"
    i = i + 1
    strDir = Dir
Loop
End Sub
 
SirJB7,

Spot on :D

In my first posting I have used dir command with "/b" switch which strips off the path which otherwise dir command returns.
 
​
I agree SirJB7, I prefer use internal VBA functions for simple need and external ones for special requirement …​
 
All,
I am kind of new to vba and I got lost during your discussion.
Would anyone explain whats going on little more simply?

Thanks!
 
shrivallabha:

First one (ListAndHyperlink) worked great and it is exactly what i needed. Actually, is it possible to hyperlink the file names instead of heaving a separate list that says click here?​

Thanks a million!​


Krishnakumar:
Thank you so much for the link. It is great to be able to do this with just a formula. I will definitely use this too.​
 
Thanks for the feedback. Here you go:
Code: 
Option Explicit
Public Sub ListAndHyperlink3()
Const cstrPath As String = "E:\Knowledge Base\Books" 'Change this to suit
Dim wsCurr As Worksheet
Dim varList As Variant
Dim i As Long
varList = Split(CreateObject("WScript.Shell").exec("cmd /c dir " & Chr(34) & cstrPath & Chr(34) & " /b").stdout.readall, vbCrLf)
Set wsCurr = ActiveSheet
For i = LBound(varList) To UBound(varList)
    If Len(varList(i)) > 0 Then
        Cells(i + 1, 1).Value = varList(i)
        wsCurr.Hyperlinks.Add Cells(i + 1, 1), cstrPath & Application.PathSeparator & varList(i), _
        , "Click on the Hyperlink to see document!", varList(i)
    End If
Next i
End Sub
 
Shrivallabha,

I love the recent code!!!
Your code worked great but I have one more thing to ask; excel list doesn't update when I delete one of the files in the folder
It only updates when a new file is added.
Do I need to make this a loop?
 
The code is more of an example code. I am posting a commented code which should help you work out any changes you want to make.
Code: 
Option Explicit
Public Sub ListAndHyperlink4()
Const cstrPath As String = "E:\Knowledge Base\Books" 'Change this to suit
Dim wsCurr As Worksheet
Dim varList As Variant
Dim i As Long
'\\ Use DOS to generate list of pdf files and build an array of names
varList = Split(CreateObject("WScript.Shell").exec("cmd /c dir " & Chr(34) & cstrPath & Chr(34) & " /b").stdout.readall, vbCrLf)
Set wsCurr = ActiveSheet
With wsCurr
    '\\ Clear old list and assign suitable heading to the list
    .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)).Delete xlUp
    .Cells(1, 1).Value = "File List"
    '\\ Loop through the array and create hyperlinks
    For i = LBound(varList) To UBound(varList)
        If Len(varList(i)) > 0 Then
            wsCurr.Hyperlinks.Add .Cells(i + 2, 1), cstrPath & Application.PathSeparator & varList(i), _
            , "Click on the Hyperlink to see document!", varList(i)
        End If
    Next i
End With
End Sub
 
Shrivallabha,

This code is excellent. Thank you and everyone in this thread so much for your help! You guys are awesome!
 
shrivallabha sir how can i include subfolders and can find a file with a input box. and i also need Location of file in C column
Sir please help me
 
mrmohit1a said:
shrivallabha sir how can i include subfolders and can find a file with a input box. and i also need Location of file in C column
Sir please help me
Click to expand...
Hi ,

Please start a new thread for your question.

Within your post , you can include a link to this earlier thread.

Narayan
 
You must log in or register to reply here.
Back
Top