Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro to filter for specific files (excel 2003)

    Hello All,
    I have a simple question. I have GetOpenFilename command from a macro to get files for a macro, but I only want the user to see all files that have "block 1" word included somewhere in excel file name. Below is the code that I am updating, but I don't know how to do it. Any help would be great.


    FilesToOpen = Application.GetOpenFilename _
    (FileFilter:="Microsoft Excel Files (*.xls), *.xls", _
    MultiSelect:=True)

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to filter for specific files (excel 2003)

    I do not believe that this is possible using GetOpenFilename.
    Legare Coleman

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to filter for specific files (excel 2003)

    GetOpenFilename doesn't allow you to specify a filename, only the extension. If all users have Excel 2002 or later, you can use FileDialog:

    Dim vFile As Variant
    With Application.FileDialog(msoFileDialogFilePicker)
    .Filters.Add "Excel files", "*.xls"
    .InitialFileName = "C:Excel*block 1*.xls"
    .AllowMultiSelect = True
    If .Show = True Then
    For Each vFile In .SelectedItems
    MsgBox vFile
    Next vFile
    End If
    End With

    Substitute the correct path, and replace the MsgBox line with the action you want to perform on the selected files.

  4. #4
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to filter for specific files (excel 2003)

    Thank you both. Hans. I was trying to input this command in the code from my previous post (576,337) but could not get it to work. I think I am just going to leave the getopenfile command for now. Thanks anyway.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to filter for specific files (excel 2003)

    Here is a version of the code from <post#=576337>post 576337</post#> using FileDialog:

    Sub FileProcessingExample()
    ' Variable Declaration
    Dim vFile As Variant
    Dim FilesToOpen
    Dim iFileCount As Integer
    Dim x As Integer
    Dim wbk As Workbook

    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    ' Get files to work with
    With Application.FileDialog(msoFileDialogFilePicker)
    .Filters.Add "Excel files", "*.xls"
    .InitialFileName = "C:Excel*block 1*.xls"
    .AllowMultiSelect = True
    If .Show = True Then
    ' Act on each file
    For Each vFile In .SelectedItems
    ' Open it
    Set wbk = Workbooks.Open(Filename:=vFile)
    ' Transfer sheet
    wbk.Sheets("1-15 1st Half").Copy Before:=Workbooks("Summary.xls").Sheets(1)
    ' Close it
    wbk.Close SaveChanges:=False
    Next vFile
    End If
    iFileCount = .SelectedItems.Count
    End With

    ' Give a message saying you are done
    If iFileCount = 1 Then
    MsgBox "1 File was processed"
    Else
    MsgBox iFileCount & " Files were processed"
    End If

    ExitHandler:
    Application.ScreenUpdating = True
    Exit Sub

    ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
    End Sub

    Don't forget to modify the path (C:Excel in this example).

  6. #6
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to filter for specific files (excel 2003)

    Thanks Hans. This is exactly what I needed.

  7. #7
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to filter for specific files (excel 2003)

    Hans,

    I am trying to modify the code above. I change the following:
    .InitialFileName = "C:Excel*block *.xls"
    Since I am able to select multiple files to process, I also need to change the line
    wbk.Sheets("1-15 1st Half").Copy Before:=Workbooks("Summary.xls").Sheets(1)
    I will be selecting four files with different sheets name. For example , the first file has Sheet ("1-15 1st Half"), the second file has a different sheet name ("16-30 2nd Half"), and so fourth. How do i process each file so that I can select diffent sheets names for each file. Is there a command to change this? I tried the following, but didn't work.
    wbk.sheets(array("1-15 1st Half, 16-30 2nd Halft").copybefore:=Workbooks("summary.xls").sheet (1)
    But it gave me some script error. Any help would be great.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to filter for specific files (excel 2003)

    How do you determine which sheet you want to copy from each file? Or does each file have only one sheet?

  9. #9
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to filter for specific files (excel 2003)

    Hans,
    the sheet to copy will either be block 1, block2, block 3, or block four depending on the file is processing. However, it will only be one. For example if it selects ("1-15" 1st Half"). It should process sheet ("block 1"). Each selected file will have a sheet called block with it. For example, ("1-15 1st Half") will only have block 1. File ("16-30" 2nd half") will have a sheet call ("block 2") Does that help?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to filter for specific files (excel 2003)

    I'm utterly confused now. Earlier, the file names contained "block" and the sheet names contained "1st half" or "2nd half". Now it is the other way round.

  11. #11
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to filter for specific files (excel 2003)

    I am sorry Hans. I made some modifications to the names of the files from "1st half" to "block". Each file has a sheet name call "block" too. Below is the new code

    Sub Processfiles()
    ' Variable Declaration
    Dim vFile As Variant
    Dim FilesToOpen
    Dim iFileCount As Integer
    Dim x As Integer
    Dim wbk As Workbook
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    ' Get files to work with
    With Application.FileDialog(msoFileDialogFilePicker)
    .Filters.Add "Excel files", "*.xls"

    .InitialFileName = _
    "C:adminNew Code StructuresNew Timesheet*block*.xls"
    .AllowMultiSelect = True
    If .Show = True Then
    ' Act on each file
    For Each vFile In .SelectedItems
    ' Open it
    Set wbk = Workbooks.Open(Filename:=vFile)
    ' Transfer sheet
    wbk.Sheets("block 1").Copy Before:=Workbooks("recap.xls").Sheets(1)----------------------------->when I run this macro and the sheet name is not "block 1" I get an error. The files I will be selecting can either have block 1,2,3 or 4. This is where I am running into the error. I don't know if it makes sense or not, but I need to be able to select multiple files and different sheets (block 1, 2, 3, or 4)?
    ' Close it
    wbk.Close SaveChanges:=False
    Next vFile
    End If
    iFileCount = .SelectedItems.Count
    End With

    ' Give a message saying you are done
    If iFileCount = 1 Then
    MsgBox "1 File was processed"
    Else
    MsgBox iFileCount & " Files were processed"
    End If

    ExitHandler:
    Application.ScreenUpdating = True
    Exit Sub

    ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
    End Sub

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to filter for specific files (excel 2003)

    So we're back to my earlier questions: how can we determine the sheet name? Or does each workbook (file) contain only one sheet?

  13. #13
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to filter for specific files (excel 2003)

    Hans, each workbook (file) has three sheets in it. However, I am only trying to pull one sheet from these workbooks. I was wondering if there is an if statement which can pull a sheet as is processing the workbook. The if statement would say
    If Sheet name in workbook is "block1" or "block 2" or "block 3" or "block 4" then copy the sheet to the "recap" workbook. Let me know if this helps, if not, I can always run the macro, but will have to select all files with block 1 then all files with block 2 and so forth.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to filter for specific files (excel 2003)

    You're not being specific enough.
    1) Is there a way to determine which sheet ("block 1" or "block 2" or ...) you need from the filename?
    or
    2) Is the sheet you need the only one whose name begins with "block"? For example, if the sheets are named "Woody", "Jose" and "block 3", it is obvious which one you need. But if they are called "block 1", "Jose" and "block 2", you have a problem.

  15. #15
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to filter for specific files (excel 2003)

    Hello Hans. The first statement on number 2 is correct. The sheet that I need begins with a "block" on it.

Page 1 of 2 12 LastLast

Posting Permissions

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