VBA to pull in and generate hyperlink for files with the letter *R* in the first 4 letters of the file name.

[Bvendett4]

Hello,
I am currently using the below vba, this generates a hyperlink into a database once the item is saved into the specific file path.

Sub Example1()

Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer

Sheet2.Activate

'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")

'Get the folder object
Set objFolder = objFSO.GetFolder("G:\Shared\Health & Safety\5. Risk Assessments\Risk Assessments New")
i = 1

'loops through each file in the directory
For Each objFile In objFolder.Files
Sub DoFolder(Folder)
    Dim SubFolder
    For Each SubFolder In Folder.SubFolders
        DoFolder SubFolder
    Next
    Dim File
    For Each File In Folder.Files
    'create hyperlink in appropriate cell
    ActiveSheet.Hyperlinks.Add Anchor:=ActiveSheet.Cells(i + 1, 2), Address:=objFile.path, TextToDisplay:=objFile.Name
    'add date created to the right
    ActiveSheet.Cells(i + 1, 3).Value = objFile.DateCreated
    i = i + 1
Next objFile

End Sub

This is placed over 3 individual worksheets and pulls in from 3 different file paths
i.e.
Risk Assessments New - is replaced with Task Assessments New on the 2nd worksheet and replaced with PUWER Assessmnet New on the 3rd worksheet.

The files are saved as below
101R A Assessment - Risk Assessment
101T A Assessment - Task Assessment
101P A Assessment - PUWER Assessment

I have now been asked if if we can store all the files in the same location, i.e. in a new Equipment Assessment Folder.

If the vba can read the letter R, T or P as the 4th digit, then hopefully I can carry on using this database and it will pull in the correct files based on these digits.

I hope this explains what I require.

Many Thanks