Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Nov 2002

    Unhappy Unanswered: API File Open/Save dialog box - Again?!?!?!?!

    I am really sorry to bore everybody, but I have been struggling with this for the past couple of months (I have given up a few times in between).

    I am working on a very simple database for myself. All of the stuff is very basic except for one function which I really need but I can't get it to work. It has been covered here many times but I just don't get it.

    What I need is:
    - A Command Button which opens API Open/Save with ‘multiple’ or at least ‘single’ File selection option.

    - These Files (Links) are then saved in a List Box or Combo Box

    - When I click on one of the Files (Links) in the List /Combo Box, it should open up in the correct application. (Tried 'ShellExecute' but couldn't get it to work)

    - If at all possible, a 'delete' function to get rid of Files (links) that are out of date.

    This is it. Probably nothing complicated for most of you, but caused me endless headaches and sleepless nights.

    Attached a db file so you can see how far I've got. Please keep in mind that I have no clue about VBA programming. You really need to explain it to me step by step.


    Last edited by Hight; 05-12-03 at 11:28.

  2. #2
    Join Date
    Oct 2002
    Northampton, England
    Have a look at the Dev Ashish site.

  3. #3
    Join Date
    Nov 2002

    Tried it

    Hi Tanis,

    I've tried this one many times before. All previous posts about this subject suggested this link but I can't get it to work. Only thing I ever got to work in VBA is the one I have used in my attachment and that was only because somebody was nice enough to explain Step-By-Step what to do. I sit in front of the codes and haven't got a clue what any of it means. Don’t even know what exactly a module or a class module is and how they are related to the 'Click' function.

    Thx for the tip though


  4. #4
    Join Date
    Nov 2002

    Question Step further with API Open/Save - Help

    - Please Help -

    Here is the breakdown:

    ******DESCRIPTION BEGIN***********

    Command Button (cmdOpenSaveDialog)
    List Box (listboxFiles)

    **** Behind the Command Button I've put: ****

    Private Sub cmdOpenSaveDialog_Click()
    Dim strFilter As String
    Dim strInputFileName As String

    strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)")
    strInputFileName = ahtCommonFileOpenSave( _
    Filter:=strFilter, OpenFile:=True, _
    DialogTitle:="Please select an input file...", _
    End Sub

    **** In a Module I copied: ****

    Option Compare Database

    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)", _
    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 _
    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, _
    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 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)
    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)
    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)
    TrimNull = strItem
    End If
    End Function

    ******END DESCRIPTION*******

    What happens?

    I press the Command Button (cmdOpenSaveDialog) and the Windows Open/Save window opens. I select a file, press 'OPEN' and nothing shows up in my List Box (lstbxFile).

    Any thoughts? File attached!

    Thx in advance

    Last edited by Hight; 05-12-03 at 11:29.

  5. #5
    Join Date
    Aug 2002
    Northampton, England
    I tried to open your zip file, but I think it must be Access2000. So, here is a step by step guide to use the code as I have used it in Access 97

    1. Open your db in design mode
    2. Create a new module by clicking on the module tab then New.
    3. Copy and paste the code from the dev ashish site in its entirety into the module.
    4. Create an unbound form with one text box and three command buttons.
    5. One button will open the file. One button will cancel the operation and one button will browse.
    6. Place this code behind the open file button.

    Private Sub cmdOpenFile_Click()
    Application.FollowHyperlink (Me!YourTextBoxName)
    End Sub

    7. Place this code behind the cancel button

    Private Sub cmdCancel_Click()
    On Error GoTo Err_cmdCancel_Click

    MsgBox "Action was cancelled."

    Exit Sub

    MsgBox Error$
    Resume Exit_cmdCancel_Click

    End Sub

    8. Place this code behind your browse button.

    Private Sub cmdBrowse_Click()
    On Error GoTo err_cmdBrowse

    Me![YourTextBoxName] = GetOpenFile_CLT("C:\", "Select a File To Open")

    Exit Sub

    MsgBox Error$
    Resume exit_cmdBrowse

    End Sub

    I have used a text box instead of a list box or combo box.

    Hope this helps


  6. #6
    Join Date
    May 2002
    Atlanta, GA
    Hey Alex,

    Following davids advice here is a simple app for you. I think it does everything asked. Let me know if you have any questions.

    Later, Kal
    Attached Files Attached Files

  7. #7
    Join Date
    Nov 2002


    Hi David
    Hi Kal,

    I had a long day today so I didn't have a chance to test it out yet but I will do it as soon as I get back in tomorrow evening.

    In advance thanks for your time and I will let you know how I got on.

    Many Thx

    Talk Later


  8. #8
    Join Date
    May 2002
    Atlanta, GA

    Have you made any progress???

  9. #9
    Join Date
    Nov 2002
    Well, yes and no. I tried David's suggestion (by the way, I've got Access 2002. Student Pack. You know - CHEAP) and I got an error message saying:
    Compile Error: Ambiguous Name detection: GetOpenFile

    So I thought I must have done something wrong again and tried yours. Now that worked like a dream and is exactly what I wanted.
    Problem started though when I tried to copy the codes and things into my db. I am now getting exactly the same error message as I did with David's.

    Compile Error: Ambiguous Name detection: GetOpenFile


    Any thoughts??



  10. #10
    Join Date
    May 2002
    Atlanta, GA

    Well who's code are you copying. Try and just import instead of copying the code. Import the module, form, and table then do some altering. Also, make sure your references in vb are set correctly.

    If this doesn't work, send the file as a zip file and I'll look at it.


  11. #11
    Join Date
    Nov 2002
    Tried it with importing and it doesn't make any difference. As soon as I hit "Browse" the previous mentioned error message shows up.

    Attache my db. If you look in the History form, that is where it suppose to go. You will also find your imported files.

    Thx for looking at it.


    PS: I only attached an excerpt because of size. If you need the whole db let me know.
    Last edited by Hight; 03-19-03 at 11:34.

  12. #12
    Join Date
    May 2002
    Atlanta, GA
    Hey Alex,

    Here is your file back. I got it to work but had to remove some of your references. I'm not sure why, but something was confusing vb. Look through it and see what references were removed. You can add them back and then test each time to see which one causes the error. Sometimes just changing the order of importance can fix the problem so you may be able to add them back with no problem.

    Later, Kal
    Attached Files Attached Files

  13. #13
    Join Date
    Nov 2002
    Hi Kal,

    Many thx. OpenSaveFile is working like a treat now.
    Have another problem with the OpenSaveFile though which I hadn't anticipated. I need to have MANY "Histories" (which includes the OpenFile Txtbox) under ONE "Company" (This is the main Form of which the "History" Form is the subform). What is happening at the moment is that I can create multiple "History" records except for the paths saved in the OpenSaveFile. The Paths in there stay the same, whatever "History" Record I call up.

    Hope I can work it out soon.

    Many thx for your input Kal, great help.


  14. #14
    Join Date
    May 2002
    Atlanta, GA
    Your welcome

  15. #15
    Join Date
    Feb 2003

    i usually just rename dev's function

    i usually just rename dev's function to 'GetOpenFolder'

    sortof like the GetOpenFile function in Excel VBA.. (why arent these built into Access again??)
    Access 2002 ADP Rocks my World

    Long live SQL Server and 64bit Windows!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts