Using Excel2003 (code may also be used on 2007), on XP

I usually hardcode the name of source reports from our mainframe, because
they usually retain a single name and are overwritten each month. However, I
now have one where the source filenames are included in the file name to
prevent overwriting old data. I'm trying to minimize the steps for the end
user of this workbook to update the raw data and run some analyses.

In this particular workbook, there are three source files, all of which will
have the month in the filename. I decided that a fast option would be to use:
Application.FileDialog(msoFileDialogOpen)
to have the user select the appropriate files for each of the three items,
then run the code. However, I haven't found a way to change the prompt, such
as in a msgbox [Title] where I could change it to say "Select the current abc
file".

My less appealing alternative is to build a userform and link the
FileDialogue to three separate labels, and have the instructional text on the
userform, but I'm hoping there is a better way, with hints as to the
appropriate syntax. The key is that I need to prompt the user so they know
which of the three source files to select (in order) so that the correct data
is pulled from each file.

Thanks!
Keith

I use something like this

Sub OpenWorkbook(oWB As Workbook, myTitle As String)
Dim sFile As String
Dim ShortName As String


With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Filters.Clear
.Title = myTitle
If .Show = False Then Exit Sub
sFile = .SelectedItems(1)
End With

ShortName = Right(sFile, Len(sFile) - InStrRev(sFile, "\"))

Set oWB = Nothing
On Error Resume Next
Set oWB = Workbooks(ShortName)
On Error GoTo 0


If oWB Is Nothing Then
Application.AutomationSecurity = msoAutomationSecurityLow
Set oWB = Workbooks.Open(sFile, UpdateLinks:=False, ReadOnly:=True)
Application.AutomationSecurity = msoAutomationSecurityByUI

End If
End Sub

I then test in the calling sub like this

if myWB is nothing then
Msgbox("Workbook not opened. Execution ending.")
End
end if


"ker_01" wrote:

> Using Excel2003 (code may also be used on 2007), on XP
>
> I usually hardcode the name of source reports from our mainframe, because
> they usually retain a single name and are overwritten each month. However, I
> now have one where the source filenames are included in the file name to
> prevent overwriting old data. I'm trying to minimize the steps for the end
> user of this workbook to update the raw data and run some analyses.
>
> In this particular workbook, there are three source files, all of which will
> have the month in the filename. I decided that a fast option would be to use:
> Application.FileDialog(msoFileDialogOpen)
> to have the user select the appropriate files for each of the three items,
> then run the code. However, I haven't found a way to change the prompt, such
> as in a msgbox [Title] where I could change it to say "Select the current abc
> file".
>
> My less appealing alternative is to build a userform and link the
> FileDialogue to three separate labels, and have the instructional text on the
> userform, but I'm hoping there is a better way, with hints as to the
> appropriate syntax. The key is that I need to prompt the user so they know
> which of the three source files to select (in order) so that the correct data
> is pulled from each file.
>
> Thanks!
> Keith