Hello,
I have failed to be able to get GetOpenFilename method
to restrict the list of files to select from beyond
the stated extentsion???

Is this possible -- and if so how.

What I mean is that I want the list to not include
ALL .xls files but rather a more narrow list such
as "orders*.xls" -- this list would be only the files
that began with "orders" and had a .xls extentsion.

2nd question, is it possible to force the file list
produced by GetOpenFilename -- to be have it in
<details mode and sorted by daye newest to oldest.
I know I can do this -- one the select window is
opened by this method. But can I override the default
<list setting.

Thanks in advance.

Sincerely,
Kevin Waite

Kevin,

GetOpenFilename won't do that. This will allow such as order*.xls

I use a version encapsulated in a class module, attached below. To use it,
add this code to a class module, call it clsGetOpenFileName, and invoke it
is the following way

Dim cFileOpen As clsGetOpenFileName


Set cFileOpen = New clsGetOpenFileName


With cFileOpen
.FileName = "order*.xls"
.FileType = "Excel Files"
.DialogTitle = "Class GetOpenFileName Demo"
.MultiFile = "N"
.SelectFile


If .SelectedFiles.Count 0 Then
MsgBox (.SelectedFiles(1))
End If
End With


Set cFileOpen = Nothing



'Class code


Option Explicit

'-----------------------------**-----------------------------*-*------------
--
' Win32 API Declarations
'-----------------------------**-----------------------------*-*------------
--
Private Declare Function GetOpenFileName Lib "comdlg32.dll" _
Alias "GetOpenFileNameA" ( _
pOpenfilename As OPENFILENAME) As Long


Private Declare Function GetSaveFileName Lib "comdlg32.dll" _
Alias "GetSaveFileNameA" ( _
pOpenfilename As OPENFILENAME) As Long


Private Declare Function GetShortPathName Lib "kernel32" _
Alias "GetShortPathNameA" ( _
ByVal lpszLongPath As String, _
ByVal lpszShortPath As String, _
ByVal cchBuffer As Long) As Long


Private Type OPENFILENAME
nStructSize As Long
hWndOwner As Long
hInstance As Long
sFilter As String
sCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
sFile As String
nMaxFile As Long
sFileTitle As String
nMaxTitle As Long
sInitialDir As String
sDialogTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
sDefFileExt As String
nCustData As Long
fnHook As Long
sTemplateName As String
End Type


'-----------------------------**-----------------------------*-*------------
--
' Private Variables
'-----------------------------**-----------------------------*-*------------
--
Private OFN As OPENFILENAME


Private sFileType As String 'Type of file narrative
Private sFileName As String 'Filename string to restrict list
Private sReadOnly As String 'Y/N flag
Private sMultiFile As String 'Allow selection of multiple files
Private sTitle As String 'Title in file dialog box


'-----------------------------**-----------------------------*-*------------
--
' Private Constants
'-----------------------------**-----------------------------*-*------------
--
Private Const OFN_ALLOWMULTISELECT As Long = &H200
Private Const OFN_CREATEPROMPT As Long = &H2000
Private Const OFN_ENABLEHOOK As Long = &H20
Private Const OFN_ENABLETEMPLATE As Long = &H40
Private Const OFN_ENABLETEMPLATEHANDLE As Long = &H80
Private Const OFN_EXPLORER As Long = &H80000
Private Const OFN_EXTENSIONDIFFERENT As Long = &H400
Private Const OFN_FILEMUSTEXIST As Long = &H1000
Private Const OFN_HIDEREADONLY As Long = &H4
Private Const OFN_LONGNAMES As Long = &H200000
Private Const OFN_NOCHANGEDIR As Long = &H8
Private Const OFN_NODEREFERENCELINKS As Long = &H100000
Private Const OFN_NOLONGNAMES As Long = &H40000
Private Const OFN_NONETWORKBUTTON As Long = &H20000
Private Const OFN_NOREADONLYRETURN As Long = &H8000& '*see comments
Private Const OFN_NOTESTFILECREATE As Long = &H10000
Private Const OFN_NOVALIDATE As Long = &H100
Private Const OFN_OVERWRITEPROMPT As Long = &H2
Private Const OFN_PATHMUSTEXIST As Long = &H800
Private Const OFN_READONLY As Long = &H1
Private Const OFN_SHAREAWARE As Long = &H4000
Private Const OFN_SHAREFALLTHROUGH As Long = 2
Private Const OFN_SHAREWARN As Long = 0
Private Const OFN_SHARENOWARN As Long = 1
Private Const OFN_SHOWHELP As Long = &H10
Private Const OFS_MAXPATHNAME As Long = 260


