Trying to get the first file selected when doing
Application.GetOpenFilename.
In Excel 2002 I could do:

SendKeys "{TAB 7}", False

FileNameToOpen = _
Application.GetOpenFilename(FileFilter:="Text Files (*.txt), *.txt", _
Title:="")

and that worked fine.

In Excel 2003, it only works partially in that the first file has the dotted
line around it, but doesn't actually have the focus. If I do the HOME key
manually the first file gets the focus, but doing:

SendKeys "{TAB 7}", False
SendKeys "{HOME}", False

FileNameToOpen = _
Application.GetOpenFilename(FileFilter:="Text Files (*.txt), *.txt", _
Title:="")

doesn't work. Adding SendKeys "{HOME}", False just doesn't make any
difference. Tried it with the UP, DOWN, LEFT and RIGHT keys, but that didn't
work either.
Any suggestions how to do this?


RBS

The only reason for using GetOpenFilename I know of is to let the user
select one or several files. If you know you always want the first file
(alphabetically sorted?) in a folder, why not use the Scripting.Filesystem
object in the Scripting Runtime?

/Lars Hammarberg
www.camako.se
MSProject Premier Partner

"RB Smissaert" wrote in message
...
Trying to get the first file selected when doing
Application.GetOpenFilename.
In Excel 2002 I could do:

SendKeys "{TAB 7}", False

FileNameToOpen = _
Application.GetOpenFilename(FileFilter:="Text Files (*.txt), *.txt", _
Title:="")

and that worked fine.

In Excel 2003, it only works partially in that the first file has the
dotted
line around it, but doesn't actually have the focus. If I do the HOME key
manually the first file gets the focus, but doing:

SendKeys "{TAB 7}", False
SendKeys "{HOME}", False

FileNameToOpen = _
Application.GetOpenFilename(FileFilter:="Text Files (*.txt), *.txt", _
Title:="")

doesn't work. Adding SendKeys "{HOME}", False just doesn't make any
difference. Tried it with the UP, DOWN, LEFT and RIGHT keys, but that
didn't
work either.
Any suggestions how to do this?


RBS

It looks as if the Excel 2003 File, Open dialog ignores or kills any sent
keystrokes after the focus is shifted to the file window in the dialog.
There is another difference too - Excel 2003 highlights the first folder if
there is one, else the first file. Excel 2002 selected the first file, even
if folders are present.

If you can put up with this (second) problem you could use the
WindowsGetOpenfileName API which doesn't kill the sent keystrokes. See
below for an example. Note that I'm passing a space via SendKeys to
actually select the first folder/file.

--
Jim Rech
Excel MVP

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

Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
Flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type

Sub Command1_Click()
Dim OpenFile As OPENFILENAME
Dim lReturn As Long
Dim sFilter As String
OpenFile.lStructSize = Len(OpenFile)
OpenFile.hwndOwner = 0
OpenFile.hInstance = 0
sFilter = "Excel Files (*.xls)" & Chr(0) & "*.xls" & Chr(0)
OpenFile.lpstrFilter = sFilter
OpenFile.nFilterIndex = 1
OpenFile.lpstrFile = String(257, 0)
OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
OpenFile.lpstrFileTitle = OpenFile.lpstrFile
OpenFile.nMaxFileTitle = OpenFile.nMaxFile
OpenFile.lpstrInitialDir = CurDir
OpenFile.lpstrTitle = "File Open using Windows API"
OpenFile.Flags = &H80004
SendKeys "+{TAB} "
lReturn = GetOpenFileName(OpenFile)
If lReturn = 0 Then
MsgBox "The User pressed the Cancel Button"
Else
MsgBox "The user chose " & Trim(OpenFile.lpstrFile)
End If
End Sub

I don't always want the first file, but I need the focus somewhere to start
selecting files with the keyboard. The first file seems a sensible choice.

RBS


"Lars Hammarberg" wrote in message
...
The only reason for using GetOpenFilename I know of is to let the user
select one or several files. If you know you always want the first file
(alphabetically sorted?) in a folder, why not use the Scripting.Filesystem
object in the Scripting Runtime?

/Lars Hammarberg
www.camako.se
MSProject Premier Partner

"RB Smissaert" wrote in message
...
Trying to get the first file selected when doing
Application.GetOpenFilename.
In Excel 2002 I could do:

SendKeys "{TAB 7}", False

FileNameToOpen = _
Application.GetOpenFilename(FileFilter:="Text Files (*.txt), *.txt",
_
Title:="")

and that worked fine.

In Excel 2003, it only works partially in that the first file has the
dotted
line around it, but doesn't actually have the focus. If I do the HOME
key
manually the first file gets the focus, but doing:

SendKeys "{TAB 7}", False
SendKeys "{HOME}", False

FileNameToOpen = _
Application.GetOpenFilename(FileFilter:="Text Files (*.txt), *.txt",
_
Title:="")

doesn't work. Adding SendKeys "{HOME}", False just doesn't make any
difference. Tried it with the UP, DOWN, LEFT and RIGHT keys, but that
didn't
work either.
Any suggestions how to do this?


RBS

Thanks, will give that a try. It is a bit of a shame that I will have to add
this complexity just to select a file.

RBS

"Jim Rech" wrote in message
...
It looks as if the Excel 2003 File, Open dialog ignores or kills any sent
keystrokes after the focus is shifted to the file window in the dialog.
There is another difference too - Excel 2003 highlights the first folder
if
there is one, else the first file. Excel 2002 selected the first file,
even
if folders are present.

If you can put up with this (second) problem you could use the
WindowsGetOpenfileName API which doesn't kill the sent keystrokes. See
below for an example. Note that I'm passing a space via SendKeys to
actually select the first folder/file.

--
Jim Rech
Excel MVP

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

Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
Flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type

Sub Command1_Click()
Dim OpenFile As OPENFILENAME
Dim lReturn As Long
Dim sFilter As String
OpenFile.lStructSize = Len(OpenFile)
OpenFile.hwndOwner = 0
OpenFile.hInstance = 0
sFilter = "Excel Files (*.xls)" & Chr(0) & "*.xls" & Chr(0)
OpenFile.lpstrFilter = sFilter
OpenFile.nFilterIndex = 1
OpenFile.lpstrFile = String(257, 0)
OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
OpenFile.lpstrFileTitle = OpenFile.lpstrFile
OpenFile.nMaxFileTitle = OpenFile.nMaxFile
OpenFile.lpstrInitialDir = CurDir
OpenFile.lpstrTitle = "File Open using Windows API"
OpenFile.Flags = &H80004
SendKeys "+{TAB} "
lReturn = GetOpenFileName(OpenFile)
If lReturn = 0 Then
MsgBox "The User pressed the Cancel Button"
Else
MsgBox "The user chose " & Trim(OpenFile.lpstrFile)
End If
End Sub

Tried it out, but as you say selecting the first folder is a problem.
Worse is that you can't get the files sorted by date when showing the
dialog.
What is nice is that the selected file name shows in the File name listbox.
Alltogether I think I will stick with the old method.

RBS


"Jim Rech" wrote in message
...
It looks as if the Excel 2003 File, Open dialog ignores or kills any sent
keystrokes after the focus is shifted to the file window in the dialog.
There is another difference too - Excel 2003 highlights the first folder
if
there is one, else the first file. Excel 2002 selected the first file,
even
if folders are present.

If you can put up with this (second) problem you could use the
WindowsGetOpenfileName API which doesn't kill the sent keystrokes. See
below for an example. Note that I'm passing a space via SendKeys to
actually select the first folder/file.

--
Jim Rech
Excel MVP

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

Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
Flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type

Sub Command1_Click()
Dim OpenFile As OPENFILENAME
Dim lReturn As Long
Dim sFilter As String
OpenFile.lStructSize = Len(OpenFile)
OpenFile.hwndOwner = 0
OpenFile.hInstance = 0
sFilter = "Excel Files (*.xls)" & Chr(0) & "*.xls" & Chr(0)
OpenFile.lpstrFilter = sFilter
OpenFile.nFilterIndex = 1
OpenFile.lpstrFile = String(257, 0)
OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
OpenFile.lpstrFileTitle = OpenFile.lpstrFile
OpenFile.nMaxFileTitle = OpenFile.nMaxFile
OpenFile.lpstrInitialDir = CurDir
OpenFile.lpstrTitle = "File Open using Windows API"
OpenFile.Flags = &H80004
SendKeys "+{TAB} "
lReturn = GetOpenFileName(OpenFile)
If lReturn = 0 Then
MsgBox "The User pressed the Cancel Button"
Else
MsgBox "The user chose " & Trim(OpenFile.lpstrFile)
End If
End Sub

Worse is that you can't get the files sorted by date when showing the
dialog.

Seems to sort from the Details view just like Excel's File, Open.

--
Jim Rech
Excel MVP

Yes, you can sort, but it seems you have to do this every time again as it
doesn't retain the previous sort.
I think I solved it all now using the keybd_event API:

Public Const KEYEVENTF_EXTENDEDKEY = &H1
Public Const KEYEVENTF_KEYUP = &H2
Public Declare Sub keybd_event _
Lib "user32.dll" (ByVal bVk As Byte, _
ByVal bScan As Byte, _
ByVal dwFlags As Long, _
ByVal dwExtraInfo As Long)

Sub OpenFile()

SendKeys "{TAB 7}", False

keybd_event 36, 0, 0, 0 'press HOME
keybd_event 36, 0, KEYEVENTF_KEYUP, 0 'release HOME


FileNameToOpen = _
Application.GetOpenFileName(FileFilter:="Text Files (*.txt), *.txt", _
Title:="")

'rest of code

End Sub


RBS


"Jim Rech" wrote in message
...
Worse is that you can't get the files sorted by date when showing the
dialog.

Seems to sort from the Details view just like Excel's File, Open.

--
Jim Rech
Excel MVP

SendKeys "{TAB 7}", False

So you prefer 7 tabs to a single shift-tab?<g

SendKeys "+{TAB} "

--
Jim Rech
Excel MVP

Now you lost me. I tried your SendKeys "+{TAB} "
but that didn't get me where I want.
Is there something wrong with SendKeys "{TAB 7}", False ?

RBS


"Jim Rech" wrote in message
...
SendKeys "{TAB 7}", False

So you prefer 7 tabs to a single shift-tab?<g

SendKeys "+{TAB} "

--
Jim Rech
Excel MVP