Get "Open File" dialog

[DianePDavies]

I want to select a file by opening tha standard Windows "open file" menu -
i.e. be able to go through a folder structure and then select a file.

Please show VBA sample code for that.

I think it is something like using the GetOpenFileName Function - but the
sample code found so far is quite comprehensive - and I cant really
understand how to use it...

 

[Douglas J. Steele]

Check http://www.mvps.org/access/api/api0001.htm at "The Access Web"

Copy everything in the shaded area (between Code Start and Code End) into a
new module (not a class module, nor a module associated with a form or
report). To invoke, use code like the four lines at the very top of the
page:

Dim strFilter As String
Dim strInputFileName as string

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)

That will prompt for Excel files only. You need to play with the filter to
prompt for other file types.

 

[Paolo]

Hi Diane,
Take a look at this URL
http://www.mvps.org/access/api/api0001.htm
there's exactly what you are asking for.

HTH Paolo

 

[John... Visio MVP]

Just a heads up. I tried the routine and got an error

I replaced
If IsMissing(hwnd) Then hwnd = Application.hWndAccessApp
with
If IsMissing(hwnd) Then hwnd = Application.WindowHandle32
and it worked

John... Visio MVP

 

[Douglas J. Steele]

Let me guess, John. You did this from Visio (or some other client than
Access)

Application.hWndAccessApp would only have meaning in Access.

 

[DianePDavies]

--
Diane

Check http://www.mvps.org/access/api/api0001.htm at "The Access Web"

Copy everything in the shaded area (between Code Start and Code End) into a
new module (not a class module, nor a module associated with a form or
report). To invoke, use code like the four lines at the very top of the
page:

Dim strFilter As String
Dim strInputFileName as string

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)

That will prompt for Excel files only. You need to play with the filter to
prompt for other file types.

 

[DianePDavies]

It works. It's just a little overwhelming with the lengthy code... but it
worked - and I didn't get any errors when I call the code with this (as
stated at the top of the article..):

Dim strFilter As String
Dim strInputFileName as string

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)

 

[Clifford Bass]

Hi Diane,

In addition to the coding supplied by the others you can also use the
Application.FileDialog:

Public Sub SelectAFile()

Dim fd As FileDialog

Set fd = FileDialog(msoFileDialogOpen)
With fd
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Text Files", "*.txt; *.csv; *.tab"
.Filters.Add "All Files", "*.*"
If .Show Then
MsgBox .SelectedItems(1)
End If
End With

End Sub

See the FileDialog help for other customizing settings. You will need
to add a reference to the Microsoft Office xx.x Object Library (in VBA
Editor, Tools menu, References item).

Clifford Bass

 

[John... Visio MVP]

You are good. ;-) The reference to Access was missed. I guess I was making
the same mistake as those who consider microsoft.public.access is public
access to Microsoft.

John... Visio MVP

 

[Douglas J. Steele]

By itself, "Application" is going to refer to whatever client is running, so
if it's Visio or Word, neither of which have an hWndAccessApp property
defined, it still wouldn't work even if you had a reference set to Access.

hWndAccessApp is the name of the property that contains the value of the
handle assigned by Windows to the main Access window. Similarly,
WindowHandle32 is what Visio calls the handle assigned by Windows to the
main Visio window. Excel calls it hWnd, and I don't see a similar property
for Word.

The only reason to assign that value in the structure is to indicate which
window owns the dialog box. It can be any valid window handle, or it can be
0.

 

[N.Ordiers]

Douglas
I just try the the code that you suggested and got the following error:
"Compile error: Sub or Function not defined" and it reference
'ahtAddFilterItem'
I have reference to office. What can be going wrong?

 

[Douglas J. Steele]

Sounds as though you didn't copy everything between Code Start and Code End
like I suggested.

The definition for ahtAddFilterItem is definitely there.

 

[N.Ordiers]

Douglas here is the code that produced the error:
Function TestIt()
Dim strFilter As String
Dim strInputFileName As String

strFilter = ahtAddFilterItem(strFilter, "Word Files (*.DOC)", "*.DOT")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
End Function
The only change I made, was to the filter from Excel to Word
Any suggestion will be appreciated.

 

[Douglas J. Steele]

Did you copy all of the code in the shaded area between Code Start and Code
End into a module? What did you name the module when you saved it?

 

[N.Ordiers]

Will check the code against what it is posted and I am sure I will find my
mistake otherwise I will get to you