Browse For File

[BarneyF]

I would like to browse (e.g. from a buton on an Access 2K form) for a file anywhere on the hd and when I click on it to assign its full name and path to a variable.

I have been struggling with this for a few days but have stumbled against a "near solution" at the following KB article
http://vbaexpress.com/kb/getarticle.php?kb_id=284

This (thanks a million) does exactly what I need but I would need to be able to select a file name within the folder as well as (or rather, instead of) the folder name.

Does anyone have any straightforward code that does this for me.

Regards Barney

[mvidas]

Hi Barney,

Welcome to VBAX!

Put the following in a module, it should do what you need!Option Explicit
Public Declare Function GetOpenFileNameB Lib "comdlg32.dll" Alias "GetOpenFileNameA" _
(pOpenfilename As OPENFILENAME) As Long
Public 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 BarneyFExample()
Dim vFile As String
vFile = GetOpenFilename("Excel Files,*.xls;*.csv,All Files,*.*")
If vFile <> "" Then MsgBox vFile
End Sub

Public Function GetOpenFilename(Optional ByVal vFileFilter As String) As String
Dim ofn As OPENFILENAME, retVal As Long
ofn.lStructSize = Len(ofn)
ofn.hwndOwner = 0
ofn.hInstance = 0
If vFileFilter = "" Then
ofn.lpstrFilter = "All Files (*.*)" & Chr(0) & "*.*"
Else
ofn.lpstrFilter = Replace(vFileFilter, ",", Chr$(0))
End If
ofn.lpstrFile = Space$(254)
ofn.nMaxFile = 255
ofn.lpstrFileTitle = Space$(254)
ofn.nMaxFileTitle = 255
ofn.lpstrInitialDir = CurDir
ofn.lpstrTitle = "Select File"
ofn.flags = 0
retVal = GetOpenFileNameB(ofn)
If retVal Then GetOpenFilename = Trim$(ofn.lpstrFile)
End FunctionMatt

[mvidas]

Please note that I changed the code slightly above to make it a little easier to use

[BarneyF]

Matt

Thanks a million for your very prompt attention. The code did exactly what

I wanted. With a little tweaking it'll allow me to implement a mod for

users to attach files from anywhere on their hdd to eMails generated

directly from their Access database without recourse to Outlook or OE. I'm

extremely grateful and delighted to have come across VbaExpress. I'm a

convert. I'll reply to the thread and mark it closed when I'm back in work

tomorrow.

Regards

Barney

[mvidas]

Glad to help, Barney! I was playing around with the API yesterday, and changed my GetOpenFilename function to:Public Function GetOpenFilename(Optional ByVal vFileFilter As String, Optional ByVal _
vWindowTitle As String, Optional ByVal vInitialDir As String, Optional ByVal _
vInitialFileName As String) As String
Dim ofn As OPENFILENAME, retVal As Long
ofn.lStructSize = Len(ofn)
ofn.hwndOwner = 0
ofn.hInstance = 0
ofn.lpstrFile = IIf(vInitialDir = "", Space$(254), vInitialDir)
ofn.lpstrInitialDir = IIf(vWindowTitle = "", CurDir, vInitialDir)
ofn.lpstrTitle = IIf(vWindowTitle = "", "Select File", vWindowTitle)
ofn.lpstrFilter = IIf(vFileFilter = "", "All Files (*.*)" & Chr(0) & "*.*", _
Replace(vFileFilter, ",", Chr$(0)))
ofn.nMaxFile = 255
ofn.lpstrFileTitle = Space$(254)
ofn.nMaxFileTitle = 255
ofn.flags = 0
retVal = GetOpenFileNameB(ofn)
If retVal Then GetOpenFilename = Trim$(ofn.lpstrFile)
End FunctionSo you can specify not only the filter, but also the title, initial directory, and initial filename.

If you lookup the common dialog api ("comdlg32.dll" - MSDN (http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnarvb4/html/msdn_comdlg.asp) is a great resource for this) you can see how you can access just about any function. Making a slight change to my routine above (literally changing GetOpenFileNameA to GetSaveFileNameA) will change the button from 'Open' to 'Save', and you can still use the same parameters.

Let me know if you need anything else! And again, welcome to VBAX :)
Matt