VBA code to open folder and display specific prefix named files only

[JackDanIce]

Hi,

In my spreadsheet a macro opens a folder for the user to select multiple files, upon which the macro performs various actions.

The folder contains a large number of files and all the files I want being with the prefix "IED p&l"

Similar to how you can write code for a folder to open with defined file types (e.g. .xls, .xlsm etc), is there a way for the folder opened to only display files that being with the above prefix, i.e. "IED p&l*.*"

Thanks,
Jack

 

[ravishankar]

Hi
save a workbook with these codes inside the folder of your choice and run the macro. Copy the macro xxxx which you wish to run on all the files.

Sub Jack()
Dim z  As Long, e As Long
Dim f As String, b As String
Application.ScreenUpdating = fasle
Cells(1, 1) = "=cell(""filename"")"
Cells(1, 2) = "=left(A1,find(""["",A1)-1)"
Cells(2, 1).Select
f = Dir(Cells(1, 2) & "*.xls")
    Do While Len(f) > 0
    If InStr(f, "IED p&l") > 0 Then
    ActiveCell.Formula = f
    ActiveCell.Offset(1, 0).Select
    End If
    f = Dir()
    Loop
z = Cells(Rows.Count, 1).End(xlUp).Row
    For e = 2 To z
        If Cells(e, 1) <> ActiveWorkbook.Name Then
        b = Cells(1, 2) & Cells(e, 1)
        Workbooks.Open Filename:=b
      Call xxxx ' xxxx is your macro name
        ActiveWorkbook.Close True
        End If
    Next e
    Application.ScreenUpdating = True
MsgBox "complete."
End Sub

It lists all file names with IED p&l in it in col A. Opens each of them, runs the macro xxxx, saves the file and closes it. I suggest you try it on a test file before you use it.
ravi

 

[JackDanIce]

Thank you very much ravishankar, but unfortunately this won't quite do what I require.

My macro requests the user to open a summary file (in a pre-defined location) and then to open 6 "IED*" files from another folder. But this second folder contains all month to date IED files and more than the 6 daily ones required, as well as other files that do not begin with the prefix IED

I was hoping simply be able to open the folder than contains the IED and other files but to only display IED files, from which the user could choose the 6 they need.

I could modify your code to subselect just the IED files the user would need but the post fix to the files which indicates their value date is not always in the same format, either.

I thought/hoped there would be a simple filter or wild card like code I could envoke which would limit the files shown when the macro opens the required folder

Thank you once again for your efforts though, do appreciate it.
]ack

 

[xenou]

msoDialog with a filter setting?

http://www.instantpages.ltd.uk/Office_FileDialog.htm
http://msdn.microsoft.com/en-us/library/aa219843(office.11).aspx

Not something I use often but it should in theory let you set a filter, open the dialog to display those file filter criteria, and let the user select multiple files (my last attempt with a file dialog didn't go very well, though - you can post back if you want to try it and can't make progress yourself).

 

[JackDanIce]

Thanks for that xenou, need to play around with it, but if it's too much hassle, may just berate the end users and tell them to deal with it!