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

[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!

 

[Clifford Bass]

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

 

[Mark Andrews]

I would use this
http://www.mvps.org/access/api/api0001.htm

over the filedialog. Cuts down on relying on a reference to Microsoft
Office and does the same job.

HTH,
Mark
RPT Software
http://www.rptsoftware.com

PS: It can be tweaked to allow multiple selection as well.

 

[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

 

[CrazyAccessProgrammer]

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.

 

[Clifford Bass]

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

 

[Douglas J. Steele]

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.

 

[Clifford Bass]

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

 

[Douglas J. Steele]

If nothing else, the problem outlined in
http://www.allenbrowne.com/bug-17.html can affect your ability to use a
reference to the Office object.

 

[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