file browse button on a form

1,923

Recognized Expert Top Contributor

Hello,

I have been working on a project in Access 2003, that requires an excel file to be selected (via browsing through folders, then selecting a file), the selected file name will then appear in a text box.

This will all take place on a form.

I had no idea browsing to a file would be so complicated. After many hours of searching I think I have found the information I need but unsure as to how to make it actually work. http://www.mvps.org/access/api/api0001.htm

Being fairly new to Access still I have no clue as to how to implement this code, or how to connect it to a button on a form etc.

I would guess that a module will need to be created then a specific function in the code be called but that is just guesswork.

Any help would be appreciated.

Without actually implementing the code that is posted on the web site, I could not tell you what to do for sure. The links I am providing below have working mdb files with the browse function that you can download. The source code is provided with the download and is much easier to implement.

Browse and Open Files Database
http://www.utterangel.com/utterangel.aspx#8

File Open Dialog Boxes without ActiveX
http://www.fabalou.com/Access/Module...pen_dialog.asp

Let me know if you have any problems.

Jul 24 '07 #2

New Member

Thanks!

I'll check them out and get back to you.

Jul 24 '07 #3

New Member

So I think the second example you sent me will work nicely

File Open Dialog Boxes without ActiveX
http://www.fabalou.com/Access/Modul...open_dialog.asp

but I ran into a small problem.

If I click on the "Please Select an Excle File" browse button first I get this error:

Invalid use of Null (Error 94)

and the following is what comes up with the Debug:

Me.txtXLFIle.Value = FindFile(Me.txtDir.Value, "Please Select an Excel File", "Excel Files", "*.xl?")

The strange thing is that when I select the first button "Please Select a Directory" it works fine and then if I select the "Please Select an Excel File" after, it works like it should.

Any thoughts?

Jul 24 '07 #4

1,923

Recognized Expert Top Contributor

So I think the second example you sent me will work nicely

File Open Dialog Boxes without ActiveX
http://www.fabalou.com/Access/Modul...open_dialog.asp

but I ran into a small problem.

If I click on the "Please Select an Excle File" browse button first I get this error:

Invalid use of Null (Error 94)

and the following is what comes up with the Debug:

Me.txtXLFIle.Value = FindFile(Me.txtDir.Value, "Please Select an Excel File", "Excel Files", "*.xl?")

The strange thing is that when I select the first button "Please Select a Directory" it works fine and then if I select the "Please Select an Excel File" after, it works like it should.

Any thoughts?

Just a guess....looks like a typo....if this is not it, let me know and I will download the mdb.

change this: >>> Me.txtXLFIle.Value

to this: >>>>>>> Me.txtXLFile.Value

Jul 24 '07 #5

New Member

Just a guess....looks like a typo....if this is not it, let me know and I will download the mdb.

change this: >>> Me.txtXLFIle.Value

to this: >>>>>>> Me.txtXLFile.Value

That would make sense, but it is actually Me.txtXLF(I - this is a capital i that looks exactly like a lower case L in this font )le

In the debug screen when I mouse over this line of code that is highlighted

Me.txtXLFIle.Value = FindFile(Me.txtDir.Value, "Please Select an Excel File", "Excel Files", "*.xl?")
there is a little tooltip pop up that says " Me.txtXLFIle.Value = Null "
I don't know if that helps at all.

Please let me know if you get around to checking it out further.

Thanks again!

Jul 25 '07 #6

1,923

Recognized Expert Top Contributor

That would make sense, but it is actually Me.txtXLF(I - this is a capital i that looks exactly like a lower case L in this font )le

In the debug screen when I mouse over this line of code that is highlighted

Me.txtXLFIle.Value = FindFile(Me.txtDir.Value, "Please Select an Excel File", "Excel Files", "*.xl?")
there is a little tooltip pop up that says " Me.txtXLFIle.Value = Null "
I don't know if that helps at all.

Please let me know if you get around to checking it out further.

Thanks again!