'OFS_FILE_OPEN_FLAGS and OFS_FILE_SAVE_FLAGS below are mine to save long
'statements; they're not a standard Win32 type.
Private Const OFS_FILE_OPEN_FLAGS = OFN_EXPLORER Or _
OFN_LONGNAMES Or _
OFN_CREATEPROMPT Or _
OFN_NODEREFERENCELINKS


Private Const OFS_FILE_SAVE_FLAGS = OFN_EXPLORER Or _
OFN_LONGNAMES Or _
OFN_OVERWRITEPROMPT Or _
OFN_HIDEREADONLY


'-----------------------------**-----------------------------*-*--
' Class Properties
'-----------------------------**-----------------------------*-*--
Public SelectedFiles As New Collection

Public Property Let FileType(FileType As String)
sFileType = FileType
End Property

Public Property Let FileName(FileName As String)
sFileName = FileName
End Property

Public Property Let MultiFile(MultiFile As String)
sMultiFile = UCase(MultiFile)
End Property

Public Property Let DialogTitle(Title As String)
sTitle = Title
End Property

Public Property Get ReadOnly()
ReadOnly = sReadOnly
End Property

'-----------------------------**-----------------------------*-*--
' Class Methods
'-----------------------------**-----------------------------*-*--
Public Function SelectFile() As Long
'-----------------------------**-----------------------------*-*--
Dim i
Dim sFilters As String
Dim sBuffer As String
Dim sLongname As String
Dim sShortname As String

If ValidInput Then
'create a string of filters for the dialog
sFilters = sFileType & vbNullChar & vbNullChar

With OFN

.nStructSize = Len(OFN) 'Size of the OFN structure
.sFilter = sFilters 'Filters for dropdown combo
.nFilterIndex = 1 'Index to the initial filter


'Default filename, plus additional padding for user 's final
' selection(s). Must be double-null terminated
.sFile = sFileName & Space$(1024) & vbNullChar & vbNullChar

.nMaxFile = Len(.sFile) 'the size of the buffer
'Default if file has no extension
.sDefFileExt = sFileName & vbNullChar & vbNullChar
'Make space for file title if single selection made,
' double-null terminated, and its size
.sFileTitle = vbNullChar & Space$(512) & _
vbNullChar & vbNullChar
.nMaxTitle = Len(OFN.sFileTitle)
'Starting folder, double-null terminated
.sInitialDir = ActiveWorkbook.Path & vbNullChar

.sDialogTitle = sTitle 'the dialog title string

'Default open flags and multiselect
.flags = OFS_FILE_OPEN_FLAGS Or _
OFN_NOCHANGEDIR

If sMultiFile = "Y" Then .flags = .flags Or _
OFN_ALLOWMULTISELECT

End With

SelectFile = GetOpenFileName(OFN)
If SelectFile Then
'Remove trailing pair of terminating nulls and
' trim returned file string
sBuffer = Trim$(Left$(OFN.sFile, Len(OFN.sFile) - 2))
'If multiple-select, first member is Path, remaining
' members are the files under that path
Do While Len(sBuffer) 3
SelectedFiles.Add StripDelimitedItem(sBuffer, _
vbNullChar)
Loop

