Like this

Sub File_Opener()
Dim XL As Excel.Application
Dim SearchStr, FileName As String
Set XL = CreateObject("Excel.Application")
XL.Visible = True


Set Files = Nothing
FiletoOpen = XL.Application _
.GetOpenFilename("Excel Files (*.xls), *.xls", MultiSelect:=True)

If Not IsArray(FiletoOpen) Then
MsgBox ("Cannot Open file - Exiting Macro")
Exit Sub
End If
For Each FName In FiletoOpen
XL.Workbooks.Open FName
Next FName
End Sub

" wrote:

That's a great solution. I failed to mention that there might be
several files that fit the "*SearchStr*" so the user needs to specify
the exact file to open which is why I need to use some type of of Open
File dialog. I would just like to filter the results of the dialog.


On Jan 7, 9:47 am, Joel wrote:
Sub File_Opener()
Dim XL As Excel.Application
Dim SearchStr, FileName As String

Set XL = CreateObject("Excel.Application")
XL.Visible = True

Folder = "c:\temp\"
FName = Dir(Folder & "*.xls")
Do While FName < ""

XL.Workbooks.Open Folder & FName
FName = Dir()
Loop
End Sub



" wrote:
I have a VB program in Outlook that's making calls to Excel. The user
needs to open an Excel file and there are two things I would like to
do to help things go easier.

1.) I would like to change the current directory. If the code was in
Excel, it would be a simple matter of of using the ChDrive and ChDir
functions. However, the Excel.Application object does not contain
either of those fuctions and I have not been able figure it out how to
do it.

2.) I would like to filter the files in the Open Filename dialog box.
The filename will be of the form *SearchStr*.xls*. I can set the
*.xls* file filter easily enough, but I have not been successful at
setting the *SearchStr* for the file name filter.

Here is a summary of the code I am using.

Sub File_Opener()
Dim XL As Excel.Application
Dim SearchStr, FileName As String

'*** Omit code that populates Search String based on the contents of
an open email message

Set XL = CreateObject("Excel.Application")
XL.Visible = True

'*** How Could I change the current directory in Excel?

'*** How can I set the file filter in the Open File Dialog to be of
the form *SearchStr*.xls*?
FileName = XL.GetOpenFilename("Excel Files (*.xls*), *.xls*")
If FileName = "" Then Exit Sub Else XL.Workbooks.Open FileName
End Sub- Hide quoted text -

- Show quoted text -