Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Customizing a message with msoFileDialogOpen ?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Customizing a message with msoFileDialogOpen ?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filter The msoFileDialogOpen | Excel Programming | |||
Application.FileDialog(msoFileDialogOpen) and error 75 | Excel Programming | |||
Application.FileDialog(msoFileDialogOpen) | Excel Programming | |||
FileDialog(msoFileDialogOpen) | Excel Programming | |||
Customizing the Locked Cell Warning Message Box | Excel Programming |