sReadOnly = Abs((OFN.flags And OFN_READONLY))

End If
End If

End Function


Private Sub Class_Initialize()
sTitle = "GetOpenFileName"
End Sub


Private Sub Class_Terminate()
Set SelectedFiles = Nothing
End Sub


'-----------------------------**-----------------------------*-*------
Private Function ValidInput() As Boolean
'-----------------------------**-----------------------------*-*------
Dim i As Integer


ValidInput = True


i = 1
If IsEmpty(sFileName) Then
sFileName = " - a file description must be supplied"
i = i + 1
ValidInput = False
End If


If IsEmpty(sFileType) Then
sFileType = " - a file extension must be supplied"
i = i + 1
ValidInput = False
End If


If sMultiFile < "Y" And sMultiFile < "N" Then
sMultiFile = "Multiple files must be Y or N"
i = i + 1
ValidInput = False
End If


End Function


'-----------------------------**-----------------------------*-*------
Private Function StripDelimitedItem(startStrg As String, _
delimiter As String) As String
'-----------------------------**-----------------------------*-*------
'take string separated by nulls, split off 1 item, and shorten string
' so the next item is ready for removal.
Dim pos As Long
Dim item As String


pos = InStr(1, startStrg, delimiter)


If pos Then
StripDelimitedItem = Mid$(startStrg, 1, pos)
startStrg = Mid$(startStrg, pos + 1, Len(startStrg))
End If


End Function


'-----------------------------**-----------------------------*-*------
Private Function TrimNull(item As String) As String
'-----------------------------**-----------------------------*-*------
Dim pos As Integer


pos = InStr(item, Chr$(0))
If pos Then
TrimNull = Left$(item, pos - 1)
Else
TrimNull = item
End If


End Function






--

HTH

RP
(remove nothere from the email address if mailing direct)


"kevin_waite" wrote in message
oups.com...
Hello,
I have failed to be able to get GetOpenFilename method
to restrict the list of files to select from beyond
the stated extentsion???

Is this possible -- and if so how.

What I mean is that I want the list to not include
ALL .xls files but rather a more narrow list such
as "orders*.xls" -- this list would be only the files
that began with "orders" and had a .xls extentsion.

2nd question, is it possible to force the file list
produced by GetOpenFilename -- to be have it in
<details mode and sorted by daye newest to oldest.
I know I can do this -- one the select window is
opened by this method. But can I override the default
<list setting.

Thanks in advance.

Sincerely,
Kevin Waite

I don't think there is any way to implement #2. Well, there might be
with some amount of hacking with APIs and grabbing control of various
windows and keyboard / mouse movements but IMO it would definitely not
be worth while, not to mention that after all isn't it supposed to be
the user's choice?

Also, without writing your own validation routine no way to enforce #1
either. Note that even with Bob's code, the specification is only an
*initial* setting. The user can always override it. Try it. Run the
code and with the dialog box open, in the Filename field enter *.* and
press the ENTER key.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article .com,
says...
Hello,
I have failed to be able to get GetOpenFilename method
to restrict the list of files to select from beyond
the stated extentsion???

Is this possible -- and if so how.

What I mean is that I want the list to not include
ALL .xls files but rather a more narrow list such
as "orders*.xls" -- this list would be only the files
that began with "orders" and had a .xls extentsion.

2nd question, is it possible to force the file list
produced by GetOpenFilename -- to be have it in
<details mode and sorted by daye newest to oldest.
I know I can do this -- one the select window is
opened by this method. But can I override the default
<list setting.

Thanks in advance.

Sincerely,
Kevin Waite

Hi,

one way:
(uncertain, because of 'SendKeys')

