PDF Hyperlinking Macro Excel 2010

Hello all,

Back in 2008, with the help of some of the members here, we were able to create a macro that would hyperlink all the pdf files in a specified folder and display them in descending order in the excel spreadsheet as their file names. I believe it was Excel 2003 that I was using at the time. I am now looking to use this macro for Excel 2010 and am running into Run-time error '438'.

Is anyone able to suggest a solution for this? If so, my company would greatly appreciate it as it was an incredibly useful tool back when it was working properly.

Thank you very much and I look forward to your responses/suggestions. I've attached the old code below.

Paul B

Sub CREATEPDFDIRECTORY()
Dim path As String
Dim f As Integer
path = "filepath"
f = 1
 
With Application.FileSearch
.LookIn = path
.FileType = msoFileTypeAllFiles
.Execute
For i = 1 To .FoundFiles.Count
If UCase(Right(.FoundFiles(i), 3)) = "PDF" Then
Range("A" & f).Value = .FoundFiles(i)
ActiveSheet.Hyperlinks.Add Anchor:=Range("A" & f), _
Address:=.FoundFiles(i), TextToDisplay:=Split(Dir(.FoundFiles(i)), ".")(0)

 

f = f + 1
End If
Next i
End With
 
End Sub

Anyone able to have a closer look and offer any ideas or a solution to this?

Originally Posted by abousetta

Here you go:

Option Explicit

Sub CREATEPDFDIRECTORY()

Dim f As Integer
Dim myFolder, myFile
Dim fso As Object, fPath As String
Dim path As String

    path = "Path"                    '<-- Replace with your own path

  ' Open dialog box
    Set fso = CreateObject("Scripting.FileSystemObject")
    fPath = path
    Set myFolder = fso.GetFolder(fPath).Files
  
    ' Open each file sequentially
      For Each myFile In myFolder
        If UCase(Right(myFile.Name, 4)) = ".PDF" Then
            f = f + 1
            Range("A" & f).Value = myFile.Name
            ActiveSheet.Hyperlinks.Add Anchor:=Range("A" & f), _
            Address:=path & myFile.Name, TextToDisplay:=fso.GetBaseName(myFile)
        End If
      Next myFile
  ' clean up
    myFile = vbNullString

End Sub

Enjoy

im not even a part of this thread but THANK YOU!!!! this has helped me to no end!

thanks again REPPED

this routine strips zeros off filenames. How do I preserve the leading zeros - mostly 3 of them.

YES! THANK YOU SO MUCH! Working perfectly as intended. You literally just shaved off weeks of hyperlinking because we scan and log EVERYTHING here. You made my day, abousetta.

Again, thank you so very much!