CODEWITHSUNDEEP
excelvba
wailing_stone
wailing_stone

Reputation: 173

Populate cell with a filename based on path excerpt in previous cell

I'm working with an excel spreadsheet that has a list of titles in column A, and a list of filenames in column D. The filenames are created with a specific naming convention that's based on the title in column A-- for example, if the title in A1 is "Taco_123," then the filename in D1 will contain the exact phrase "Taco_123".

At the moment, the filenames must be entered manually into D1 after the files have been created. But what I'd like is a VBA script that would do that automatically. My idea is that for each row it would read the title in column A, search for the file containing that exact phrase in the directory where the files are, then copy the file name (minus the extension) into column D.

Is such a thing even possible? I've tried searching for a solution online as I know zilch about VBA, but I haven't had much luck; I can't even find someone else with the same problem. Any help would be appreciated.

Edit: I don't know if this makes a difference or not, but the filetype that would need to be searched for a matching file name is PSD.

Upvotes: 1

Views: 1684

Answers (1)

David Zemens
David Zemens

Reputation: 53623

Such a thing is definitely possible. There was an easy way to do this in older versions of excel using Application.FileSearch in like, Excel 2003 and prior. For 2007 and newer, you will have to use or modify the solution from P. Havdra, here:

http://social.msdn.microsoft.com/Forums/en-US/isvvba/thread/a450830d-4fc3-4f4e-aee2-03f7994369d6/

This script prints out a list of files matching your search parameters and also gives you an annoying message box popup.

I made some modifications to his FileSearch subroutine to get you started. This builds an array of matching PSD file names in a specified directory and then you can do stuff with the array and interact with your worksheet data.

Sub FileSearch()
'
' Example of FileSearchByHavrda procedure calling as replacement of missing FileSearch     function in the newest MS Office VBA
' 01.06.2009, Author: P. Havrda, Czech Republic

'As modified Feb-13-2013, David Zemens, for _
http://stackoverflow.com/questions/14865258/populate-cell-with-a-filename-based-on-path-excerpt-in-previous-cell
'
Dim sDir As String  '< this is the search directory you will use

Dim FileNameWithPath As Variant
Dim ListOfFilenamesWithParh As New Collection ' create a collection of filenames
Dim rCount As Long 'row counter

Dim myArray() As Variant
Dim a As Long 'array iterator
a = 0

'Set the search directory:
sDir = "C:\DESKTOP\"  '<-- MODIFY TO SUIT YOUR NEEDS

' Filling a collection of filenames (search Excel files including subdirectories)
Call FileSearchByHavrda(ListOfFilenamesWithParh, sDir, "*.psd", False)

' Print list to immediate debug window and as a message window
For Each FileNameWithPath In ListOfFilenamesWithParh ' cycle for list(collection) processing
    'Create an array of matching filenames
    ReDim Preserve myArray(a)
    myArray(a) = FileNameWithPath
    a = a + 1
Next FileNameWithPath

' If no file was found:
If ListOfFilenamesWithParh.Count = 0 Then
    'Debug.Print "No file was found !"
    MsgBox "No file was found !"
Else:
    'some files were found, so do something with myArray
    '########################################################
    '
    '  <-- Code to manipulate your worksheet will go here -->
    '
    '
    '########################################################
End If
End Sub

Upvotes: 1

Related Questions