Sub Test()
Dim Filename As Variant
Do
SendKeys "order*{enter}%l{left}d%l{left}id"
Filename = Application.GetOpenFilename("Excel files,*.xls")
If VarType(Filename) < vbString Then Exit Do
If LCase(Dir(Filename)) Like "order*.xls" Then Exit Do
MsgBox "please select 'order' file."
Loop
MsgBox Filename
End Sub

but i don't know how to fix sort order.


the setting of this dialog is stored in registry for each dialog's title.
for instance, this is the registry data for 'Open' dialog in Excel 2000,

HKEY_CURRENT_USER Software Microsoft\Office\9.0\Common\Open Find\
Microsoft Excel\Settings\Open\View\Data

the following is the details that i see in regedit.
i am not sure that this depends on version, timing, etc.
(but likely to do)

views
offset 0018 (00:Preview, 01:Properties, 02:Details, 03:List)

sort
Name: 0030(00:unselected, 01:selected), 0038(00:descending, 01:ascending)
Modified: 0190, 0198
Type: 01B0, 01B8
Size: 01D0, 01D8

the following is an example that changes registry.
assuming that 'open.reg' is a file exported from the registry
(...Settings\Open\View\Data) by using regedit.

Sub Test2()
Dim Filename As Variant
Do
CreateObject("WScript.Shell").Run "regedit /s open.reg", 0, True
SendKeys "order*{enter}"
Filename = Application.GetOpenFilename("Excel files,*.xls", , "Open")
If VarType(Filename) < vbString Then Exit Do
If LCase(Dir(Filename)) Like "order*.xls" Then Exit Do
MsgBox "please select 'order' file."
Loop
MsgBox Filename
End Sub

--
HTH

okaizawa


kevin_waite wrote:
Hello,
I have failed to be able to get GetOpenFilename method
to restrict the list of files to select from beyond
the stated extentsion???

Is this possible -- and if so how.

What I mean is that I want the list to not include
ALL .xls files but rather a more narrow list such
as "orders*.xls" -- this list would be only the files
that began with "orders" and had a .xls extentsion.

2nd question, is it possible to force the file list
produced by GetOpenFilename -- to be have it in
<details mode and sorted by daye newest to oldest.
I know I can do this -- one the select window is
opened by this method. But can I override the default
<list setting.

Thanks in advance.

Sincerely,
Kevin Waite

Thank You!

This worked very well -- and I learned a lot too.

Do you know how one can set the hook field so that the location of the
getopenfilename window can be controlled?

You have been a great help.

Sincerely,
Kevin Waite

Is it possible to set the default path? I realize the user could
navigate away from there. It would be a great help as I have a default
repository for files to be imported.

This is really an excellent example. I learned a lot but still only
understand half of it.

Darn shame we can't get it to sort newest to oldest. My file names
"yymmddFileName.txt" would be a perfect fit for this feature.

Thank You

Craigm


--
Craigm
------------------------------------------------------------------------
Craigm's Profile: http://www.excelforum.com/member.php...o&userid=24381
View this thread: http://www.excelforum.com/showthread...hreadid=389596

Is it possible to specify the poisiton of the file picking window. Say
to the middle of the worksheet?

I found this in the class module ".sInitialDir" I think I can pass it
in as a string for the inital directory seeting or is there a better
method?


--
Craigm
------------------------------------------------------------------------
Craigm's Profile: http://www.excelforum.com/member.php...o&userid=24381
View this thread: http://www.excelforum.com/showthread...hreadid=389596

On 23 Jul 2005 08:30:16 -0700, "kevin_waite"
wrote in
microsoft.public.excel.programming:

I have failed to be able to get GetOpenFilename method
to restrict the list of files to select from beyond
the stated extentsion???

Is this possible -- and if so how.

What I mean is that I want the list to not include
ALL .xls files but rather a more narrow list such
as "orders*.xls" -- this list would be only the files
that began with "orders" and had a .xls extentsion.
[snip]

Doesn't the FileDialog object do this?

--
Michael Bednarek http://mbednarek.com/ "POST NO BILLS"