How can I use a Browse Window in VBA?

[newboy22]

I want to be able to import data from text files of
unknown names, does anyone have a simple example of how
to use VBA for Access to open a Browse window and assign
a string to the selected file name please?

 

[mp9]

I've just recently posted a solution to the same question in another thread which you can find at thread705-394632

http://www22.brinkster.com/accessory/

 

[newboy22]

Thanks, this works great but it is much longer than I had expected, I am only looking for .TXT files in a folder off the route of my C drive.
I assume that this is a routine that may do much more, is there any code that I can remove?

 

[mincefish]

This will give you an 'Open File' dialogue box, and parses the file name and path into a ByRef string.

I must say, if its any good, then I can't take credit for it - I'm pretty sure I found the answer on Tek-Tips too!

Public Function fncFindFile(ByRef strPath As String, Optional ByRef strType As String) As Boolean
On Error GoTo ERRHANDLE

    Select Case strType
        Case "csv"
            strType = "CSV (Comma Delimited) (*.csv)" + Chr$(0) + "*.csv" + Chr$(0) + "All Files (*.*)" + Chr$(0) + "*.*" + Chr$(0)
        Case "xls"
            strType = "Microsoft Excel Workbook (*.xls)" + Chr$(0) + "*.xls" + Chr$(0) + "Text Files (*.txt)" + Chr$(0) + "*.txt" + Chr$(0) + "All Files (*.*)" + Chr$(0) + "*.*" + Chr$(0)
        Case "txt"
            strType = "Text Files (*.txt)" + Chr$(0) + "*.txt" + Chr$(0) + "All Files (*.*)" + Chr$(0) + "*.*" + Chr$(0)
        Case Else
            strType = "All Files (*.*)" + Chr$(0) + "*.*" + Chr$(0)
    End Select

    If IsNull(strPath) Then strPath = "V:\DMT\"
    If strPath = "" Then strPath = "V:\DMT\"
    
    Dim OFName As OPENFILENAME
    OFName.lStructSize = Len(OFName)
    'Set the parent window
    'OFName.hwndOwner = Me.hwnd
    'Set the application's instance
    'OFName.hInstance = App.hInstance
    'Select a filter
    OFName.lpstrFilter = strType
    'create a buffer for the file
    OFName.lpstrFile = Space$(254)
    'set the maximum length of a returned file
    OFName.nMaxFile = 255
    'Create a buffer for the file title
    OFName.lpstrFileTitle = Space$(254)
    'Set the maximum length of a returned file title
    OFName.nMaxFileTitle = 255
    'Set the initial directory
    OFName.lpstrInitialDir = strPath
    'Set the title
    OFName.lpstrTitle = "Import Excel File - New London Aircode"
    'No flags
    OFName.flags = 0

    'Show the 'Open File'-dialog
    If GetOpenFileName(OFName) Then
        strPath = Trim$(OFName.lpstrFile)
        fncFindFile = True
    Else
        fncFindFile = False
        'MsgBox "Cancel was pressed"
    End If

ERREXIT:
    Exit Function
    
ERRHANDLE:
    MsgBox Err.Description, vbOKOnly, "Procedure: fncFindFile"
    fncFindFile = False
    Resume ERREXIT

End Function

There are a couple of bits which are specific to my location, but its pretty easily configurable...

HTH

Mincefish