Change Current Directory
 

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

Change Current Directory
 

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

Change Current Directory
 

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 -

Change Current Directory
 

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 -

Change Current Directory
 

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 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



" 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.