cory.j.layman
1/7/2009 5:59:00 PM
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.
On Jan 7, 12:13 pm, Joel <J...@discussions.microsoft.com> wrote:
> 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.lay...@gmail.com" 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 <J...@discussions.microsoft.com> 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
>
> > > "cory.j.lay...@gmail.com" 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 -- Hide quoted text -
>
> - Show quoted text -