Macro to filter for specific files (excel 2003)

Topic

New Reply

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)

Reply | Quote

Replies

I do not believe that this is possible using GetOpenFilename.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Here is a version of the code from post 576337 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).

Reply | Quote

Thanks Hans. This is exactly what I needed.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

YOU STILL HAVE NOT ANSWERED HANS QUESTION!!!!!

1- OK, the sheet begins with “block”, but is there only one sheet in the workbook that begins with “block”, or could there be more than one?

2- If there can be more than one, which one do wyou want copied?

3- Is there a possibility that no sheet will begin with “block”, and if so what do you want done?

You need three very specific answers if you want any help with your problem. Please number your answers 1, 2, and 3, and don’t send the reply until you have three answers.

Reply | Quote

Replace the line

wbk.Sheets(“block 1”).Copy Before:=Workbooks(“recap.xls”).Sheets(1)

with the following:

Dim wsh As Worksheet
For Each wsh In wbk.Worksheets
If LCase(Left(wsh.Name, 5)) = “block” Then
wsh.Copy Before:=Workbooks(“recap.xls”).Sheets(1)
Exit For
End If
Next wsh

If that doesn’t do what you want, you will have to answer Legare’s questions precisely and completely.

Reply | Quote

Hans you are a genius. This is exactly what I need. Thanks.

Reply | Quote