Macro: Search for a file name in a specified folder and link to excel cell

**jr13** · 06-11-2012, 03:59 PM

Hi!

I need a macro to read the contents (policy number) in a cell (A2), then search for a file name in a certain folder that starts with the contents of that cell then link that file to other cells (B2,C2,D2) and repeat the process down the column.

I have no idea how to search for a file name using excel, so any help would be amazing. Thanks!

Ex. Book1.xlsx

**jr13** · 06-13-2012, 05:10 PM

So I started writing something...

Dim FileFilter As Variant
Dim TabNames() As String
    Max = ActiveWorkbook.Sheets.Count
    ReDim TabNames(1 To Max)
    For csht = 1 To ActiveWorkbook.Sheets.Count
    TabNames(csht) = Sheets(csht).Name
    Next csht
Policy = Worksheets(TabNames(1)).Range("A4", Range("A4").End(xlDown)).Rows.Count
For a = 1 To Policy
'FindFile = SOMETHING???

If FindFile <> False Then FoundFileCount = FindFile.Count

    For b = 1 To FoundFileCount
    Cells(3 + a, b) = "TRUE"
    Next b

Next a

I'm using windows 7, and I *THINK* I just need something to actually search a specified PDF folder... I just have no idea how to do that. Seriously, any help would be AMAZING. Even if it was just a hint at a place to look for ideas. I'm so lost/stuck/frustrated. I really don't care about linking anything. Just finding the darn files.

I really don't get how vba works (kinda just learned as i went along for the past two weeks or so), and I kinda just made up that code with NO IDEA what it means

**tigeravatar** · 06-13-2012, 05:22 PM

jr13,

Are the Claim Form, Death Certificate, and Payment Proof files in different folders, or all in the same folder? As for how to search through the files in a folder, this should get you started:

Sub tgr()
    
    Const strFolderPath As String = "C:\Test"   'Change to correct folder location
    
    Dim FSO As Object
    Dim oFile As Object
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    For Each oFile In FSO.GetFolder(strFolderPath).Files
        MsgBox strFolderPath & "\" & oFile.Name
    Next oFile
    
End Sub

**jr13** · 06-13-2012, 05:33 PM

They are all in the same folder.. the path name is basically J:\COMPANYBLAH\Reinsurance\Claims\YEAR\CLIENT\Proofs\

Do you know how to edit that so it would look for a file name that started with a certain number and was in pdf format? and is it possible when entering the folder location to stop after the folder "Claims", so the search looks in all years and all client folders?

**tigeravatar** · 06-13-2012, 06:24 PM

jr13,

Give this a try. Run tgr, not GetFiles. GetFiles is called by tgr.

Sub tgr()
    
    Const strFolderPath As String = "J:\COMPANYBLAH\Reinsurance\Claims"
    
    Dim lCalc As Long
    Dim wsDest As Worksheet
    Dim wsFiles As Worksheet
    Dim arrFiles As Variant
    Dim rngPolicy As Range
    Dim PolicyCell As Range
    
    With Application
        lCalc = .Calculation
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
    
    Set wsDest = ActiveSheet
    Set rngPolicy = wsDest.Range("A2", wsDest.Cells(Rows.Count, "A").End(xlUp))
    
    For Each PolicyCell In rngPolicy.Cells
        Set wsFiles = Sheets.Add
        GetFiles strFolderPath, PolicyCell.Text & "*.txt", wsFiles
        arrFiles = Application.Transpose(wsFiles.UsedRange.Value)
        If IsArray(arrFiles) Then
            PolicyCell.Offset(, 1).Resize(, UBound(arrFiles)).Value = arrFiles
            Erase arrFiles
        Else
            PolicyCell.Offset(, 1).Value = arrFiles
        End If
        wsFiles.Delete
        
    Next PolicyCell
    
    With Application
        .Calculation = lCalc
        .EnableEvents = True
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
    
End Sub

Public Sub GetFiles(ByVal strFolderPath As String, ByVal strFileName As String, ByVal ws As Worksheet)
    
    Dim FSO As Object
    Dim oFile As Object
    Dim oFolder As Object
    
    Set FSO = CreateObject("Scripting.FileSystemObject").GetFolder(strFolderPath)
    
    For Each oFile In FSO.Files
        If LCase(oFile.Name) Like LCase(strFileName) Then
            ws.Cells(Rows.Count, "A").End(xlUp).Offset(1).Value = oFile.Path
        End If
    Next oFile
    For Each oFolder In FSO.SubFolders
        GetFiles oFolder.Path, strFileName, ws
    Next oFolder
    
End Sub