Need to open a file/path select dialog - Access 2003

  • Thread starter Thread starter Dennis
  • Start date Start date
D

Dennis

I need to setup a button control that will open a file/path select dialog.
The user needs to be able to browse to their chosen file, click on it, and
return the path and filename to VBA. Can someone point me in the direction of
that methodology? I've done it once before a couple of years ago, but I can't
find the application where that code lives.

Thanks a bunch!
 
Hi Dennis,

Here is a function I wrote for selecting an Access database. You could
adapt it to your needs. While in the VBA Editor, add "Microsoft Office nn.n
Object Library" to your references (Tools menu, References). It returns
either the selected file name or a zero-length string ("") if none selected.

========================================

Public Function SelectAccessDatabase() As String

' Uses Microsoft Office nn.n Object Library

Dim fd As FileDialog

Set fd = FileDialog(msoFileDialogOpen)
With fd
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Microsoft Office Access", _
"*.accdb; *.mdb; *.adp; *.accda; *.mde; *.accde; *.ade"
.Filters.Add "All Files", "*.*"
.FilterIndex = 1
.Title = "Select Access Database"
If .Show Then
SelectAccessDatabase = .SelectedItems(1)
Else
SelectAccessDatabase = vbNullString
End If
End With

End Function

========================================

Clifford Bass
 
Hi Mark,

FileDialog allows for multiple selections also. I use both methods,
but am finding that I prefer the FileDialog object, except for a few
situations where FileDialog does not provide for exactly what I need. Adding
the reference to Microsoft Office probably does not add anything overheadwise
because Access is probably already using it for its own file open and save
dialogs. They both call out to the Common Dialog Control so those DLLs have
to be loaded regardless of which you use. FileDialog eliminates the need for
all that extra code.

My $0.02 for what it is worth.

Clifford Bass
 
Will the OpenDialog code provided here by Clifford Bass also work in Windows
Vista? I copied and pasted this code into my access database running on
windows xp and it works great. just wondering if it works in Vista as well.
 
Hi,

The FileDialog is part of Access/Office, which I presume runs fine on
Vista, so I would expect it to work, but cannot test it due to not having
Vista yet.

Clifford Bass
 
While FileDialog works as a replacement for FileOpen, I believe there are
bugs with it for FileSave.

I think Allen Browne has documented them, but I can't find them on his site.
 
Hi Douglas,

I have not encountered any in my use that I can recall. That is not to
say there are not some. If you do find any information, please let me know.

Thanks,

Clifford Bass
 
Hi Douglas,

Lovely! Not! It sounds like that problem extends to any number of
other libraries. Thanks Microsoft! Not! Forewarned is forearmed.

Thanks,

Clifford Bass
 
Back
Top