Windows API - File Menu Processing...

[JEB]

Please excuse my lack of knowledge/understanding of the Windows API - I'm
totally confused.
I have an Excel application that calls for the user to import a text file.
At the moment I have confined the file name and path to hard code in VBA (The
user MUST have a file called ABC.txt and it MUST reside in the c:\temp
directory. This is no longer acceptable.
I need to provide the functionality for the user to select the file from a
list in their My Documents directory (or sub directory), only limiting the
file name to .txt files.
What is the best and simplest way to approch this?
Thank you in advance.
JEB

 

[Steve Yandl]

JEB,

Give this a try. I tested on Excel 2003. In the example, I have a message
box display the value of vrtSelectedItem, you would change to do something
with the path to the txt file.

_________________________________________

Sub UserGetMyDocsFiles()

Dim fd As FileDialog
Dim vrtSelectedItem As Variant

' Determine Path to the My Documents folder
Set objShell = CreateObject("Shell.Application")
Set objMyDocsFldr = objShell.Namespace(&H5&)
strMyDocsPath = objMyDocsFldr.Self.Path

' Create a file picker dialog opening to My Documents
' Limit return to .txt files only one selection
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
..Filters.Clear
..Filters.Add "Text Documents", "*.txt"
..AllowMultiSelect = False
..Title = "Fetch the text file you want for XL"
..InitialFileName = strMyDocsPath
If .Show = -1 Then
For Each vrtSelectedItem In .SelectedItems
MsgBox vrtSelectedItem
Next vrtSelectedItem
End If
End With

Set objShell = Nothing
Set fd = Nothing

End Sub

_______________________________________


Steve Yandl