Good write-up. I don't think I need to download the file. Try this:

Change this:
Me.txtXLFIle.Value = FindFile(Me.txtDir.Value, "Please Select an Excel File", "Excel Files", "*.xl?")

To This:

If Not IsNull(Me.txtXLFIle) Then
Me.txtXLFIle.Value = FindFile(Me.txtDir.Value, "Please Select an Excel File", "Excel Files", "*.xl?")
End If

Let me know what happens.

Jul 25 '07 #7

New Member

I changed out the existing code with your code and there was no error but not browse either.

Right when I changed out the code this line was highlighted

If Not IsNull(Me.txtXLFIle) Then

Jul 25 '07 #8

New Member

Once again even with your new code if I click on the "Please Select A Directory" button first then hit cancel then press the "Please Select An Excel File" button the browse function works.

Pretty strange.

FYI

On the form there is 3 buttons and 3 text boxes one for browsing a directory one to select a database and one to select an excel file.

I will make a new form with just the browse to select an excel file and see what happens.

Jul 25 '07 #9

New Member

So I made a new form (in the same db as the example) with the a browse button and text box both named the same as in the example form then copied the same onclick function to the new form and got this error.

Compile Error Method or data member not found (Error 461)

Me.txtXLFIle.Value = FindFile(Me.txtDir.Value, "Please Select an Excel File", "Excel Files", "*.xl?")

with this part highlighted txtDir.

I did the exact same steps but for the browse to a directory button/function and it worked fine.

Jul 25 '07 #10

New Member

So.... I think I was able to figure out a way to have a file browse button on a form select a file and put the file path into a text box by doing the following (for anyone else who may run into this problem.)

Thanks for all the assistance puppydogbuddy I really do appreciate your help.

1. Create a new Module in Access name it what ever you want and paste the following code from http://www.mvps.org/access/api/api0001.htm

Expand|Select|Wrap|Line Numbers

 
'***************** Code Start **************

'This code was originally written by Ken Getz.

'It is not to be altered or distributed,

'except as part of an application.

'You are free to use it in any application,

'provided the copyright notice is left unchanged.

'

' Code courtesy of:

' Microsoft Access 95 How-To

' Ken Getz and Paul Litwin

' Waite Group Press, 1996
 
Type tagOPENFILENAME

    lStructSize As Long

    hwndOwner As Long

    hInstance As Long

    strFilter As String

    strCustomFilter As String

    nMaxCustFilter As Long

    nFilterIndex As Long

    strFile As String

    nMaxFile As Long

    strFileTitle As String

    nMaxFileTitle As Long

    strInitialDir As String

    strTitle As String

    Flags As Long

    nFileOffset As Integer

    nFileExtension As Integer

    strDefExt As String

    lCustData As Long

    lpfnHook As Long

    lpTemplateName As String

End Type
 
Declare Function aht_apiGetOpenFileName Lib "comdlg32.dll" _

    Alias "GetOpenFileNameA" (OFN As tagOPENFILENAME) As Boolean
 
Declare Function aht_apiGetSaveFileName Lib "comdlg32.dll" _

    Alias "GetSaveFileNameA" (OFN As tagOPENFILENAME) As Boolean

Declare Function CommDlgExtendedError Lib "comdlg32.dll" () As Long
 
Global Const ahtOFN_READONLY = &H1

Global Const ahtOFN_OVERWRITEPROMPT = &H2

Global Const ahtOFN_HIDEREADONLY = &H4

Global Const ahtOFN_NOCHANGEDIR = &H8

Global Const ahtOFN_SHOWHELP = &H10

' You won't use these.

'Global Const ahtOFN_ENABLEHOOK = &H20

'Global Const ahtOFN_ENABLETEMPLATE = &H40

'Global Const ahtOFN_ENABLETEMPLATEHANDLE = &H80

Global Const ahtOFN_NOVALIDATE = &H100

Global Const ahtOFN_ALLOWMULTISELECT = &H200

