VBA macro to fetch excel filenames that contain a specific string from folder that contains my active book

[ash.mak]

Hi Champs,

I know if I can't get my answer here, I probably wont get it elsewhere. I am trying to fetch excel book filenames from folder that contains the excelbook I am working on and contain a specific string like *WFM*.xl*. I receive few files daily from WFM dept but file names have "WFM" appear at different location within file name and since these are daily reports they have dates attached to the file names as well. So the only option I have left is to fetch file names that have WFM in them. Once I have these copied on active work sheet with full address, I can use INDIRECT() and concatenation to join the full file names with already known sheet names and cell refs to fetch data.

Hope I have been clear enough for you.

THNX!!!

 

[JLGWhiz]

You need to make sure the fPath variable on this line of code:

Code:
fPath = ThisWorkbook.Path
Code:

has the correct path for the WFM workbooks that you are trying to retrieve, otherwise this procedure fails.
In this For Next loop:

Code:
For i = LBound(rFile) To UBound(rFile)
MsgBox rFile(i)
Next
Code:

The nessage box is for demo purposes only and can be deleted, but the rFile(i) variable represents the workbooks that you are retrieving. That varible can be used to open and close the workbooks, with statements like:

Code:
Workbooks.Open rFile(i)
Code:

And

Code:
Workbooks(rFile).Close True 'Or False if you do not want to save before closing
Code:

Here is the Procedure:

Code:
Sub fileWFM()
Dim sh As Worksheet, lr As Long, fPath As String, fName As String, rFile() As Variant
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
fPath = ThisWorkbook.Path 'This must be the directory path for the WFM workbooks.
If Right(fPath, 1) <> "/" Then
fPath = fPath & "/"
End If
ctr = 1
fName = Dir(fPath & "*.xl*")
Do Until fName = ""
If InStr(UCase(fName), "WFM") > 0 Then
ReDim Preserve rFile(1 To ctr)
rFile(ctr) = fName
ctr = ctr + 1
End If
fName = Dir
Loop
For i = LBound(rFile) To UBound(rFile)
MsgBox rFile(i) 'The variable rFile(i) represents the workbooks you want to work with.
Next
End Sub
Code:

 

[ash.mak]

Thanks a million JLGWhizz.

that was a great solution and served hot as well. apreciate all your help.

 

[JLGWhiz]

Thanks a million JLGWhizz.

that was a great solution and served hot as well. apreciate all your help.

You are quite welcome,

Regards, JLG