Results 1 to 5 of 5

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

This is a discussion on VBA code to open folder and display specific prefix named files only within the Excel Questions forums, part of the Question Forums category; Hi, In my spreadsheet a macro opens a folder for the user to select multiple files, upon which the macro ...

  1. #1
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    5,025

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

    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

  2. #2
    Board Regular
    Join Date
    Feb 2006
    Posts
    3,437

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

    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

  3. #3
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    5,025

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

    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

  4. #4
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,156

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

    msoDialog with a filter setting?

    http://www.instantpages.ltd.uk/Office_FileDialog.htm
    http://msdn.microsoft.com/en-us/libr...ffice.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).

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  5. #5
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    5,025

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

    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!

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com