Results 1 to 2 of 2

Thread: attaching files

  1. #1
    Join Date
    Apr 2004
    Location
    ny, ny
    Posts
    224

    Unanswered: attaching files

    I need a field so that I can attach excel files to it by path name. Is there a user friendly way to do it. Presently I have done the hyperlinking method but its too tedious to put hyperlink the path
    I have also tried the OLE Object method, but it inserts the whole excel sheet in there
    Am looking for something like click browse, takes me to the file and puts in the link in the field

    thank you

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    This may seem a little long winded but once it's in the use is rather simple. The code can be used to show a Open or Save dialog box from which you can select your file(s).

    Create a small Comand button on your form and locate it beside your textbox field which will contain the Excel path & file name. In the Caption property for the command button, enter two or three minus characters (i.e.: -- ).

    Now...Place the following code into a database code module:

    Code:
    Declare Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Boolean
    Declare Function GetSaveFileName Lib "comdlg32.dll" Alias "GetSaveFileNameA" (pOpenfilename As OPENFILENAME) As Boolean
    
    Type MSA_OPENFILENAME
    ' Filter string used for the File Open dialog filters.
    ' Use MSA_CreateFilterString() to create this.
    ' Default = All Files, *.*
        strFilter As String
    ' Initial Filter to display.
    ' Default = 1.
        lngFilterIndex As Long
    ' Initial directory for the dialog to open in.
    ' Default = Current working directory.
        strInitialDir As String
    ' Initial file name to populate the dialog with.
    ' Default = "".
        strInitialFile As String
        strDialogTitle As String
    ' Default extension to append to file if user didn't specify one.
    ' Default = System Values (Open File, Save File).
        strDefaultExtension As String
    ' Flags (see constant list) to be used.
    ' Default = no flags.
        lngFlags As Long
    ' Full path of file picked.  On OpenFile, if the user picks a
    ' nonexistent file, only the text in the "File Name" box is returned.
        strFullPathReturned As String
    ' File name of file picked.
        strFileNameReturned As String
    ' Offset in full path (strFullPathReturned) where the file name
    ' (strFileNameReturned) begins.
        intFileOffset As Integer
    ' Offset in full path (strFullPathReturned) where the file extension begins.
        intFileExtension As Integer
    End Type
    
    Const ALLFILES = "All Files"
    
    Type OPENFILENAME
        lStructSize As Long
        hWndOwner As Long
        hInstance As Long
        lpstrFilter As String
        lpstrCustomFilter As Long
        nMaxCustrFilter 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
        lCustrData As Long
        lpfnHook As Long
        lpTemplateName As Long
    End Type
    
    Const OFN_ALLOWMULTISELECT = &H200
    Const OFN_CREATEPROMPT = &H2000
    Const OFN_EXPLORER = &H80000
    Const OFN_FILEMUSTEXIST = &H1000
    Const OFN_HIDEREADONLY = &H4
    Const OFN_NOCHANGEDIR = &H8
    Const OFN_NODEREFERENCELINKS = &H100000
    Const OFN_NONETWORKBUTTON = &H20000
    Const OFN_NOREADONLYRETURN = &H8000
    Const OFN_NOVALIDATE = &H100
    Const OFN_OVERWRITEPROMPT = &H2
    Const OFN_PATHMUSTEXIST = &H800
    Const OFN_READONLY = &H1
    Const OFN_SHOWHELP = &H10
    
    
    'Procedure BrowseForFile (Dialog Box)
    Function BrowseForFile(strSearchPath) As String
        '---------------------------------------------------------------
        ' Displays the open file dialog box for the user to locate a File.
        ' Returns the full path to the selected file.
        '---------------------------------------------------------------
        Dim msaof As MSA_OPENFILENAME
        
        ' Set options for the dialog box.
        msaof.strDialogTitle = "Browse For File...."
        If strSearchPath = "" Then msaof.strInitialDir = "C:\" Else msaof.strInitialDir = strSearchPath
        msaof.strFilter = MSA_CreateFilterString("Excel Files (*.xl*)", "*.xl*", "All Files (*.*)", "*.*")
    
        ' Call the Open File dialog routine.
        MSA_GetOpenFileName msaof
        
        ' Return the path and file name.
        BrowseForFile = Trim(msaof.strFullPathReturned)
       
    End Function
    
    
    'procedure MSA_CreateFilterString
    Function MSA_CreateFilterString(ParamArray varFilt() As Variant) As String
        '-------------------------------------------------------------------
        ' Creates a filter string from the passed in arguments.
        ' Returns "" if no args are passed in.
        ' Expects an even number of args (filter name, extension), but
        ' if an odd number is passed in, it appends *.*
        '-------------------------------------------------------------------
        Dim strFilter As String
        Dim intRet As Integer
        Dim intNum As Integer
    
        intNum = UBound(varFilt)
        If (intNum <> -1) Then
            For intRet = 0 To intNum
                strFilter = strFilter & varFilt(intRet) & Chr$(0)
            Next
            If intNum Mod 2 = 0 Then
                strFilter = strFilter & "*.*" & Chr$(0)
            End If
            
            strFilter = strFilter & Chr$(0)
        Else
            strFilter = ""
        End If
    
        MSA_CreateFilterString = strFilter
    End Function
    
    'procedure MSA_GetOpenFileName
    Public Function MSA_GetOpenFileName(msaof As MSA_OPENFILENAME) As Integer
        '---------------------------------------------------------------
        ' Opens the file open dialog.
        '---------------------------------------------------------------
        Dim of As OPENFILENAME
        Dim intRet As Integer
    
        MSAOF_to_OF msaof, of
        intRet = GetOpenFileName(of)
        If intRet Then
            OF_to_MSAOF of, msaof
        End If
        MSA_GetOpenFileName = intRet
    
    End Function
    
    'procedure MSA_GetSaveFileName
    Public Function MSA_GetSaveFileName(msaof As MSA_OPENFILENAME) As Integer
        '------------------------------------------------------
        ' Opens the file save dialog.
        '------------------------------------------------------
        Dim of As OPENFILENAME
        Dim intRet As Integer
    
        MSAOF_to_OF msaof, of
        of.Flags = of.Flags Or OFN_HIDEREADONLY
        intRet = GetSaveFileName(of)
        If intRet Then
            OF_to_MSAOF of, msaof
        End If
        MSA_GetSaveFileName = intRet
    End Function
    
    'procedure MSAOF_to_OF
    Private Sub MSAOF_to_OF(msaof As MSA_OPENFILENAME, of As OPENFILENAME)
        '-----------------------------------------------------------------
        ' This sub converts from the friendly MSAccess structure to the
        '  win32 structure.
        '-----------------------------------------------------------------
        Dim strFile As String * 512
    
        ' Initialize some parts of the structure.
        of.hWndOwner = Application.hWndAccessApp
        of.hInstance = 0
        of.lpstrCustomFilter = 0
        of.nMaxCustrFilter = 0
        of.lpfnHook = 0
        of.lpTemplateName = 0
        of.lCustrData = 0
        
        If msaof.strFilter = "" Then
            of.lpstrFilter = MSA_CreateFilterString(ALLFILES)
        Else
            of.lpstrFilter = msaof.strFilter
        End If
        of.NFilterIndex = msaof.lngFilterIndex
        
        of.lpstrFile = msaof.strInitialFile _
                    & String$(512 - Len(msaof.strInitialFile), 0)
        of.nMaxFile = 511
    
        of.lpstrFileTitle = String$(512, 0)
        of.nMaxFileTitle = 511
    
        of.lpstrTitle = msaof.strDialogTitle
    
        of.lpstrInitialDir = msaof.strInitialDir
        
        of.lpstrDefExt = msaof.strDefaultExtension
    
        of.Flags = msaof.lngFlags
        
        of.lStructSize = Len(of)
    End Sub
    
    'procedure OF_to_MSAOF
    Private Sub OF_to_MSAOF(of As OPENFILENAME, msaof As MSA_OPENFILENAME)
        '---------------------------------------------------------------------
        ' This sub converts from the win32 structure to the friendly MSAccess
        ' structure.
        '---------------------------------------------------------------------
        msaof.strFullPathReturned = Left$(of.lpstrFile, _
            InStr(of.lpstrFile, Chr$(0)))
        msaof.strFileNameReturned = of.lpstrFileTitle
        msaof.intFileOffset = of.nFileOffset
        msaof.intFileExtension = of.nFileExtension
    End Sub
    Now...in the On Click event for the new command button, place in the code below:

    Code:
        Dim Strg As String
        If Not IsNull(Me![myTextBox]) Then Strg = Me![myTextBox]
        Strg = BrowseForFile(Strg)
        If Strg = "" Then Exit Sub
        Me![myTextBox] = Strg
    Start your form and click your new dialog button.
    Note: The lookup filter can be changed within the BrowseForFile function so to suit your needs.


Posting Permissions

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