Global Const ahtOFN_EXTENSIONDIFFERENT = &H400

Global Const ahtOFN_PATHMUSTEXIST = &H800

Global Const ahtOFN_FILEMUSTEXIST = &H1000

Global Const ahtOFN_CREATEPROMPT = &H2000

Global Const ahtOFN_SHAREAWARE = &H4000

Global Const ahtOFN_NOREADONLYRETURN = &H8000

Global Const ahtOFN_NOTESTFILECREATE = &H10000

Global Const ahtOFN_NONETWORKBUTTON = &H20000

Global Const ahtOFN_NOLONGNAMES = &H40000

' New for Windows 95

Global Const ahtOFN_EXPLORER = &H80000

Global Const ahtOFN_NODEREFERENCELINKS = &H100000

Global Const ahtOFN_LONGNAMES = &H200000
 
Function TestIt()

    Dim strFilter As String

    Dim lngFlags As Long

    strFilter = ahtAddFilterItem(strFilter, "Access Files (*.mda, *.mdb)", _

                    "*.MDA;*.MDB")

    strFilter = ahtAddFilterItem(strFilter, "dBASE Files (*.dbf)", "*.DBF")

    strFilter = ahtAddFilterItem(strFilter, "Text Files (*.txt)", "*.TXT")

    strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")

    MsgBox "You selected: " & ahtCommonFileOpenSave(InitialDir:="C:\", _

        Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, _

        DialogTitle:="Hello! Open Me!")

    ' Since you passed in a variable for lngFlags,

    ' the function places the output flags value in the variable.

    Debug.Print Hex(lngFlags)

End Function
 
Function GetOpenFile(Optional varDirectory As Variant, _

    Optional varTitleForDialog As Variant) As Variant

' Here's an example that gets an Access database name.

Dim strFilter As String

Dim lngFlags As Long

Dim varFileName As Variant

' Specify that the chosen file must already exist,

' don't change directories when you're done

' Also, don't bother displaying

' the read-only box. It'll only confuse people.

    lngFlags = ahtOFN_FILEMUSTEXIST Or _

                ahtOFN_HIDEREADONLY Or ahtOFN_NOCHANGEDIR

    If IsMissing(varDirectory) Then

        varDirectory = ""

    End If

    If IsMissing(varTitleForDialog) Then

        varTitleForDialog = ""

    End If
 
    ' Define the filter string and allocate space in the "c"

    ' string Duplicate this line with changes as necessary for

    ' more file templates.

    strFilter = ahtAddFilterItem(strFilter, _

                "Access (*.mdb)", "*.MDB;*.MDA")

    ' Now actually call to get the file name.

    varFileName = ahtCommonFileOpenSave( _

                    OpenFile:=True, _

                    InitialDir:=varDirectory, _

                    Filter:=strFilter, _

                    Flags:=lngFlags, _

                    DialogTitle:=varTitleForDialog)

    If Not IsNull(varFileName) Then

        varFileName = TrimNull(varFileName)

    End If

    GetOpenFile = varFileName

End Function
 
Function ahtCommonFileOpenSave( _

            Optional ByRef Flags As Variant, _

            Optional ByVal InitialDir As Variant, _

            Optional ByVal Filter As Variant, _

            Optional ByVal FilterIndex As Variant, _

            Optional ByVal DefaultExt As Variant, _

            Optional ByVal FileName As Variant, _

            Optional ByVal DialogTitle As Variant, _

            Optional ByVal hwnd As Variant, _

            Optional ByVal OpenFile As Variant) As Variant

' This is the entry point you'll use to call the common

' file open/save dialog. The parameters are listed

' below, and all are optional.

'

' In:

' Flags: one or more of the ahtOFN_* constants, OR'd together.

' InitialDir: the directory in which to first look

' Filter: a set of file filters, set up by calling

' AddFilterItem. See examples.

' FilterIndex: 1-based integer indicating which filter

' set to use, by default (1 if unspecified)

