CODEWITHSUNDEEP
excelvbavariables
justinua
justinua

Reputation: 63

Find a file name within a folder and make it a variable

I'm trying to run a macro that will take a folder path I've provided and then take the file in that folder and make the file name the variable. That file name variable will then be used as part of a VLookup to get data from a cell within that file. Below is the code I've found so far, but I haven't been able to make it work:

Dim FilePath As String
FilePath = "____________________"

Set wbCodeBook = ThisWorkbook
    With Application.FileSearch
    .NewSearch
    'Change path to suit
    .LookIn = FilePath
    .FileType = msoFileTypeExcelWorkbooks
    'Optional filter with wildcard
    .filename = "*AD*.xlsm*"
    End With

I get Object doesn't support this action at the Application.FileSearch part of the code. I also don't know how I can take that code above and make the output the variable.

Is there any way to do this?

Upvotes: 0

Views: 1236

Answers (1)

alfromFR29
alfromFR29

Reputation: 346

Do you mean something like this ?

Public Function GetFileName() As String
    Dim sFilePath As String
    Dim sFileName As String
    '----------------
    sFilePath = "____________________"
    sFileName = "*AD*.xlsm*"
    GetFileName = Dir(sFilePath & sFileName, vbNormal)
End Function

If you put it a module, you can then call it in a cell using =GetFileName().

Upvotes: 1

Related Questions