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