' DefaultExt: Extension to use if the user doesn't enter one.

' Only useful on file saves.

' FileName: Default value for the file name text box.

' DialogTitle: Title for the dialog.

' hWnd: parent window handle

' OpenFile: Boolean(True=Open File/False=Save As)

' Out:

' Return Value: Either Null or the selected filename

Dim OFN As tagOPENFILENAME

Dim strFileName As String

Dim strFileTitle As String

Dim fResult As Boolean

    ' Give the dialog a caption title.

    If IsMissing(InitialDir) Then InitialDir = CurDir

    If IsMissing(Filter) Then Filter = ""

    If IsMissing(FilterIndex) Then FilterIndex = 1

    If IsMissing(Flags) Then Flags = 0&

    If IsMissing(DefaultExt) Then DefaultExt = ""

    If IsMissing(FileName) Then FileName = ""

    If IsMissing(DialogTitle) Then DialogTitle = ""

    If IsMissing(hwnd) Then hwnd = Application.hWndAccessApp

    If IsMissing(OpenFile) Then OpenFile = True

    ' Allocate string space for the returned strings.

    strFileName = Left(FileName & String(256, 0), 256)

    strFileTitle = String(256, 0)

    ' Set up the data structure before you call the function

    With OFN

        .lStructSize = Len(OFN)

        .hwndOwner = hwnd

        .strFilter = Filter

        .nFilterIndex = FilterIndex

        .strFile = strFileName

        .nMaxFile = Len(strFileName)

        .strFileTitle = strFileTitle

        .nMaxFileTitle = Len(strFileTitle)

        .strTitle = DialogTitle

        .Flags = Flags

        .strDefExt = DefaultExt

        .strInitialDir = InitialDir

        ' Didn't think most people would want to deal with

        ' these options.

        .hInstance = 0

        '.strCustomFilter = ""

        '.nMaxCustFilter = 0

        .lpfnHook = 0

        'New for NT 4.0

        .strCustomFilter = String(255, 0)

        .nMaxCustFilter = 255

    End With

    ' This will pass the desired data structure to the

    ' Windows API, which will in turn it uses to display

    ' the Open/Save As Dialog.

    If OpenFile Then

        fResult = aht_apiGetOpenFileName(OFN)

    Else

        fResult = aht_apiGetSaveFileName(OFN)

    End If
 
    ' The function call filled in the strFileTitle member

    ' of the structure. You'll have to write special code

    ' to retrieve that if you're interested.

    If fResult Then

        ' You might care to check the Flags member of the

        ' structure to get information about the chosen file.

        ' In this example, if you bothered to pass in a

        ' value for Flags, we'll fill it in with the outgoing

        ' Flags value.

        If Not IsMissing(Flags) Then Flags = OFN.Flags

        ahtCommonFileOpenSave = TrimNull(OFN.strFile)

    Else

        ahtCommonFileOpenSave = vbNullString

    End If

End Function
 
Function ahtAddFilterItem(strFilter As String, _

    strDescription As String, Optional varItem As Variant) As String

' Tack a new chunk onto the file filter.

' That is, take the old value, stick onto it the description,

' (like "Databases"), a null character, the skeleton

' (like "*.mdb;*.mda") and a final null character.
 
    If IsMissing(varItem) Then varItem = "*.*"

    ahtAddFilterItem = strFilter & _

                strDescription & vbNullChar & _

                varItem & vbNullChar

End Function
 
Private Function TrimNull(ByVal strItem As String) As String

Dim intPos As Integer

    intPos = InStr(strItem, vbNullChar)

    If intPos > 0 Then

        TrimNull = Left(strItem, intPos - 1)

    Else

        TrimNull = strItem

    End If

End Function

'************** Code End *****************

2. Make a form and name it what ever you want. On the form make a text box and name it txtfilename then make a button on the form and name it TestIt()
On this buttons OnClick event paste the following code

