Change Current Directory

[cory.j.layman]

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

 

[Joel]

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

 

[cory.j.layman]

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.

 

[Joel]

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

 

[cory.j.layman]

Ideally, what I would like is if it would work like this.

FiletoOpen = XL.Application _
.GetOpenFilename("Excel Files (*" & SearchStr & "*.xls), *.xls",
MultiSelect:=False)

I have tried it like that and it did not work for me. There could be
multiple files that could fit the *SearchStr*.xls format. The user
needs to select a single file from the several that fit that pattern.