Create an Excel lookup table from pdf files

[DrivelineRob]

Is it possible to create an Excel lookup table from pdf files.
I have thousands of (8815) pdf technical drawings in a "PDF_TubeType" attached file and want to search this file and retrieve one specific drawing in Excel .

Can I make a table in Excel from all of the 8815 pdf items and lookup one specific technical drawing Example: 48 S2 F32 F43 W2 ?
Kind of like a reverse Hyperlink!

The Excel table would have 8815 rows of these sample numbers below and look something like this:
48 S1 F32 F43 W2
48 S2 F32 F43 W2
48 S3 F32 F43 W2
48 S4 F32 F43 W2
etc.....

Is it possible to VLookup one of these 8815 items?
Or, is there a better way to pull one of these pdf items in Excel?

 

[ranman256]

paste the code below into a module.
alter the constant kDIR to your folder. DONT USE DRIVE LETTERS, USE UNC path: \\server\folder1\folder2\file.txt
then run: CreateHypers
It will create a hyperlink on each cell if the document exists.
User can then ctrl-F to find the doc name, then click it to open it.

Public Const kDIR = "\\robsurina\elbe selector 2021\pdf_tubeType\"

Public Sub CreateHypers()
Dim vFile, vName
While ActiveCell.Value <> ""
   vName = ActiveCell.Value
   vFile = kDIR & vName & ".pdf"
 
   If FileExists(vFile) Then
      ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=vFile, TextToDisplay:=vName
   End If
 
   ActiveCell.Offset(1, 0).Select  'next row
Wend
End Sub


Private Function FileExists(ByVal pvFile) As Boolean
Dim fso
On Error Resume Next
Set fso = CreateObject("Scripting.FileSystemObject")
FileExists = fso.FileExists(pvFile)
Set fso = Nothing
End Function

 

[ranman256]

if the document names are not yet in the excel sheet,
use this to scan the folder to load them in & hyperlink them:

Public Sub ScanFilesIn1Folder()
Dim FileSystem As Object
Dim Folder As Object
Dim oFile As Object

Set FileSystem = CreateObject("Scripting.FileSystemObject")
Set Folder = FileSystem.GetFolder(kDIR)
Range("A2").Select
For Each oFile In Folder.Files
     ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=oFile, TextToDisplay:=oFile.Name
 
     ActiveCell.Offset(1, 0).Select  'next row
skip1:
Next
Set oFile = Nothing
Set Folder = Nothing
Set FileSystem = Nothing
End Sub

 

[JEC]

When the files already exist and are starting in A1

Sub jec()
 Dim ar As Variant, i As Long
 ar = Cells(1, 1).CurrentRegion
 
 For i = 1 To UBound(ar)
   If CreateObject("scripting.filesystemobject").FileExists(ar(i, 1)) Then ActiveSheet.Hyperlinks.Add Cells(i, 1), ar(i, 1)
 Next
End Sub