Expand|Select|Wrap|Line Numbers

 
Private Sub TestIt___Click()

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)

Me.txtfilename.Value = ahtCommonFileOpenSave( _

                Filter:=strFilter, OpenFile:=True, _

                DialogTitle:="Please select an input file...", _

                Flags:=ahtOFN_HIDEREADONLY)

End Sub

There may be a better way to display the file path/name but I don't know how to do it. If someone else has better code please share.

Hope this helps.

Jul 25 '07 #11

2,653

Recognized Expert Specialist

Sure, there is a simpler way.
If you have Office 2003 you may use the following code.

Expand|Select|Wrap|Line Numbers

 
Private Sub btnPickFile_Click()
 
    Dim dlgPickFiles As Office.FileDialog

    Dim strList As String
 
    Set dlgPickFiles = Application.FileDialog(msoFileDialogFilePicker)
 
    With dlgPickFiles

        .AllowMultiSelect = False

        With .Filters

            .Clear

            .Add "Excel files (*.xls)", "*.xls"

        End With

        .Show

        Me.txtFileName = .SelectedItems(1)

    End With
 
    Set dlgPickFiles = Nothing
 
End Sub

Be sure "Microsoft Office 11.0 Object Library" is referenced (Tools -> References)

Anyway you've done nice job.

Jul 25 '07 #12

New Member

Sure, there is a simpler way.
If you have Office 2003 you may use the following code.

Expand|Select|Wrap|Line Numbers

Private Sub btnPickFile_Click()

    Dim dlgPickFiles As Office.FileDialog

    Dim strList As String

    Set dlgPickFiles = Application.FileDialog(msoFileDialogFilePicker)

    With dlgPickFiles

        .AllowMultiSelect = False

        With .Filters

            .Clear

            .Add "Excel files (*.xls)", "*.xls"

        End With

        .Show

        Me.txtFileName = .SelectedItems(1)

    End With

    Set dlgPickFiles = Nothing

End Sub

Be sure "Microsoft Office 11.0 Object Library" is referenced (Tools -> References)

Anyway you've done nice job.

Wow.

That is much easier and exactly what I was looking for, thanks!

Is there a way to start off the browse in a specific directory?

Jul 25 '07 #13

2,653

Recognized Expert Specialist

Wow.

That is much easier and exactly what I was looking for, thanks!

Is there a way to start off the browse in a specific directory?

Of course.

There are many useful properties and methods in Office.FileDialog. You can easily view it in VBA window in Object Browser. Naming is quite intuitive, help available as well, what else ... ah the water is fine :)

The property you need is FileDialog.InitialFileName.

Good luck.

Jul 25 '07 #14

1,923

Recognized Expert Top Contributor

Fish,
That is good to know. I have Access 2000 and am not familiar with that particular FileDialog control.

jaccess,
Was out of the office - just got back on-line. Glad you got your problem resolved with a better FileDialog method with more efficient code.

Jul 25 '07 #15

2,653

Recognized Expert Specialist

Fish,
That is good to know. I have Access 2000 and am not familiar with that particular FileDialog control.

jaccess,
Was out of the office - just got back on-line. Glad you got your problem resolved with a better FileDialog method with more efficient code.

Hi, PDB.

I don't remember exactly how and now have no opportunity to check it, but I've once implemented this in Office 2000 after having trouble to move Access 2003 db with FileDialog to computer where Office 2000 was installed. Not sure maybe Office 2000 has a similar class or maybe some Office 2000 application does (maybe Excel).

Jul 25 '07 #16

1,923

Recognized Expert Top Contributor

Hi, PDB.

I don't remember exactly how and now have no opportunity to check it, but I've once implemented this in Office 2000 after having trouble to move Access 2003 db with FileDialog to computer where Office 2000 was installed. Not sure maybe Office 2000 has a similar class or maybe some Office 2000 application does (maybe Excel).

Hi Fish,

Thanks for the good info. Now if I could only find the time.......

PDB

Jul 25 '07 #17