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. *Theuser
> > > > 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 howto
> > > > 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 -
|
Similar Threads
