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

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,900
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
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.
Code:
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

Well-known Member
Joined
Feb 3, 2010
Messages
9,900
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,900
Office Version
  1. 365
Platform
  1. Windows
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!
 

Forum statistics

Threads
1,186,101
Messages
5,955,854
Members
438,221
Latest member
Salvy03

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top